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 | foo@mail.com | $2y$10$xyovyCx32CpbPaiLnKR1o.ls9ydQhupEdMbPyx0Nn2MwOksRAjd0e | NULL | 2017-04-07 16:05:53 | 2017-04-07 16:05:53 |
| 3 | Guxguz | guz@mail.com | $2y$10$fcxZSH9tTFQ8NYRKK46mTuLos3M/vIdzHintrDWhfx5dpteuL4FEG | NULL | 2017-04-07 21:17:04 | 2017-04-07 21:17:04 |
| 4 | Sluxsux | slux@mail.com | $2y$10$l1QdRB7vrkYvwaA4OiMkWeWl5h2l41U4d9yp1uLuWdcp8QuV07APm | NULL | 2017-04-07 21:21:54 | 2017-04-07 21:21:54 |
| 6 | Anbob | an@mail.com | $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!
via O'Niel
