在laravel中加入join的子查询 [英] 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中?

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

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

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