dsfhd78978
2019-01-25 01:39 阅读 219
已采纳

如何使用laravel 5.7在内连接中连接表中的几个子选择

I have a query select that works very well directly in MySql, however, I would like to know if I have to replicate in Laravel 5.7, in the Controller class, to send it to my view and create a table with the data, follow the query:

SELECT
    B.ID,
    A.nome,
    A.vulgo,
    A.rg,
    A.sexo,
    I.indiciado_por,
    B.area_atuacao,
    C.carta,
    N.naipe,
    B.data_entrada,
    S.situacao,
    B.data_saida,
    B.observacoes
FROM
    baralho B
INNER JOIN baralho_naipe N ON
    B.id_naipe = N.naipe_id
INNER JOIN baralho_carta C ON
    B.id_carta = C.carta_id
INNER JOIN baralho_situacao S ON
    B.id_situacao = S.situacao_id
INNER JOIN(
    SELECT
        id_baralho,
        GROUP_CONCAT(indiciado.indicio SEPARATOR '/') AS indiciado_por
    FROM
        baralho_has_baralho_indicio
    INNER JOIN baralho B ON
        baralho_has_baralho_indicio.id_baralho = B.id
    INNER JOIN baralho_indicios indiciado ON
        baralho_has_baralho_indicio.id_baralho_indicio = indiciado.indicio_id
    GROUP BY
        B.id
    HAVING
        B.id = B.id
) AS I
ON
    B.id = I.id_baralho
INNER JOIN(
    SELECT
        id_baralho,
        ALVO.nome,
        ALVO.vulgo,
        ALVO.rg,
        ALVO.sexo
    FROM
        baralho_has_baralho_alvo
    INNER JOIN baralho B ON
        baralho_has_baralho_alvo.id_baralho = B.id
    INNER JOIN baralho_alvos ALVO ON
        baralho_has_baralho_alvo.id_baralho_alvo = ALVO.id_alvo
    GROUP BY
        B.id
    HAVING
        B.id = B.id
) AS A
ON
    B.id = A.id_baralho
ORDER BY
    B.id ASC  

How could I make this query work on Laravel? Please help me, I am a beginner at Laravel and thank you for any help

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    dougu3988 dougu3988 2019-01-25 05:25

    This will work I think

    DB::table('baralho AS B')
    ->join('baralho_naipe AS N', 'B.id_naipe', '=', 'N.naipe_id')
    ->join('baralho_carta AS C', 'B.id_carta', '=', 'C.carta_id')
    ->join('baralho_situacao AS S', 'B.id_situacao', '=', 'S.situacao_id')
    ->join(DB::raw("(SELECT
            id_baralho,
            GROUP_CONCAT(indiciado.indicio SEPARATOR '/') AS indiciado_por
        FROM
            baralho_has_baralho_indicio
        INNER JOIN baralho B ON
            baralho_has_baralho_indicio.id_baralho = B.id
        INNER JOIN baralho_indicios indiciado ON
            baralho_has_baralho_indicio.id_baralho_indicio = indiciado.indicio_id
        GROUP BY
            B.id
        HAVING
            B.id = B.id) AS I"), 'B.id', '=', 'I.id_baralho')
    ->join(DB::raw("(SELECT
            id_baralho,
            ALVO.nome,
            ALVO.vulgo,
            ALVO.rg,
            ALVO.sexo
        FROM
            baralho_has_baralho_alvo
        INNER JOIN baralho B ON
            baralho_has_baralho_alvo.id_baralho = B.id
        INNER JOIN baralho_alvos ALVO ON
            baralho_has_baralho_alvo.id_baralho_alvo = ALVO.id_alvo
        GROUP BY
            B.id
        HAVING
            B.id = B.id) AS A"), 'B.id', '=', 'A.id_baralho')
    ->orderBy('B.id',  'ASC')
    ->get();
    
    点赞 评论 复制链接分享

相关推荐