CODEWITHSUNDEEP
phpmysqllaravel
O'Niel
O'Niel

Reputation: 1787

Latest row of each user Laravel

I have a kind of 'chat' system where users can chat privately with the administrator, but I have a problem. In the adminpanel I need to list all the users with which I have (had) a conversation with, and those at who I haven't replied at yet need to be coloured red. I was able to do this in standard PHP but I have no clue on how to do it in Laravel.

This is my database: Chat

+----+-----------------------------------------------------------------------------+--------+-------+------+------+------------+------------+
| id | message                                                                     | sender | admin | file | type | created_at | updated_at |
+----+-----------------------------------------------------------------------------+--------+-------+------+------+------------+------------+
|  1 | Heeey!                                                                      |      1 |     0 |    0 |    1 | NULL       | NULL       |
|  2 | Hey.                                                                        |      1 |     1 |    0 |    1 | NULL       | NULL       |
|  4 | Zou ik iets mogen vragen?                                                   |      1 |     0 |    0 |    1 | NULL       | NULL       |
|  5 | Ja, tuurlijk.                                                               |      1 |     1 |    0 |    1 | NULL       | NULL       |
|  6 | Zou u een foto voor mij willen vectoriseren?                                |      1 |     0 |    0 |    1 | NULL       | NULL       |
|  7 | Het is een vrij grootte                                                     |      1 |     0 |    0 |    1 | NULL       | NULL       |
|  8 | Dat is geen enkel probleem!                                                 |      1 |     1 |    0 |    1 | NULL       | NULL       |
|  9 | Stuur maar door                                                             |      1 |     1 |    0 |    1 | NULL       | NULL       |
| 10 | <a class='.fileReference' href='/uploads/Foobar/24344cat.jpeg'>cat.jpeg</a> |      1 |     0 |    1 |    1 | NULL       | NULL       |
| 11 | Bedankt!                                                                    |      1 |     0 |    0 |    1 | NULL       | NULL       |
| 12 | Geen probleem.                                                              |      1 |     1 |    0 |    1 | NULL       | NULL       |
| 13 | e.                                                                          |      1 |     0 |    0 |    1 | NULL       | NULL       |
| 14 | e.                                                                          |      1 |     1 |    0 |    1 | NULL       | NULL       |
| 15 | e.                                                                          |      1 |     0 |    0 |    1 | NULL       | NULL       |
| 16 | e.                                                                          |      1 |     1 |    0 |    1 | NULL       | NULL       |
| 17 | e.                                                                          |      2 |     0 |    0 |    1 | NULL       | NULL       |
| 18 | e.                                                                          |      3 |     0 |    0 |    1 | NULL       | NULL       |
+----+-----------------------------------------------------------------------------+--------+-------+------+------+------------+------------+

Users

+----+---------+---------------+--------------------------------------------------------------+----------------+---------------------+---------------------+
| id | name    | email         | password                                                     | remember_token | created_at          | updated_at          |
+----+---------+---------------+--------------------------------------------------------------+----------------+---------------------+---------------------+
|  1 | Foobar  | [email protected]  | $2y$10$xyovyCx32CpbPaiLnKR1o.ls9ydQhupEdMbPyx0Nn2MwOksRAjd0e | NULL           | 2017-04-07 16:05:53 | 2017-04-07 16:05:53 |
|  3 | Guxguz  | [email protected]  | $2y$10$fcxZSH9tTFQ8NYRKK46mTuLos3M/vIdzHintrDWhfx5dpteuL4FEG | NULL           | 2017-04-07 21:17:04 | 2017-04-07 21:17:04 |
|  4 | Sluxsux | [email protected] | $2y$10$l1QdRB7vrkYvwaA4OiMkWeWl5h2l41U4d9yp1uLuWdcp8QuV07APm | NULL           | 2017-04-07 21:21:54 | 2017-04-07 21:21:54 |
|  6 | Anbob   | [email protected]   | $2y$10$SO/NsCXmBxGjtsBQmvXrMuUyUb8wjmTE532UaImFqXDxeuGI95hCa | NULL           | 2017-04-07 21:23:20 | 2017-04-07 21:23:20 |
+----+---------+---------------+--------------------------------------------------------------+----------------+---------------------+---------------------+

My controller:

   $users = DB::table('chat')
        ->select('sender', 'admin', 'name')
        ->orderBy('chat.admin', 'asc')
        ->groupBy('sender')
        ->join('users', 'chat.sender', '=', 'users.id')
        ->get();

What I get back:

[{"sender":"1","admin":"0","name":"Foobar"},{"sender":"3","admin":"0","name":"Guxguz"}]

Like you can see at Foobar it says admin=0, but this ain't correct, but if you look in the database you'll see that the latest row of Foobar says admin=1.

I basically need to take the last row of each user, so I can fetch the name from there and the admin-column to see if the admin-column is 0 (I wasn't last) or 1 (I was last).

I tried ordering, distinct, max-id,... but none worked.

Thanks!

Upvotes: 0

Views: 741

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31812

There are some ways to solve your issue. The probably simplest in laravel is to execute two queries.

First get the highest chat id per user:

$latestChatIds = DB::table('chat')
    ->groupBy('sender')
    ->select(DB::raw('max(id) as id'))
    ->get()
    ->pluck('id');

Then use those ids in whereIn() to get the corresponding rows:

$users = DB::table('chat')
    ->join('users', 'chat.sender', '=', 'users.id')
    ->whereIn('chat.id', $latestChatIds)
    ->select('sender', 'admin', 'name')
    ->get();

You can also use the first query as subquery in the second one:

$latestChatIds = DB::table('chat')
    ->groupBy('sender')
    ->select(DB::raw('max(id)'));

$users = DB::table('chat')
    ->join('users', 'chat.sender', '=', 'users.id')
    ->whereIn('chat.id', $latestChatIds)
    ->select('sender', 'admin', 'name')
    ->get();

Upvotes: 3

Related Questions