Convert from SQL to DQL

Question

Can you please convert it to DQL :

SELECT molecule.cas, molecule.id_molecule, molecule.statutvlep8h, statutvlepct,
       vlep8h_mg, vlepct_mg,molecule.unitevlep, prelevement.id_laboratoire 
FROM thym_dev.molecule 
INNER JOIN thym_dev.prelevement 
WHERE molecule.id_molecule = prelevement.id_molecule

UNION ALL

SELECT molecule.cas, molecule.id_molecule, molecule.statutvlep8h, statutvlepct, 
       vlep8h_mg, vlepct_mg,molecule.unitevlep, analyse.id_laboratoire 
FROM thym_dev.molecule 
INNER JOIN thym_dev.analyse
WHERE molecule.id_molecule = analyse.id_molecule;

There's no `UNION` in DQL. I think that Native SQL query will be much easier to implement in Doctrine. http://doctrine-orm.readthedocs.io/en/latest/reference/native-sql.html — Jakub Matczak, Aug 24 '16 at 15:40
Please show that you've tried something. @dragoste has a good suggestion. — Alvin Bunk, Aug 24 '16 at 15:53

score 0 · Accepted Answer · answered Sep 05 '16 at 11:14

I get the answer :

    $queryBuilder0 = "
    SELECT molecule.molecule, molecule.cas, molecule.statutvlep8h,molecule.statutvlepct,molecule.vlep8hMg, molecule.vlepctMg,molecule.unitevlep,IDENTITY(prelevement.laboratoire)
    FROM AppBundle:Molecule molecule
    INNER JOIN AppBundle:Prelevement prelevement
    WHERE prelevement.molecule= molecule.id 
    ";

    $queryBuilder1 = "
    SELECT molecule.molecule , molecule.cas, molecule.statutvlep8h, molecule.statutvlepct, molecule.vlep8hMg, molecule.vlepctMg,molecule.unitevlep,IDENTITY(analyse.laboratoire)
    FROM AppBundle:Molecule molecule 
    INNER JOIN AppBundle:Analyse analyse
    WHERE analyse.molecule= molecule.id
    ";

    $results = array_merge($this->_em->createQuery($queryBuilder0)->getResult(), $this->_em->createQuery($queryBuilder1)->getResult());

Convert from SQL to DQL

1 Answers1