Avoiding Joins to Reference/Lookup Tables in Doctrine 2

Question

In my application, I have a a number of simple reference/lookup database tables used for supplying a list of permitted values in a related table.

(You know, a 'Countries' table has a list of countries that are permitted in the 'country' field of the Address table...)

To keep my data model as lean as possible, I use the "Bill Karwin technique" of skipping the 'id' column in the lookup table and just using the actual value as the primary key. That way, you don't need to do a join to get the value in the main table because it's already there as the foreign key.

The problem is, Doctrine uses object references for all associations, which means that queries still require joins to the lookup tables -- even when the main table already has the values I need.

For example, this query doesn't work:

$qb->select(array('a.id', 'a.street', 'a.city', 'a.country'))
   ->from('Entity\Address', 'a');

Instead, you have to do this:

$qb->select(array('a.id', 'a.street', 'a.city', 'c.country'))
   ->from('Entity\Address', 'a')
   ->join('a.country', 'c');

Otherwise you get this error: "Invalid PathExpression. Must be a StateFieldPathExpression."

Add up all the joins needed for lookup tables, and there's a lot of unnecessary cost in my queries.

Does anyone know a good way to avoid having to perform joins to lookup/reference tables in Doctrine 2?

(P.S. - I'd prefer to avoid using ENUMs, as they're not supported by Doctrine and have other well-documented disadvantages.)

Answer 1

Yes, this kind of sucks, but I guess they had a good reason for doing it that way.

You can use the HINT_INCLUDE_META_COLUMNS hint. It will include all the fields in the query result, including foreign keys which are mapped as relations.

$query = \Doctrine::em()->createQuery($queryString)
    ->setParameters($params)
    ->setHint(\Doctrine\ORM\Query::HINT_INCLUDE_META_COLUMNS, TRUE);

So if your you have a field city_id in the Address table in the db, it will also be outputted in the query result, along with the "standard" City relation.