Subquery With Join In Laravel

I'm about to go crazy about a query, can someone help me to translate a query to work in laravel, or to write in 'laravel way'. The query code is below, and it's using MySQL. The p

Solution 1:

try to send the subquery as a raw expression as the first parameter in the leftJoin() method, for example:

$subquery = '(SELECT 
                    usr_log1.*
                FROM
                    users_log_logradouro AS usr_log1
                LEFT JOIN 
                    users_log_logradouro AS usr_log2 
                    ON usr_log1.user_id = usr_log2.user_id
                    AND usr_log1.created_at < usr_log2.created_at
                WHERE
                    usr_log2.user_id IS NULL) 
                AS temp';

    DB::table('users as usr')
        ->select(...)
        ->leftJoin(DB::raw($subquery), 'usr.id', '=', 'temp.user_id')
        ->leftJoin(...)
        ->join(...)
        ->join(...)
        ->join(...)
        ->leftJoin(...)
        ->leftJoin(...)
        ->leftJoin(...)
        ->where(...)
        ->get()

there are similar examples in these questions:

Laravel Fluent Query Builder Join with subquery

How to write this (left join, subquery ) in Laravel 5.1?

more info abaut joins: https://laravel.com/docs/5.5/queries#joins

Solution 2:

Thanks to all users that answered, after a few tests, i'd choose to use the DB::table with raw query and it worked very well

Thank you all.

Solution 3:

You can try my Eloquent extension enabling subqueries inside eloquent designed exactly for this case.

In your sample it will be

User::selectRaw(....)
->leftJoinSubquery(UserLogs::where(...)->leftJoin(...),
    'temp', 
    function ($join) {
        $join->on('users.id', '=', 'temp.user_id');
    }
)