gpt4 book ai didi

mysql - 如何在左外连接上使用 laravel 中的 select 作为第二个条件

转载 作者:行者123 更新时间:2023-11-29 16:21:00 25 4
gpt4 key购买 nike

我正在尝试将此 mysql block 转换为 laravel sql,但无论如何都无法成功

select tt.id
, tt.date
, m1.date
, m2.date
, m1.userid
, m2.userid
from tbltickets tt
join tblticketreplies m1
on tt.id = m1.tid
left
join tblticketreplies m2
on m1.tid = m2.tid
and m2.date =
(SELECT min(m3.date)
FROM tblticketreplies m3
WHERE m3.date > m1.date
and m3.tid=m1.tid)
where tt.date > NOW() - INTERVAL 1 MONTH;

Laravel block :

        $rows = DB::table('tbltickets')
->join('tblticketreplies as m1', 'tbltickets.id', '=', 'm1.tid')
->leftJoin("tblticketreplies as m2", function($join) {
$join->on('m2.tid', '=', 'm1.tid');
$join->on('m2.date',"=",DB::raw("SELECT min(m3.date) FROM tblticketreplies m3 WHERE m3.date > m1.date and m3.tid=m1.tid"));
})
->select('tt.id as id,tt.date as first,m1.date as second,m2.date as third ,m1.userid as fid,m2.userid as sid')
->get();

有人知道如何在外连接中使用 select 吗?或者是否可以做到?

最佳答案

您尚未向 tbltickets (tt) 添加别名,并在 select 中使用它,其次在 select() 中您需要传递多个参数,目前它只是一个长字符串。

<?php
$rows = DB::table('tbltickets as tt')
->join('tblticketreplies as m1', 'tbltickets.id', '=', 'm1.tid')
->leftJoin("tblticketreplies as m2", function($join) {
$join->on('m2.tid', '=', 'm1.tid');
$join->on('m2.date',"=", DB::raw("SELECT min(m3.date) FROM tblticketreplies m3 WHERE m3.date > m1.date and m3.tid=m1.tid"));
})
->select(
'tt.id as id',
DB::raw('tt.date as first'),
DB::raw('m1.date as second'),
DB::raw('m2.date as third'),
DB::raw('m1.userid as fid'),
DB::raw('m2.userid as sid')
)
->get();

关于mysql - 如何在左外连接上使用 laravel 中的 select 作为第二个条件,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/54509726/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com