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’, ‘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!

For more information on the Doctrine Project, check out http://www.doctrine-project.org/.

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();

Casey

Hi! I'm Casey, a web developer/programmer, my goal with this blog is to share my experience with web programming to hopefully help others. I consider myself a jack of all trades programmer. While I'm more comfortable working in the back-end code I often find myself in the UX world. I'm primary a LAMP (Linux, Apache, MySQL, PHP) developer but have done some work with .NET, ColdFusion, and Java.

4 thoughts on “Joining a Subquery to Doctrine QueryBuilder

  1. your example doesn’t run, too many error: corrected below

    $query = $connection->createQueryBuilder();

    $searchIds = array(‘2345′,’A567’);

    $query->select(“emp.id, emp.first_name, emp.last_name, emp.email, emp.phone”)
    ->from(“employeeMain”, “emp”)
    ->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(“emp”, “(“.$subQuery->getSQL().”)”, “sub”, “empMain.id = sub.empId”)
    ->addSelect(“sub.jobTitle”);

    $sql = $query->getSQL();

    echo “selectQuery: ” . print_r($sql ,true) .” “;

      1. this looks better:
        $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();

        1. Hi Moya!

          Nice work! Sorry for the broken code and awful example. My main point of this post was to show that you can hack in a subquery into Doctrine’s join methods, so I guess I didn’t focus too much on the example. I will make sure to do better with my future examples and code! 🙂

          Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *