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?
Support my open-source work via Github or follow me on Twitter for more blog posts and other interesting articles from around the web. I'd also love to hear your thoughts on this post - simply drop a comment below!
