how to write sub-query in symfony doctrine

Question

Am working on a symfony project with backend developed in doctrine & api-platform framework.I need to fetch some details along with checking a field in another table which will be a status. We use this status for handling something in front end.

I tried:-

$qb = $this->createQueryBuilder('contact');
$qb2=$qb;
$sub_query = $qb2->select('field')
    ->from('OtherTable','g')
    ->where("'id= '".$personId."'")
    ->OrderBy('updated_at', 'DESC')
    ->setMaxResults(1)
    ->getQuery()
    ->getResult();

$qb->select("contact.id, 
                contact.title, 
                count (distinct person.id) as 
number_of_contacts_with_email',(".$sub_query.") as status")
 ->leftjoin('contact.people', 'person')
 ->leftJoin('person.jobs', 'jobs')
 ->groupBy('contact.id, contact.title');


$query=$qb->getQuery();
$result = $qb->getQuery()->getArrayResult();
return $result;

Am getting this error when executing query.

  [Semantical Error] line 0, col 59 near 'OtherTable g': Error: Class 'OtherTable' is not defined.

How to write this sub query here?Is there any solution for this?

Possible duplicate of [Doing a WHERE .. IN subquery in Doctrine 2](https://stackoverflow.com/questions/6637506/doing-a-where-in-subquery-in-doctrine-2) — rkeet, Jul 30 '19 at 11:21
The Othertable entity exist but the entity is not properly executing in my query, i have already included $injected the enity in repository. — mevr, Jul 30 '19 at 12:48
how to write the entire query in nativequery, nativequery is not working for me! — mevr, Jul 31 '19 at 10:26

score 2 · Answer 1 · answered Jul 30 '19 at 10:16

2

You should simply use the DQL of the subquery, as example:

// Don't take the query/result instances
$sub_query = $qb2->select('field')
    ->from('OtherTable','g')
    ->where("'id= '".$personId."'")
    ->OrderBy('updated_at', 'DESC')
    ->setMaxResults(1);

and use

$qb->select("contact.id, 
                contact.title, 
                count (distinct person.id) as 
number_of_contacts_with_email',(".$sub_query->getDQL().") as status")
 ->leftjoin('contact.people', 'person')
 ->leftJoin('person.jobs', 'jobs')
 ->groupBy('contact.id, contact.title');

Hope this help

answered Jul 30 '19 at 10:16

Matteo

37,680
11
100
115

Hi @Matteo Thanks for the reply, i tried your solution but am still getting this error:- [Semantical Error] line 0, col 220 near 'OtherTable g WHERE': Error: Class 'OtherTable' is not defined. – mevr Jul 30 '19 at 10:44
The problem i found is we cannot run subquery with limits in doctrine.Any other solution for this? am also checking! – mevr Jul 31 '19 at 05:35
https://github.com/doctrine/orm/issues/3979 so i think i need to write all query in SQL other than DQL for my requirement :( please correct me if am wrong ! – mevr Jul 31 '19 at 05:51

how to write sub-query in symfony doctrine

1 Answers1