Doctrine Query Builder Using Inner Join with Conditions

Doctrine query builder using inner join with conditions

I'm going to answer my own question.

1. innerJoin should use the keyword "WITH" instead of "ON" (Doctrine's documentation [13.2.6. Helper methods] is inaccurate; [13.2.5. The Expr class] is correct)
2. no need to link foreign keys in join condition as they're already specified in the entity mapping.

Therefore, the following works for me

$qb->select('c')
    ->innerJoin('c.phones', 'p', 'WITH', 'p.phone = :phone')
    ->where('c.username = :username')
    ->setParameter('phone', $phone)
    ->setParameter('username', $username);

or

$qb->select('c')
    ->innerJoin('c.phones', 'p', Join::WITH, $qb->expr()->eq('p.phone', ':phone'))
    ->where('c.username = :username')
    ->setParameter('phone', $phone)
    ->setParameter('username', $username);;

How to add multiple AND conditions in a JOIN

You can use conditionType of the leftJoin function in queryBuilder check the documentation

    public function leftJoin($join, $alias, $conditionType = null, $condition = null, $indexBy = null);

    Example:
    $qb->leftJoin('c.phones', 'p', 'WITH', 'p.is_main = 1 AND p.category = 0', 'p.id')

Symfony2/Doctrine2 innerJoin using QueryBuilder

You are working on a DQL level with tables, which means that you actually joining a table, so you just need the table name, not the entity name. The table "phonenumbers might not even had an entity to begin with, this is why Doctrine requests a table name and not an entity name.

Edit

It is actually possible to work with entity names as well as follows (taken from my own code which is working as a charm):

$builder = $this->createQueryBuilder('m');
$builder->innerJoin(
    'YourBundle:Category',
    'c',
    Join::WITH,
    $builder->expr()->eq('m.id', 'c.mdl_id')
);

To use the constants from Join you should first:

use Doctrine\ORM\Query\Expr\Join;

But this should also work (taken from documentation which means should work like a charm):

$queryBuilder
    ->select('id', 'name')
    ->from('users', 'u')
    ->innerJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id');

This is taken form: http://doctrine-orm.readthedocs.org/projects/doctrine-dbal/en/latest/reference/query-builder.html#join-clauses

How to create an inner join with Doctrine?

If your entities are mapped correctly, then querybuilder will automatically join on the columns specified in your mapping and you do not need to specify them here again:

$qb->select("a,u")
        ->from('MyBundle\Association','a')
        ->innerJoin('a.users','u')
        ->where('u.id = :id')
        ->setParameter('id',$userid);

Symfony 2: INNER JOIN on non related table with doctrine query builder

Today I was working on similar task and remembered that I opened this issue. I don't know since which doctrine version it's working but right now you can easily join the child classes in inheritance mapping. So a query like this is working without any problem:

$query = $this->createQueryBuilder('c')
        ->select('c')
        ->leftJoin('MyBundleName:ChildOne', 'co', 'WITH', 'co.id = c.id')
        ->leftJoin('MyBundleName:ChildTwo', 'ct', 'WITH', 'ct.id = c.id')
        ->orderBy('c.createdAt', 'DESC')
        ->where('co.group = :group OR ct.group = :group')
        ->setParameter('group', $group)
        ->setMaxResults(20);

I start the query in my parent class which is using inheritance mapping. In my previous post it was a different starting point but the same issue if I remember right.

Because it was a big problem when I started this issue I think it could be also interesting for other people which don't know about it.

Implementing a join in doctrine query builder not working correctly

try to change this:

->join('u', 'AppBundle:Property', 'p', 'u.property = p.id')

to this:

->join('AppBundle:Property', 'p', 'WITH', 'u.property = p.id')

QueryBuilder and inner join

Hoping it is gonna help somebody :

$qb = $this->getEntityManager()->createQueryBuilder()
  ->select('trace.action')
  ->addSelect("concat(user.nom, ' ', user.prenom)")
  ->from('MyBundle:Ttrace', 'trace');
$qb->join('MyBundle:User', 'u', Join::WITH, $qb->expr()->eq('trace.user', 'u.id'));
$qb->where($qb->expr()->isNull('trace.datfin');

---

Related Topics