gpt4 book ai didi

php - Laravel 查询构建器中留下的 SQL 连接

转载 作者:行者123 更新时间:2023-11-29 17:51:05 26 4
gpt4 key购买 nike

我尝试在 Laravel 查询生成器中执行此查询,但结果不一样。

select u.id as id,
u.name as name,
count(c.user_id) as casos,
cast(SUM(CASE WHEN c.status = 2 THEN 1 ELSE 0 END) AS SIGNED INTEGER) AS pendientes,
cast(SUM(CASE WHEN c.status = 0 THEN 1 ELSE 0 END) AS SIGNED INTEGER) AS enviados
from users u
left join casos c
on c.user_id = u.id
where u.lab_id = 2
and u.admin = 0
group by u.id

这是我的 Laravel 查询:

User::select(
'users.id as id',
'users.name as name',
DB::raw("count(casos.user_id) as casos"),
DB::raw("cast(SUM(CASE WHEN casos.status = 2 THEN 1 ELSE 0 END) AS SIGNED INTEGER) AS pendientes"),
DB::raw("cast(SUM(CASE WHEN casos.status = 0 THEN 1 ELSE 0 END) AS SIGNED INTEGER) AS enviados"),
'users.created_at as created_at'
)
->leftJoin('casos', 'casos.user_id', '=', 'users.id')
->where('lab_id', 2)
->groupBy('user_id');

原始查询返回所有用户,即使没有 casos,laravel 仅返回有 casos 的用户。我在 laravel 查询中做错了什么?

最佳答案

将最后一行更改为:

->leftJoin('casos', 'casos.user_id', '=', 'users.id')
->where('users.lab_id', 2)
->where('users.admin', 0)
->groupBy('users.id');

缺少条件u.admin = 0。并且您按 casos.user_id 而不是 users.id 进行分组。您不应该按 LEFT JOIN 的右表进行分组。

关于php - Laravel 查询构建器中留下的 SQL 连接,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49335240/

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