php - 如何将数组与表连接起来(数组中的链接ID与表ID)

Question

所以，我有一个包含列表的数据库。列表中的项目附加了一个 user_id，我已经执行了一个查询来对它们进行计数并对它们进行分组，但由于我已经在 DB::Select 和原始 SQL 中完成了此操作，因此 实际用户的 >user_id 已丢失。

我一直在尝试加入，但到目前为止还没有成功，这是我的代码:

这会为我提供带有number(count) 和user_id 的列表。

$updatedToday = DB::select('SELECT user_id, COUNT(*) AS `num` FROM faulty_device WHERE DATE(`updated_at`) = DATE(CURDATE()) AND user_id IS NOT NULL AND (status = 3 OR status = 4 OR status = 5) GROUP BY user_id');

这不起作用，请参阅我的连接尝试:

$updatedToday = DB::select('SELECT user_id, COUNT(*) AS `num` FROM faulty_device JOIN users ON faulty_device.user_id=users.id WHERE DATE(`updated_at`) = DATE(CURDATE()) AND user_id IS NOT NULL AND (status = 3 OR status = 4 OR status = 5) GROUP BY user_id');

我想做的基本上是在我看来使用这个:

@foreach($updatedToday as $updated)
    {{$updated->user->username}}
    {{$updated->num}}
@endforeach

(显示等用户名:Ted Swansson，号码:15)

但到目前为止我只能设法让它发挥作用:

@foreach($updatedToday as $updated)
    {{$updated->user_id}}
    {{$updated->num}}
@endforeach

(显示等 user_id:5，num:15)

关于如何解决这个问题有什么想法吗？

编辑

我让它在 phpmyadmin 中工作，但相同的命令在 laravel 中失败......

SELECT user_id, username, COUNT(*) AS `num` FROM faulty_device JOIN users ON faulty_device.user_id=users.id WHERE DATE(`updated_at`) = DATE(CURDATE()) AND user_id IS NOT NULL AND (status = 3 OR status = 4 OR status = 5) GROUP BY user_id

说:

Syntax error or access violation: 1055 'database1.users.username' isn't in GROUP BY (SQL: SELECT user_id, username, COUNT(*) AS num FROM faulty_device JOIN users ON faulty_device.user_id=users.id WHERE DATE(updated_at) = DATE(CURDATE()) AND user_id IS NOT NULL AND (status = 3 OR status = 4 OR status = 5) GROUP BY user_id)

编辑2Laravel 想要一个额外的 GROUP BY，现已解决。

Answer 1

想通了。它希望在末尾有一个“用户名”作为附加的 GROUP BY。

SELECT user_id, username, COUNT(*) AS `num` FROM faulty_device LEFT JOIN users ON faulty_device.user_id=users.id WHERE DATE(`updated_at`) = DATE(CURDATE()) AND user_id IS NOT NULL AND (status = 3 OR status = 4 OR status = 5) GROUP BY user_id, username