Laravel eloquent 选择每组的第一行

Laravel eloquent select first row of each group by

提问by Ryan Exlay

I have subjects table and tutorials table in SQLite database. I am trying to select current tutorial. There are my tables

我在 SQLite 数据库中有主题表和教程表。我正在尝试选择当前教程。有我的桌子

• Subjects table:

  -------------------
  |  id |   name    |
  -------------------
  |  1  | Chemistry |
  |  2  | Physic    |
  -------------------
  

• Tutorials table:

  -----------------------------------------
  | id  |    name   | subj_id | completed |
  -----------------------------------------
  |  1  | chapter 1 |   1     |     1     |
  |  2  | chapter 2 |   1     |     0     |
  |  3  | chapter 3 |   1     |     0     |
  |  4  | chapter 1 |   2     |     1     |
  |  5  | chapter 2 |   2     |     1     |
  |  6  | chapter 3 |   2     |     0     |
  |  7  | chapter 4 |   2     |     0     |
  -----------------------------------------
  

• 科目表：

  -------------------
  |  id |   name    |
  -------------------
  |  1  | Chemistry |
  |  2  | Physic    |
  -------------------
  

• 教程表：

  -----------------------------------------
  | id  |    name   | subj_id | completed |
  -----------------------------------------
  |  1  | chapter 1 |   1     |     1     |
  |  2  | chapter 2 |   1     |     0     |
  |  3  | chapter 3 |   1     |     0     |
  |  4  | chapter 1 |   2     |     1     |
  |  5  | chapter 2 |   2     |     1     |
  |  6  | chapter 3 |   2     |     0     |
  |  7  | chapter 4 |   2     |     0     |
  -----------------------------------------
  

My current eloquent is:

我现在的口才是：

  $query->where('completed', false)->groupBy('subj_id')->get();

And it returns the following:

它返回以下内容：

-----------------------------------------
| id  |    name   | subj_id | completed |
-----------------------------------------
|  3  | chapter 3 |   1     |     0     |
|  7  | chapter 4 |   2     |     0     |
-----------------------------------------

Now I wish to select id 2 and 6.

现在我想选择 id 2 和 6。

-----------------------------------------
| id  |    name   | subj_id | completed |
-----------------------------------------
|  2  | chapter 2 |   1     |     0     |
|  6  | chapter 3 |   2     |     0     |
-----------------------------------------

How could I get id 2 and 6 using sqlite?

如何使用sqlite获得id 2和6？

回答by Ryan Exlay

My problem is solved by @zerofl4g's help.

@zerofl4g 的帮助解决了我的问题。

This eloquent helped by @zerofl4g.

@zerofl4g 帮助了这个雄辩。

$query->select(DB::raw("SELECT MIN(id), name, subj_id FROM tutorials
 WHERE completed = 0 GROUP BY subj_id"));

It doesn't work for me but help me a lot. I don't know the precise reason why not worked form me but I think I am using it as subquery. So I got duplicate select and from errors. It must surely work for someone who want to use as single query.

它对我不起作用，但对我有很大帮助。我不知道为什么不能从我这里工作的确切原因，但我认为我将它用作子查询。所以我得到了重复的选择和错误。它肯定适用于想要用作单个查询的人。

My solution is just select columns with DB::raw and ->from('tutorials') is also optional, as it is subquery of a long query

我的解决方案只是选择带有 DB::raw 的列，并且 ->from('tutorials') 也是可选的，因为它是长查询的子查询

$query->select(DB::raw('MIN(id) as id, name, subj_id'))->from('tutorials')
->where('completed', false)->groupBy('subj_id');

Final eloquent I am using is

我使用的最终雄辩是

$query->select(DB::raw('MIN(id) as id, name, subj_id'))
->where('completed', false)->groupBy('subj_id');`

Hope to be helpful.

希望有所帮助。

回答by Faiz

If you want to return first row in group by, you can try the code below. I think what you want to get is the latest chapter in the subject that not finish.

如果你想返回 group by 中的第一行，你可以试试下面的代码。我想你想得到的是该主题未完成的最新章节。

$data = Tutorial::where('completed', 0)->get();
$groups = $data->groupBy('subj_id');

$results = [];
foreach($groups as $group) {
    $results[] = head($group);
}
return $results;

1. Get all tutorials row

2. Then, group by 'subj_id'. The data you get is like below

   { 1: [{ id: "2", name: "chapter 2", subj_id: "1", completed: "0" }, { id: "3", name: "chapter 3", subj_id: "1", completed: "0" }],

   2: [{
       id: "6",
       name: "chapter 3",
       subj_id: "2",
       completed: "0"
   }, {
       id: "7",
       name: "chapter 4",
       subj_id: "2",
       completed: "0"
   }]
   

   }

3. Foreach each group and get the minimum id by using head() helper function in laravel.

1. 获取所有教程行

2. 然后，按“subj_id”分组。你得到的数据如下

   { 1: [{ id: "2", name: "chapter 2", subj_id: "1", completed: "0" }, { id: "3", name: "chapter 3", subj_id: "1" , 完成: "0" }],

   2: [{
       id: "6",
       name: "chapter 3",
       subj_id: "2",
       completed: "0"
   }, {
       id: "7",
       name: "chapter 4",
       subj_id: "2",
       completed: "0"
   }]
   

   }

3. Foreach 每个组并通过使用 laravel 中的 head() 辅助函数获取最小 id。

回答by polodev

Correct query will be like

正确的查询将类似于

$query->where('completed', false)->get()->groupBy('subj_id');

get()will be call first before groupBy. I mean once get()make collection you can use all collection method. for more details and know all collection method https://laravel.com/docs/5.8/collections#method-groupby

get()之前会先打电话groupBy。我的意思是一旦get()进行收集，您就可以使用所有收集方法。欲了解更多详情并了解所有收集方法 https://laravel.com/docs/5.8/collections#method-groupby