Symfony2 - Doctrine DQL - many to many query

Question

I have entities Offer and Area and here one Offer can have many Areas and one Area belong to many Offers:

Offer entity:

/**
 * @ORM\ManyToMany(targetEntity="Area")
 * @ORM\JoinTable(name="relationship_offer_areas",
 *      joinColumns={@ORM\JoinColumn(name="offer_id", referencedColumnName="id", onDelete="CASCADE")},
 *      inverseJoinColumns={@ORM\JoinColumn(name="area_id", referencedColumnName="id")}
 *      )
 */
private $areas;

Now I am trying to get Offers by Area using DQL (not query builder!):

$query = 'SELECT o FROM IndexBundle:Offer o '.
    'LEFT JOIN IndexBundle:Area a '.
    'WHERE a = :area '.
    'ORDER BY o.startDate ASC';

Here :area is Area entity object. Unfortunately it is not working as expected. I get all offer rows with all the areas.

Any ideas what am I missing? Working with Entities in query language really twist my mind. Thank you!

Answer 1

$query = 'SELECT o FROM IndexBundle:Offer o '.
'LEFT JOIN o.areas a '.
'WHERE a.id = :areaId '.
'ORDER BY o.startDate ASC';

It doesn't know how to use the JOIN on the fly. Use the properties of your entities for joining. It's is just like you do a LEFT JOIN within SQL as you refer to a column where you want additional data from. As you specify in the ON of the join.

Left join using DQL - See documentation: Doctrine DQL - Select examples

$query = $em->createQuery('SELECT u.id, a.id as article_id FROM CmsUser u LEFT JOIN u.articles a');
$results = $query->getResult(); // array of user ids and every article_id for each user