How to get the several data from each group on query builder laravel

Question

a16s table

id  p_id u_id   time
1   1       2   0
2   1       1   1
3   1       5   2
4   1       6   3
5   1       7   4
6   2       2   2
7   2       3   1
8   2       1   0
9   3       2   11
10  3       4   8
11  3       8   15

I want to get the first two data orderby time from each group

p_id u_id time
1     2   0
1     1   1
2     1   0
2     3   1
3     4   8
3     2   11

I try the query

  $result = DB::table('a16s')

            ->select ('p_id','u_id','time'))
            ->orderBy('time', 'desc')
            ->groupBy('p_id')
            ->get();

    echo '<pre>' ;
    print_r($result);

I got the error SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column...

Can I use groupby twice? I Want to get this result to use on the jquery datatable.

from the database

id  p_id    u_id    approve time
1   1          1    1       1
2   1          2    1       2
3   1          3    1       3
4   1          4    0       4
5   1          5    0       5
6   2          1    0       1
7   2          2    1       2
8   2          5    0       3
9   2          6    0       4
10  3          2    1       1
11  3          5    1       2
12  3          8    1       3

to get the table

try this link https://stackoverflow.com/questions/49229955/laravel-database-strict-mode/49230235#49230235 — Sohel0415, Apr 02 '18 at 10:04

score 4 · Accepted Answer · answered Apr 02 '18 at 10:09

4

try this

$result = DB::table('a16s')
            ->select('p_id', 'u_id', 'time')
            ->orderBy('time', 'desc')
            ->get()
            ->groupBy('p_id')
            ->map(function ($deal) {
                return $deal->take(2);
            });

answered Apr 02 '18 at 10:09

Rahman Qaiser

664
5
18

Thanks for your kind help! Can I add one column to group twice ? I ammend my question above. I try ->get()->groupBy('p_id')->group('approve')->.... But not work. – robspin Apr 03 '18 at 09:10
if you want to group multiple columns you can ->groupBy('p_id' , '2nd Column') – Rahman Qaiser Apr 04 '18 at 06:01
I tried groupBy('p_id','approve') but I only got the group1 first two rows just like : group1(p_id=1) -group2(approve-1)-u_id1 and u_id(2row) ,no got the group1(p_id=1)-group2(approve-0)-u_id4 and u_id5 (2rows) ? Where can I fix the problem? – robspin Apr 08 '18 at 17:51

score 1 · Answer 2 · answered Apr 02 '18 at 10:12

1

With your SQL version, u_id will either need to be left out of the select or added to the GROUP BY clause.

See this MySql doc for more info.

answered Apr 02 '18 at 10:12

CUGreen

3,066
2
13
22

score 0 · Answer 3 · answered Apr 02 '18 at 17:52

Using this trick:

$join = DB::table('a16s')->select('p_id')
    ->selectRaw('GROUP_CONCAT(time ORDER BY time ASC) times')->groupBy('p_id');
$sql = '(' . $join->toSql() . ') latest';
$result = DB::table('a16s')
    ->select('a16s.*')
    ->join(DB::raw($sql), function($join) {
        $join->on('a16s.p_id','=','latest.p_id');
        $join->whereBetween(DB::raw('FIND_IN_SET(`a16s`.`time`, `latest`.`times`)'), [1, 2]);
    })
    ->get();

How to get the several data from each group on query builder laravel

3 Answers3