0

I have the following SQL query. It retrieves all the latest messages from each conversation a user has. I am migrating my pure PHP app to Laravel, so I don't have a lot of experience with this. I managed to convert most of the queries, but this one is too complex for me even in SQL. I need some help. Here is the query:

SELECT result.id, s.name AS sender, result.sender_id, r.name AS receiver, result.receiver_id, result.sent_at, result.read
                FROM (SELECT t1.*
                        FROM letters 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 letters
                            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 = ?) result
                INNER JOIN users s ON s.id = result.sender_id
                INNER JOIN users r ON r.id = result.receiver_id
                ORDER BY result.sent_at DESC

This is the letter model:

/**
 * The user who wrote the letter
 *
 */
public function author()
{
    return $this->belongsTo(User::class, 'sender_id');
}

/**
 * The person who will receive the letter
 *
 */
public function recipient()
{
    return $this->belongsTo(User::class, 'receiver_id');
}

This is the user model:

/**
* Get all the sent letters by this user
*/
public function letters_sent()
{
    return $this->hasMany(Letter::class, 'sender_id');
}

/**
* Get all the received letters by this user
*/
public function letters_received()
{
    return $this->hasMany(Letter::class, 'receiver_id');
}
Max
  • 1
  • 1
  • 1
  • 1
    Please update your question to include your model definitions and relationships. Also, what have you tried thus far, and where are you having difficulty? – fubar Jun 10 '20 at 01:09
  • 2
    You could follow this method: https://stackoverflow.com/questions/22925451/how-can-i-query-raw-via-eloquent – zedfoxus Jun 10 '20 at 01:39
  • 1
    Have a go at it yourself first. We won't write your code for you, but we can certainly help if you've got a *specific* problem – Joundill Jun 10 '20 at 04:32
  • I know you specifically says ELOQUENT but, man, It's not a sin to use DB::select('query inside'); Your query has multiple subquerys and all. – Mondini Jun 10 '20 at 12:52

0 Answers0