Doctrine 2 – Use foreign key as field in DQL

In doctrine 2 there are by default no foreign key fields a ID. For example your “product” entity might have a “shop” property, but no “shopId” property.

To get all the products that belong to a shop you would always have to make a join like this:

/* @var $em \Doctrine\ORM\EntityManager */
$result = $em->createQuery("SELECT p FROM My\Entity\Product p
JOIN p.shop s WHERE s.id = :shopId"
)->setParameter('shopId', 1)
->getOneOrNullResult();

But in certain situations it would be nice to use the foreign key column directly, in this case “shopId”. That way we can use the following query:

/* @var $em \Doctrine\ORM\EntityManager */
$result = $em->createQuery("SELECT p FROM My\Entity\Product p
WHERE p.shopId = :shopId"
)->setParameter('shopId', 1)
->getOneOrNullResult();

Making this possible is very simple. Just add the property shopId to the product entity as any other field mapping property, and so that it corresponds with the actual column in the database table. Make sure you put this property ABOVE the association property “shop”, otherwise it will not work as expected.

This solutions goes against the philosophy of an ORM that works trough entity associations. However, the doctrine developers them self say it’s not recommended, but at the same time not forbidden. It can be useful in certain situations.