多维groupBy＆orderBy在Paginate之前 - Laravel 5

Question

What I want to do is grouping comments written by a user with same subjects, and get the latest (most-recent) one. I tried using Comment::where()->orderBy()->groupBy(), but it isn't returning data as expected.

This is my database:

| id | receiver_id | sender_id | title | body | created_at |
| 13 |      2      |     5     |  Art  |  DD  |   12.30... |
| 12 |      2      |     5     |  Art  |  CC  |   12.20... |
| 11 |      2      |     5     |  Art  |  BB  |   12.10... |
| 10 |      2      |     5     |  Art  |  AA  |   12.00... |

| 9  |      2      |     3     |  Msc  |  XX  |   11.30... |
| 8  |      2      |     3     |  Msc  |  YY  |   11.20... |
| 7  |      2      |     3     |  Msc  |  ZZ  |   11.10... |

| 6  |      2      |     2     |  Foo  |  UU  |   10.40... |
| 5  |      2      |     2     |  Foo  |  II  |   10.30... |

| 4  |      2      |     2     |  You  |  QQ  |   10.20... |
| 3  |      2      |     2     |  You  |  WW  |   10.10... |

| 2  |      2      |     3     |  Msc  |  LL  |   10.00... |

| 1  |      2      |     4     |  CSS  |  VV  |   10.30... |
| 0  |      2      |     4     |  CSS  |  NN  |   10.20... |

I realised that, created_at and ids are in the same order. So, I decided using id desc, as I may have trouble arranging date integers in created_at strings. This is what I tried:

$comment = Comment::where('receiver_id', Auth::user()->id)
                  ->orderBy('id','desc')
                  ->groupBy('sender_id', 'title')
                  ->paginate(5);
dd($comment);

The result I want to get is this:

0 => Comment { id = 13, sender_id = 5, title = Art, body = DD } (latest created_at)

1 => Comment { id = 9, sender_id = 3, title = Msc, body = XX }

2 => Comment { id = 6, sender_id = 2, title = Foo, body = UU }

3 => Comment { id = 4, sender_id = 2, title = You, body = QQ }

4 => Comment { id = 1, sender_id = 4, title = CSS, body = VV }

However this is showing something like:

0 => Comment { id = 10, sender_id = 5, title = Art, body = AA }

1 => Comment { id = 5, sender_id = 2, title = Foo, body = II }

2 => Comment { id = 3, sender_id = 2, title = You, body = WW }

3 => Comment { id = 2, sender_id = 3, title = Msc, body = LL }

4 => Comment { id = 0, sender_id = 4, title = CSS, body = NN }

• It's giving the latest comments but gets the earliest one.

• It's completely skipping User User-3 and shows User-2's 2x subject

• Then it shows User-3 here, after finishing User2's 2x subjects instead of after 'Art'.

When I remove ->paginate() and try ->toSql() in my above query, I receive:

"select * from `comments` where `receiver_id` = ? group by `title`, `sender_id` order by `id` desc"

I think the order by id desc part is getting created in the wrong position, so this may cause the problem. But, I'm not sure.

Also, when I swap the places of ->orderBy() and ->groupBy like ->groupBy() first, then ->orderBy(), it returns exactly same result.

Lastly, I tried replacing ->orderBy('title', 'sender_id'), but again same result.

What I am doing wrong or missing out? Thanks in advance.

Answer 1

your question is about mysql group by is excuted before order by.

solution should have a inner join:

Comment::join(\DB::raw('(select max(id) as id from comments group by sender_id) t'),function($join){
                  $join->on('comments.id', '=', 't.id');
})->where('comments.receiver_id', Auth::user()->id)
  ->orderBy('id','desc')
  ->paginate(5);

similar here:

MySQL order by before group by