RIGHT JOINs in Doctrine 2

I recently needed the ability to perform a RIGHT JOIN in a Symfony project. While Doctrine's DBAL library seems to support this, the ORM's QueryBuilder does not. Unfortunately, the suggested workaround of inverting the query wouldn't work in my situation. I also didn't feel like rewriting the query into DQL, so I ultimately hacked in my own support by duplicating the LEFT JOIN functionality. I figured I'd share my patch in case it helps others facing a similar issue.

While this patch worked for my particular case, these changes have not been thoroughly tested and may not function correctly for your use case. Use with caution!
commit a433c6505f9677241e7d75a18d292a4e336173e4
Author: Colin O'Dell <[email protected]>
Date:   Wed Jun 7 22:06:32 2017 -0400

    Implement basic support for RIGHT joins

diff --git a/lib/Doctrine/ORM/Query/AST/Join.php b/lib/Doctrine/ORM/Query/AST/Join.php
index 5c203aa..a51cd09 100644
--- a/lib/Doctrine/ORM/Query/AST/Join.php
+++ b/lib/Doctrine/ORM/Query/AST/Join.php
@@ -20,7 +20,7 @@
 namespace Doctrine\ORM\Query\AST;
 
 /**
- * Join ::= ["LEFT" ["OUTER"] | "INNER"] "JOIN" JoinAssociationPathExpression
+ * Join ::= ["LEFT" ["OUTER"] | "RIGHT" | "INNER"] "JOIN" JoinAssociationPathExpression
  *          ["AS"] AliasIdentificationVariable [("ON" | "WITH") ConditionalExpression]
  *
  * @link    www.doctrine-project.org
@@ -34,6 +34,7 @@ class Join extends Node
     const JOIN_TYPE_LEFT      = 1;
     const JOIN_TYPE_LEFTOUTER = 2;
     const JOIN_TYPE_INNER     = 3;
+    const JOIN_TYPE_RIGHT     = 4;
 
     /**
      * @var int
diff --git a/lib/Doctrine/ORM/Query/Expr/Join.php b/lib/Doctrine/ORM/Query/Expr/Join.php
index 7a59e24..16d5201 100644
--- a/lib/Doctrine/ORM/Query/Expr/Join.php
+++ b/lib/Doctrine/ORM/Query/Expr/Join.php
@@ -32,6 +32,7 @@ class Join
 {
     const INNER_JOIN    = 'INNER';
     const LEFT_JOIN     = 'LEFT';
+    const RIGHT_JOIN    = 'RIGHT';
 
     const ON            = 'ON';
     const WITH          = 'WITH';
@@ -67,7 +68,7 @@ class Join
     protected $indexBy;
 
     /**
-     * @param string      $joinType      The condition type constant. Either INNER_JOIN or LEFT_JOIN.
+     * @param string      $joinType      The condition type constant. Either INNER_JOIN, LEFT_JOIN, or RIGHT_JOIN.
      * @param string      $join          The relationship to join.
      * @param string|null $alias         The alias of the join.
      * @param string|null $conditionType The condition type constant. Either ON or WITH.
diff --git a/lib/Doctrine/ORM/Query/Lexer.php b/lib/Doctrine/ORM/Query/Lexer.php
index d5721a7..b415023 100644
--- a/lib/Doctrine/ORM/Query/Lexer.php
+++ b/lib/Doctrine/ORM/Query/Lexer.php
@@ -109,6 +109,7 @@ class Lexer extends \Doctrine\Common\Lexer
     const T_WITH                = 155;
     const T_PARTIAL             = 156;
     const T_NEW                 = 157;
+    const T_RIGHT               = 158;
 
     /**
      * Creates a new query scanner object.
diff --git a/lib/Doctrine/ORM/Query/Parser.php b/lib/Doctrine/ORM/Query/Parser.php
index 695e7ed..ee58231 100644
--- a/lib/Doctrine/ORM/Query/Parser.php
+++ b/lib/Doctrine/ORM/Query/Parser.php
@@ -1564,6 +1564,7 @@ class Parser
         while (
             $this->lexer->isNextToken(Lexer::T_LEFT) ||
             $this->lexer->isNextToken(Lexer::T_INNER) ||
+            $this->lexer->isNextToken(Lexer::T_RIGHT) ||
             $this->lexer->isNextToken(Lexer::T_JOIN)
         ) {
             $joins[] = $this->Join();
@@ -1635,7 +1636,7 @@ class Parser
     }
 
     /**
-     * Join ::= ["LEFT" ["OUTER"] | "INNER"] "JOIN"
+     * Join ::= ["LEFT" ["OUTER"] | "RIGHT" | "INNER"] "JOIN"
      *          (JoinAssociationDeclaration | RangeVariableDeclaration)
      *          ["WITH" ConditionalExpression]
      *
@@ -1660,6 +1661,11 @@ class Parser
                 }
                 break;
 
+            case ($this->lexer->isNextToken(Lexer::T_RIGHT)):
+                $this->match(Lexer::T_RIGHT);
+                $joinType = AST\Join::JOIN_TYPE_RIGHT;
+                break;
+
             case ($this->lexer->isNextToken(Lexer::T_INNER)):
                 $this->match(Lexer::T_INNER);
                 break;
diff --git a/lib/Doctrine/ORM/Query/SqlWalker.php b/lib/Doctrine/ORM/Query/SqlWalker.php
index 3dea8de..812c633 100644
--- a/lib/Doctrine/ORM/Query/SqlWalker.php
+++ b/lib/Doctrine/ORM/Query/SqlWalker.php
@@ -990,7 +990,13 @@ class SqlWalker implements TreeWalker
                 $sql .= $joinTableName . ' ' . $joinTableAlias . ' ON ' . implode(' AND ', $conditions);
 
                 // Join target table
-                $sql .= ($joinType == AST\Join::JOIN_TYPE_LEFT || $joinType == AST\Join::JOIN_TYPE_LEFTOUTER) ? ' LEFT JOIN ' : ' INNER JOIN ';
+                if ($joinType == AST\Join::JOIN_TYPE_LEFT || $joinType == AST\Join::JOIN_TYPE_LEFTOUTER) {
+                    $sql .= ' LEFT JOIN ';
+                } elseif ($joinType == AST\Join::JOIN_TYPE_RIGHT) {
+                    $sql .= ' RIGHT JOIN ';
+                } else {
+                    $sql .= ' INNER JOIN ';
+                }
 
                 $conditions      = array();
                 $relationColumns = ($relation['isOwningSide'])
@@ -1116,9 +1122,13 @@ class SqlWalker implements TreeWalker
         $joinType        = $join->joinType;
         $joinDeclaration = $join->joinAssociationDeclaration;
 
-        $sql = ($joinType == AST\Join::JOIN_TYPE_LEFT || $joinType == AST\Join::JOIN_TYPE_LEFTOUTER)
-            ? ' LEFT JOIN '
-            : ' INNER JOIN ';
+        if ($joinType == AST\Join::JOIN_TYPE_LEFT || $joinType == AST\Join::JOIN_TYPE_LEFTOUTER) {
+            $sql = ' LEFT JOIN ';
+        } elseif ($joinType == AST\Join::JOIN_TYPE_RIGHT) {
+            $sql = ' RIGHT JOIN ';
+        } else {
+            $sql = ' INNER JOIN ';
+        }
 
         switch (true) {
             case ($joinDeclaration instanceof \Doctrine\ORM\Query\AST\RangeVariableDeclaration):
@@ -1126,7 +1136,7 @@ class SqlWalker implements TreeWalker
                 $dqlAlias   = $joinDeclaration->aliasIdentificationVariable;
                 $tableAlias = $this->getSQLTableAlias($class->table['name'], $dqlAlias);
                 $condition = '(' . $this->walkConditionalExpression($join->conditionalExpression) . ')';
-                $condExprConjunction = ($class->isInheritanceTypeJoined() && $joinType != AST\Join::JOIN_TYPE_LEFT && $joinType != AST\Join::JOIN_TYPE_LEFTOUTER)
+                $condExprConjunction = ($class->isInheritanceTypeJoined() && $joinType != AST\Join::JOIN_TYPE_LEFT && $joinType != AST\Join::JOIN_TYPE_LEFTOUTER && $joinType != AST\Join::JOIN_TYPE_RIGHT)
                     ? ' AND '
                     : ' ON ';
 
diff --git a/lib/Doctrine/ORM/QueryBuilder.php b/lib/Doctrine/ORM/QueryBuilder.php
index e7c603d..d13052a 100644
--- a/lib/Doctrine/ORM/QueryBuilder.php
+++ b/lib/Doctrine/ORM/QueryBuilder.php
@@ -1022,6 +1022,41 @@ class QueryBuilder
     }
 
     /**
+     * Creates and adds a right join over an entity association to the query.
+     *
+     * The entities in the joined association will be fetched as part of the query
+     * result if the alias used for the joined association is placed in the select
+     * expressions.
+     *
+     * <code>
+     *     $qb = $em->createQueryBuilder()
+     *         ->select('u')
+     *         ->from('User', 'u')
+     *         ->rightJoin('u.Phonenumbers', 'p', Expr\Join::WITH, 'p.is_primary = 1');
+     * </code>
+     *
+     * @param string      $join          The relationship to join.
+     * @param string      $alias         The alias of the join.
+     * @param string|null $conditionType The condition type constant. Either ON or WITH.
+     * @param string|null $condition     The condition for the join.
+     * @param string|null $indexBy       The index for the join.
+     *
+     * @return QueryBuilder This QueryBuilder instance.
+     */
+    public function rightJoin($join, $alias, $conditionType = null, $condition = null, $indexBy = null)
+    {
+        $parentAlias = substr($join, 0, strpos($join, '.'));
+
+        $rootAlias = $this->findRootAlias($alias, $parentAlias);
+
+        $join = new Expr\Join(
+            Expr\Join::RIGHT_JOIN, $join, $alias, $conditionType, $condition, $indexBy
+        );
+
+        return $this->add('join', array($rootAlias => $join), true);
+    }
+
+    /**
      * Sets a new value for a field in a bulk update query.
      *
      * <code>

This patch is based on Doctrine 2.5.2 but may apply cleanly to other versions. You should be able to easily apply it to your Symfony site using the cweagans/composer-patches plugin.

If you found this to be useful (or found a bug) please let me know in the comments!

Enjoy this article?

About Colin O'Dell

Colin O'Dell

Colin O'Dell is a Senior Software Engineer at SeatGeek. In addition to being an active member of the PHP League and maintainer of the league/commonmark project, Colin is also a PHP docs contributor, conference speaker, and author of the PHP 7 Migration Guide.