I want to covert MySQL query into Laravel Eloquent / Laravel ORM-CodePudding

This is my query;

SELECT
    *
FROM
    `chats` chat
INNER JOIN(
    SELECT MAX(`chats`.`id`) AS last_id
    FROM
        `chats`
    WHERE
        (`chats`.`receiver_id` = 1 OR `chats`.`sender_id` = 1)
    GROUP BY
        CONCAT(
            LEAST(
                `chats`.`receiver_id`,
                `chats`.`sender_id`
            ),
            '.',
            GREATEST(
                `chats`.`receiver_id`,
                `chats`.`sender_id`
            )
        )
) conversations
ON
    conversations.last_id = chat.id
ORDER BY
    chat.created_at
DESC;

I have tried to convert this MySQL query.
This is what I have done so far;

$query = Chat::query();
$total_records = $query->select("chats.*")
        ->innerJoin('chats', function($join) use ($loggin_user) {
        $join->on('chats.id', '=',DB::raw('( SELECT MAX(`chats`.`id`) AS last_id FROM `chats` WHERE (`chats`.`receiver_id` = '.$loggin_user.' OR `chats`.`sender_id` = '.$loggin_user.') GROUP BY CONCAT( LEAST( `chats`.`receiver_id`, `chats`.`sender_id` ), '.', GREATEST( `chats`.`receiver_id`, `chats`.`sender_id` ) ) )'))
        ->orderBy("chats.created_at", "DESC")->get()->toArray();
});

It show this error on screen after quering;
Errors Screenshot

Needs someone help to convert this query into Laravel.

CodePudding user response：

use join instead of innerJoin. there is no such thing as innerJoin in eloquent you can use this three methods: join, leftJoin and rightJoin. i don't know what you're trying to do exactly. try this three methods and see if they work

CodePudding user response：

Laravel Do provide RAW functionaltiy where you can execute raw queries as well.

$query = DB::select("SELECT
    *
FROM
    `chats` chat
INNER JOIN(
    SELECT MAX(`chats`.`id`) AS last_id
    FROM
        `chats`
    WHERE
        (`chats`.`receiver_id` = 1 OR `chats`.`sender_id` = 1)
    GROUP BY
        CONCAT(
            LEAST(
                `chats`.`receiver_id`,
                `chats`.`sender_id`
            ),
            '.',
            GREATEST(
                `chats`.`receiver_id`,
                `chats`.`sender_id`
            )
        )
) conversations
ON
    conversations.last_id = chat.id
ORDER BY
    chat.created_at
DESC;");

We can still use statement command of Laravel as well

$query = DB::statement("SELECT
    *
FROM
    `chats` chat
INNER JOIN(
    SELECT MAX(`chats`.`id`) AS last_id
    FROM
        `chats`
    WHERE
        (`chats`.`receiver_id` = 1 OR `chats`.`sender_id` = 1)
    GROUP BY
        CONCAT(
            LEAST(
                `chats`.`receiver_id`,
                `chats`.`sender_id`
            ),
            '.',
            GREATEST(
                `chats`.`receiver_id`,
                `chats`.`sender_id`
            )
        )
) conversations
ON
    conversations.last_id = chat.id
ORDER BY
    chat.created_at
DESC;");

As you asked if you want to use get function with raw queries, you can modify your queries like this:

DB::table('some_table')
    ->selectRaw('COUNT(*) AS result')
    ->get();

This will return a collection of PHP objects.

Additional you can use this as well:

DB::select(DB::raw(" 
    SELECT COUNT(*) AS result
    FROM some_table"
));

This will return array of Php object.