How to transform traditional query into Laravel E.L.O.Q.U.E.N.T QUERY?

Question

Can someone help me to convert this query into Laravel ELOQUENT? The query is below on the screenshot link and also on comment section

DB::select('SELECT t1.*
    FROM messages AS t1
    INNER JOIN
    (
        SELECT
            LEAST(sender_id, receiver_id) AS sender_id,
            GREATEST(sender_id, receiver_id) AS receiver_id,
            MAX(id) AS max_id
        FROM messages
        GROUP BY
            LEAST(sender_id, receiver_id),
            GREATEST(sender_id, receiver_id)
    ) AS t2
        ON LEAST(t1.sender_id, t1.receiver_id) = t2.sender_id AND
           GREATEST(t1.sender_id, t1.receiver_id) = t2.receiver_id AND
           t1.id = t2.max_id
        WHERE t1.sender_id = ? OR t1.receiver_id = ?
    ', [$id, $id]);

I was making a messenger type thing and I want to get the chat list with the latest message send by either sender or receiver using eloquent. — Binay7587, Sep 28 '20 at 01:23
Does this answer your question? [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) — Dark Knight, Sep 28 '20 at 02:48
No bro I want to convert my working query into Eloquent with query builder — Binay7587, Sep 28 '20 at 05:24

score 0 · Answer 1 · answered Sep 28 '20 at 06:23

Considering you have two variables to store sender and receiver id for filtering.

$sender_id = 10;
$receiver_id = 12;
$sub =  DB::table('messages')
            ->selectRaw(
                "LEAST(sender_id, receiver_id) AS sender_id,
                GREATEST(sender_id, receiver_id) AS receiver_id,
                MAX(id) AS max_id"
            )
            ->groupByRaw('LEAST(sender_id, receiver_id), GREATEST(sender_id, receiver_id)');

$main   =   DB::table('messages as t1')
                ->joinSub($sub, 't2', function ($join) {
                    $join->on(function($query){
                        return $query->whereRaw('LEAST(t1.sender_id, t1.receiver_id) = t2.sender_id')
                                    ->whereRaw('GREATEST(t1.sender_id, t1.receiver_id) = t2.receiver_id')
                                    ->whereRaw('t1.id = t2.max_id');
                    });
                })
                ->selectRaw('t1.*')
                ->where('t1.sender_id', '=', $sender_id)
                ->where('t1.receiver_id', '=', $receiver_id)
                ->get();

You can replace get with toSql method to see what query will be generated.

Also I used multiple whereRaw in on clause to increase the readability, you can merge them in single whereRaw too.

See also Joining distinct table in laravel for more info.

How to transform traditional query into Laravel E.L.O.Q.U.E.N.T QUERY?

1 Answers1