Hi again to everybody, and specially thanks to Winou for your big patience,
without your help I would have been stucked in a huge
performance hole in my application.
I must remark, though, that Doctrine's dql doesn't support the clause 'ON'
directly, it uses the 'WITH' clause instead.
And this is how I solved my problem:
I have many bedrooms, and one bedroom can have many photos, but some photos
are special, so they have a 'is_special' attribute.
What I needed was to fetch all bedrooms, even if they have or not photos
(left join) but also fetch all photo that has the is_special attribute set
to true.
If you use a 'WHERE photo.is_special = true' or 'HAVING photo.is_special =
true' clause, the left join will return a partial set of bedrooms (only
those that has at least one special photo). I needed all bedrooms, and get
the special photos if any in one query.
In Doctrine you have two easy ways to do it:
1) Plain DQL:
$dql=' SELECT b, p FROM Vendor\MyBundle\Entity\Bedroom b ';
$dql.=' LEFT JOIN b.photos p WITH p.is_special=1';
$query = $this->getEntityManager()->createQuery($dql);
return $query->getResult();
2) The QueryBuilder:
$query = $this->getEntityManager()->createQueryBuilder();
$query->select('b, p')
->from('Vendor\MyBundle\Entity\Bedroom', 'b')
->leftJoin('b.photos', 'p',
\Doctrine\ORM\Query\Expr\Join::WITH, 'p.is_special = :is')
->setParameter('is', true)
;
return $query->getQuery()->getResult();
That's it. Hope this helps if someone have a problem like mine's.
2011/6/7 oscar balladares <liebegr...@gmail.com>
> Hi! Thank you very much. I will try this query, though it will take me some
> time to merge to my app.
>
> For now thanks buddy, and have a good night! (It is very late for me :D )
>
> 2011/6/7 winzou <alexandre.ba...@gmail.com>
>
>> Ok, you're stuck on an pure SQL issue. Check this query:
>> SELECT a, b FROM objectA a LEFT JOIN objectB b ON (objectB.id_a =
>> objectA.id AND objectB.is_public_content = true)
>>
>> The trick is to put the condition in the ON, not in the WHERE.
>>
>> --
>> If you want to report a vulnerability issue on symfony, please send it to
>> security at symfony-project.com
>>
>> You received this message because you are subscribed to the Google
>> Groups "symfony users" group.
>> To post to this group, send email to symfony-users@googlegroups.com
>> To unsubscribe from this group, send email to
>> symfony-users+unsubscr...@googlegroups.com
>> For more options, visit this group at
>> http://groups.google.com/group/symfony-users?hl=en
>>
>
>
--
If you want to report a vulnerability issue on symfony, please send it to
security at symfony-project.com
You received this message because you are subscribed to the Google
Groups "symfony users" group.
To post to this group, send email to symfony-users@googlegroups.com
To unsubscribe from this group, send email to
symfony-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/symfony-users?hl=en
