Convert Raw Sql to laravel Query buider format

I have a raw query which works correctly.

SELECT a.date_demande,a.row, a.nom,a.prenom,a.timestamp_statut,a.timestamp_statut_fmt,a.dossier_id_rapproche,a.type_enquete,
a.enquete_statut,a.code_couleur,a.code,a.type_demande_libelle, a.type_demande_couleur,a.type_demande_code,a.type_demande_icone,a.type_demande_contester,
a.type_demande_contester,a.date_demande_origin,a.date_demande,a.dossier_id, a.prestataire_equipe, a.ref_client, a.client,a.standalone,a.demandeur,
a.lieu_naissance,a.lieu_deces,a.no_boite_lettre,a.cp,a.ville,b.Phone FROM
(select
  distinct `dossiers`.`id`,
  `dossiers`.`id` as `row`,
  `nom`.`originalValue` as `nom`,
  `prenom`.`originalValue` as `prenom`,
  `dossiers`.`timestamp_statut` as `timestamp_statut`,
  DATE_FORMAT(timestamp_statut, "%d/%m/%Y") AS timestamp_statut_fmt,
  `dossiers`.`dossier_id_rapproche` as `dossier_id_rapproche`,
  CONCAT(t1.type_enquete, ' - ', t1.lib_type_enquete) as type_enquete,
  `enquete_statuts`.`libelle` as `enquete_statut`,
  `enquete_statuts`.`code_couleur` as `code_couleur`,
  `enquete_statuts`.`code` as `code`,
  `type_demande_client`.`libelle` as `type_demande_libelle`,
  `type_demande_client`.`code_couleur` as `type_demande_couleur`,
  `type_demande_client`.`code` as `type_demande_code`,
  `type_demande_client`.`icone` as `type_demande_icone`,
  `type_demande_client`.`contester` as `type_demande_contester`,
  `date_demande` as `date_demande_origin`,
  DATE_FORMAT(date_demande, "%d/%m/%Y") AS date_demande,
  `affectation`.dossier_id AS dossier_id ,
  `affectation`.prestataire_equipe AS prestataire_equipe ,
  `ref_client`,
  `clients`.`nom_raison_sociale` as `client`,
  `clients`.`standalone` as `standalone`,
  `clients`.`demandeur` as `demandeur`,
  CONCAT(t2.type_enquete, ' - ', t2.lib_type_enquete) as `type_enquete_retrouve`,
  (
    CASE
      WHEN date_de_naissance.originalValue not like "%/%/%" THEN DATE_FORMAT(`date_de_naissance`.`originalValue`, "%d/%m/%Y")
      ELSE date_de_naissance.originalValue
    END
  ) AS date_naissance,
  `lieu_de_naissance`.`originalValue` as `lieu_naissance`,
  (
    CASE
      WHEN date_de_deces.originalValue not like "%/%/%" THEN DATE_FORMAT(`date_de_deces`.`originalValue`, "%d/%m/%Y")
      ELSE date_de_deces.originalValue
    END
  ) AS date_deces,
  `lieu_de_deces`.`originalValue` as `lieu_deces`,
   `adresse_bp`.`originalValue` as `no_boite_lettre`,
  (
    CASE
      WHEN code_postal_particulier.originalValue is not null
      AND ville_particulier.originalValue is not null THEN code_postal_particulier.originalValue
      WHEN code_postal_professionnel.originalValue is not null
      AND ville_professionnel.originalValue is not null THEN code_postal_professionnel.originalValue
      ELSE code_postal_particulier.originalValue
    END
  ) as cp,
  (
    CASE
      WHEN code_postal_particulier.originalValue is not null
      AND ville_particulier.originalValue is not null THEN ville_particulier.originalValue
      WHEN code_postal_professionnel.originalValue is not null
      AND ville_professionnel.originalValue is not null THEN ville_professionnel.originalValue
      ELSE ville_particulier.originalValue
    END
  ) as ville
from
  `dossiers`
  left join `index_dossiers` as `nom` on `dossiers`.`id` = `nom`.`dossier_id`
  and `nom`.`zone` = 'etat_civil.nom'
  left join `index_dossiers` as `prenom` on `dossiers`.`id` = `prenom`.`dossier_id`
  and `prenom`.`zone` = 'etat_civil.prenom'
  left join `index_dossiers` as `date_de_naissance` on `dossiers`.`id` = `date_de_naissance`.`dossier_id`
  and `date_de_naissance`.`zone` = 'etat_civil.date_naissance'
  left join `index_dossiers` as `lieu_de_naissance` on `dossiers`.`id` = `lieu_de_naissance`.`dossier_id`
  and `lieu_de_naissance`.`zone` = 'etat_civil.lieu_naissance'
  left join `index_dossiers` as `date_de_deces` on `dossiers`.`id` = `date_de_deces`.`dossier_id`
  and `date_de_deces`.`zone` = 'etat_civil.date_deces'
  left join `index_dossiers` as `lieu_de_deces` on `dossiers`.`id` = `lieu_de_deces`.`dossier_id`
  and `lieu_de_deces`.`zone` = 'etat_civil.lieu_deces'
  left join `index_dossiers` as `code_postal_particulier` on `dossiers`.`id` = `code_postal_particulier`.`dossier_id`
  and `code_postal_particulier`.`zone` = 'adresses.particulier_cp'
  left join `index_dossiers` as `code_postal_professionnel` on `dossiers`.`id` = `code_postal_professionnel`.`dossier_id`
  and `code_postal_professionnel`.`zone` = 'adresses.professionnel_cp'
  left join `index_dossiers` as `ville_particulier` on `dossiers`.`id` = `ville_particulier`.`dossier_id`
  and `ville_particulier`.`zone` = 'adresses.particulier_ville'
  left join `index_dossiers` as `ville_professionnel` on `dossiers`.`id` = `ville_professionnel`.`dossier_id`
  and `ville_professionnel`.`zone` = 'adresses.professionnel_ville'
   left join `index_dossiers` as `adresse_bp` on `dossiers`.`id` = `adresse_bp`.`dossier_id`
  and `adresse_bp`.`zone` = 'adresses.particulier_no_boite_lettre'
   left join `index_dossiers` as `telephone` on `dossiers`.`id` = `telephone`.`dossier_id`
  and `telephone`.`zone` = 'telephones.numtel'
  inner join `entites` as `e1` on `dossiers`.`entite_id` = `e1`.`id`
  inner join `clients` on `clients`.`entite_id` = `e1`.`id`
  left join `enquete_statuts` on `dossiers`.`enquete_statut_id` = `enquete_statuts`.`id`
  left join `type_demande_client` on `dossiers`.`type_demande_client_id` = `type_demande_client`.`id`
  inner join `type_enquetes` as `t1` on `dossiers`.`type_enquete_id` = `t1`.`id`
  left join `type_enquetes` as `t2` on `dossiers`.`type_enquete_id_retrouve` = `t2`.`id`
  left join `sous_enquetes` as `se` on `dossiers`.`id` = `se`.`dossier_id`
  left join `affectation_dossiers` on `se`.`id` = `affectation_dossiers`.`sous_enquete_id`
  left join (
    SELECT
      sous_enquetes.dossier_id,
      GROUP_CONCAT(
        case
          when users.name IS NOT NULL then users.name
          ELSE case
            when equipes.libelle IS NOT NULL then equipes.libelle
            when prestataires.nom_raison_sociale IS NOT NULL then prestataires.nom_raison_sociale
          End
        END
      ) AS prestataire_equipe
    FROM
      affectation_dossiers
      JOIN sous_enquetes ON affectation_dossiers.sous_enquete_id = sous_enquetes.id
      LEFT JOIN users ON affectation_dossiers.user_id = users.id
      LEFT JOIN entites AS e ON affectation_dossiers.entite_id = e.id
      LEFT JOIN equipes ON e.id = equipes.entite_id
      LEFT JOIN prestataires ON e.id = prestataires.entite_id
    GROUP BY
      sous_enquetes.dossier_id
  ) AS affectation on `dossiers`.`id` = `affectation`.`dossier_id`
where
  `date_demande` is not NULL) AS a
  
  INNER JOIN
  
(  SELECT 
    `dossier_id`,
    GROUP_CONCAT(`originalValue`) AS `Phone`
FROM `index_dossiers`
WHERE zone = 'telephones.numtel'
GROUP BY `dossier_id`) AS b

ON a.row = b.dossier_id

I am able to write each part of the query which returns a and b seperately as query builder. I am however unable to get both to work together.

Any help or tips would be appreciated. I have tried using some online tools but to no avail I can run the query with DB::select(DB:raw('query') to get the correct results. However the results are returned as an array. The expected output is an instance of query builder for further filtering.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/NYZc841
via IFTTT