Joining a Subquery to Doctrine QueryBuilder

I was faced with an odd situation the other day and couldn’t find anything on the Internet to point me the right direction… I needed to join a subquery to my query using Doctrine’s QueryBuilder. I saw nothing promising on my searches and nothing in Doctrine’s docs. After some tinkering I discovered that I could slap in a query within join functions. I ended up creating the subquery as a new QueryBuilder instance and spit out the SQL into the a join function.  This should work on all their join functions: join(), innerJoin(), leftJoin(), and rightJoin(). Here’s how it works:

If you look at what the functions are doing it will click.

From the API:

join( string $fromAlias, string $join, string $alias, string $condition = null )

So when you do this: $qb->join(‘t1’, ‘tbl2’, ‘t2’, ‘ =’), Doctrine is doing this behind the scenes: JOIN tbl2 t2 ON =  So instead of adding a table as the $join variable, I dump SQL in there.

Here is a sloppy example for you:

Lets start with the base query…

$qb =$conn->createQueryBuilder();
$qb->select(", emp.first_name, emp.last_name,,")
->from("employeeMain", "emp")
->where($query->expr()->in("empId", $searchIds));

Now our sub query…

$subQuery = dbal()->createQueryBuilder();
$subQuery->select("GROUP_CONCAT(emp.jobTitle) AS jobTitle,  emp.empId")
->from("employeeMeta",  "emp")
->where("emp.locationId = '345'")
->andWhere($query->expr()->in("empId", $searchIds))

Put it all together now!

$query->leftJoin("empMain", "(".$subQuery->getSQL().")", "sub", " = sub.empId")


For more information on the Doctrine Project, check out

Read More


Welcome to the new!  My goal for this rendition is to regularly post thoughts, experiences, solutions I come up with while programming.  Hopefully I can help someone that is having similar problems that I have experienced and figured out.  Please post comments, let me know if what I came up with was helpful or just plan wrong! 😛 Welcome and enjoy!

Read More