subquery with join in laravel

Question

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 part that I'm stuck is in the first JOIN with the subquery to the other JOIN it.

SELECT 
    usr.token,
    usr.id,
    usr.username,
    usr.sn,
    usr.givenname,
    usr.telefone_numero,
    usr.active,
    usr.unidade_id,
    usr.divisao_id,
    usr.secao_id,
    clients.nick,
    posto_grad.sigla AS quadro_sigla,
    log_logradouro_complemento,
    log_logr.nome AS log_logradouro_nome,
    log_logr.cep AS log_logradouro_cep,
    log_bairro.nome AS log_bairro_nome,
    log_localidade.nome AS log_localidade_nome,
    log_uf.sigla AS log_uf_sigla
FROM
    users usr
        LEFT JOIN
    (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 ON (usr.id = temp.user_id)
        LEFT JOIN
    log_logradouro log_logr ON temp.log_logradouro_id = log_logr.id
        INNER JOIN
    clients ON clients.matricula = usr.givenname
        INNER JOIN
    quadro ON quadro.id = clients.quadro_id
        INNER JOIN
    posto_graduacao posto_grad ON posto_grad.id = clients.posto_graduacao_id
        LEFT JOIN
    log_bairro ON log_logr.log_bairro_id = log_bairro.id
        LEFT JOIN
    log_localidade ON log_bairro.log_localidade_id = log_localidade.id
        LEFT JOIN
    log_uf ON log_localidade.log_uf_sigla = log_uf.sigla
WHERE
    usr.departmentnumber IN (1,2,3,4)

If you're not planning on using a different database type, you can use `DB::raw()` and just use the raw SQL query. Saves you allot of work. — Jeffrey, Oct 21 '17 at 15:29
The "Laravel way" would be to define all of your models and relationships and use Eloquent. — Camilo, Oct 21 '17 at 15:45
All models and relations was made. I'm using it in others locations of my system — Cavalcante, Oct 21 '17 at 23:14

score 3 · Answer 1 · answered Oct 21 '17 at 15:45

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

I tried that option, but for some reason, it filtered some results and because of my ETA, i can't figure out why. So i'd used a raw query. Thank you — Cavalcante, Oct 22 '17 at 00:01

score 0 · Answer 2 · answered Oct 21 '17 at 23:59

0

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.

answered Oct 21 '17 at 23:59

Cavalcante

101
1
4

score 0 · Answer 3 · answered Jan 05 '18 at 09:09

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');
    }
)

subquery with join in laravel

3 Answers3

Linked