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

Question

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

Answer 1

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();