在 laravel 中加入子查询 [英] subquery with join in laravel

问题描述

我快要为一个查询发疯了，有人可以帮我翻译一个查询以在 laravel 中工作，或者用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".

查询代码如下，使用的是MySQL.

The query code is below, and it's using MySQL.

我被卡住的部分是在第一个 JOIN 中，子查询到另一个 JOIN 它.

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)

<小时>

推荐答案

尝试将子查询作为原始表达式作为 leftJoin() 方法中的第一个参数发送，例如:

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 查询生成器加入子查询

如何写(左连接, 子查询 ) 在 Laravel 5.1 中?

关于加入的更多信息:https://laravel.com/docs/5.5/queries#joins

这篇关于在 laravel 中加入子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！