For example I have table:
| id | title | created_at |
1 12:00
2 13:00
2 14:00
1 15:00
I want same id numbers to be near each other. In this case 1 1 2 2 or 2 2 1 1 AND order same chunks of id's by created_at time so the chunk of id's which own the latest created_at stays on top, then goes one, having highest created_at compared to 3rd chunk of id's and so on. How do I do it?
orderBy('id', 'desc')->orderBy('created_at', 'desc')->get(); // orders id's to same id chunks, but it doesn't sort that the chunk with latest id chunk (1 1) created_at at the top.
orderBy('created_at', 'desc')->orderBy('id', 'desc')->get(); // gives the latest created_at at top and so on, but same id's arent close to each other.
Bigger example:
| id | title | created_at |
1 12:00
2 13:00
1 15:00
2 15:00
1 17:00
3 18:00
1 19:00
3 20:00
Want to anchieve that foreach($table_rows as $row) { } would give me result:
3 20:00
3 18:00
1 19:00
1 17:00
1 15:00
1 12:00
2 15:00
2 13:00
I know it's hard to do with mysql alone. How do I do this in php easiest way?
I bet I have to sort by id first and then push each id's chunk relative to each other by latest created_at.
You need to get the information of the most recent date for each id. Here is a method using join and an aggregation:
select t.*
from table t join
(select t.id, max(created_at) as maxca
from table t
group by t.id
) tt
on t.id = tt.id
order by tt.maxca desc, id;
The rest is just the order by using the maximum value.
I don't know how to express this in laravel, but your question is also tagged mysql.