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’, ‘t1.id = t2.id’), Doctrine is doing this behind the scenes: JOIN tbl2 t2 ON t1.id = t2.id. 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.id, emp.first_name, emp.last_name, emp.email, emp.phone")
->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))
->groupBy("emp.empId");
Put it all together now!
$query->leftJoin("empMain", "(".$subQuery->getSQL().")", "sub", "empMain.id = sub.empId")
->addSelect("sub.jobTitle");
Cheers!
Edit:
The code above does not work… Here is a working example of my example posting in my comment by Moya:
$query = $connection->createQueryBuilder();$searchIds = array(‘2345′,’A567’);$query->select(“empMain.id, empMain.first_name, empMain.last_name, empMain.email, empMain.phone”)->from(“employeeMain”, “empMain”)->where($query->expr()->in(“empId”, $searchIds));
$subQuery = $connection->createQueryBuilder();
$subQuery->select(“GROUP_CONCAT(emp.jobTitle) AS jobTitle, emp.empId”)
->from(“employeeMeta”, “emp”)
->where(“emp.locationId = ‘345’”)
->andWhere($query->expr()->in(“empId”, $searchIds))
->groupBy(“emp.empId”);
$query->leftJoin(“empMain”, “(“.$subQuery->getSQL().”)”, “sub”, “empMain.id = sub.empId”)
->addSelect(“sub.jobTitle”);
$sql = $query->getSQL();