jeudi 7 janvier 2016

how to self join using eloquent with order by and group by

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