CODEWITHSUNDEEP
phpsymfonydoctrine-ormdoctrine
user3110828
user3110828

Reputation: 73

Convert SQL to Doctrine

I want to convert this sql query for the entity manager of doctrine :

SELECT count(c.id)
        FROM content c
        WHERE c.id IN
            (SELECT tdtc.content_id
             FROM tdtheme_content tdtc
             JOIN tdtheme tdt ON tdtc.tdtheme_id = tdt.id
             JOIN td t ON tdt.td_id = t.id
             JOIN matiere m ON t.matiere_id = m.id
             WHERE m.id = 2)

        OR c.id IN
            (SELECT sc.content_id
             FROM semaine_content sc
             JOIN semaine s ON sc.semaine_id = s.id
             JOIN cour cr ON s.cour_id = cr.id
             JOIN matiere m ON cr.matiere_id = m.id
             WHERE m.id = 2)

        OR c.id IN
            (SELECT ac.content_id
             FROM annale_content ac
             JOIN annale a ON ac.annale_id = a.id
             JOIN matiere m ON a.matiere_id = m.id
             WHERE m.id = 2)');

I have :

Matiere With Annale And Td And Cours

Annale With Contents

Td With Tdtheme With Contents

Cours With Semaines With Contents

And I want to get count of contents for one matiere

Any Idea ?

Thanks and sorry for my bad English ^^

Upvotes: 0

Views: 1262

Answers (1)

Peter Bailey
Peter Bailey

Reputation: 105888

Assuming your entity names are Content, TdTheme_Content, etc.

$repo = $em->getRepository('AppBundle:Content');
$query = $repo ->createQueryBuilder('c');

$subQuery1 = $query->createSubquery();
$subQuery1
    ->select('tdtc.contentId')
    ->from('TdTheme_Content tdtc')
    ->join('tdtc.tdTheme tdt') // Doctrine joins by property names, not column names
    ->join('tdt.td t')
    ->join('t.matiere m')
    ->where('m.id = ?', 2)
;

// Assume similar for $subQuery2 and $subQuery3

$query
    ->select('COUNT(c.id)')
    ->where('c.id IN(' . $subQuery1->getDql() ')')
    ->orWhere('c.id IN(' . $subQuery2->getDql() ')')
    ->orWhere('c.id IN(' . $subQuery3->getDql() ')')
;

NOTE: I did not debug this or attempt to execute it - this was just off the top of my head with some basic knowledge of the doctrine query-builder api.

Upvotes: 1

Related Questions