I want to execute below query in Laravel 5.1
SELECT *, max(m2.created_at) createdat FROM `messages` m1 left join messages m2
on (m1.id=m2.parent_id) where m1.parent_id=0 group by m1.id order by createdat desc
Below is my 'messages' table
id|parent_id |message_content |created_at
2 |0 |hello |2015-08-10 10:32:16
3 |0 |Hello Again |2015-08-10 10:33:25
4 |0 |how are you.This |2015-10-19 16:34:56
24 |2 |hi from a |2016-01-07 11:37:21
25 |4 |ok ||2016-01-07 11:38:23
26 |3 |now its here2 |2016-01-07 11:38:38
27 |4 |4th on top1 |2016-01-07 11:39:32
28 |3 |3rd on top1 |2016-01-07 11:46:56
29 |2 |2nd on top1 |2016-01-07 11:47:12
30 |3 |3rd on top2 |2016-01-07 11:47:24
31 |4 |4th on top2 |2016-01-07 11:47:36
I got it working using below Laravel code
$messages = Message::select(DB::raw('*,max(m2.created_at) as createdAt'))->leftJoin('messages as m2','m2.parent_id','=','messages.id')
->Parent()
->groupBy('messages.id')
->orderBy('createdAt','desc')
->get();
This is my Parent() scope
public function scopeParent($query)
{
return $query->where('messages.parent_id', '=', '0');
}
Is it possible to get it done without using "leftJoin" and purely eloquent way?
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire