Doctrine2: Polymorphic Queries: Searching on properties of subclasses

Question

I've got a project where I deal with customer orders. Some of those orders are made via Amazon.com. So I've got an Order entity, and an AmazonOrder entity that extends it. One thing added by AmazonOrder is the AmazonOrderId.

I've a requirement to implement a broad search feature. The user can enter some stuff into a text box, and be used in a bunch of expressions in one big where-clause. So, for example, if the user searched for "111", the results include any orders with an ID starting with 111, any order being shipped to zip codes that begin with 111, any order being shipped to "111 Main St", etc.

That stuff is implemented with a query-builder-created query that has a big orX() expression.

Now, I'd like to match against all Orders, but if they're an AmazonOrder, also match against AmazonOrderId.

And I'm stuck -- I suspect it may not be possible

Here's how I'm building up the query:

$qb->select('o,s')->from('PMS\Entity\Order', 'o');    
$qb->leftJoin('o.shippingInfo','s');
$qb->andWhere('o.status = :status');
$qb->setParameter('status',$status);
$qb->andWhere(
    $qb->expr()->orX(
        $qb->expr()->like('o.id',':query')
        $qb->expr()->like('s.address',':query')
        $qb->expr()->like('s.city',':query')
    )
);
$qb->setParameter('query',$userQuery .'%');

$orders = $qb->getQuery()->getResult();

And I can't figure out how to add a condition that says, roughly, "OR (Order is an AmazonOrder AND AmazonOrderId LIKE '$userQuery%')"

Anyone have any insight? Either a way to handle this, or at least a confirmation that it's not doable this way?

Answer 1

Here's another solution that works for me with Doctrine 2.4:

$qb->select('o')
   ->from('Order', 'o')
   ->leftJoin('AmazonOrder', 'ao', 'WITH', 'o.id = ao.id')
   ->andWhere('o.id like :query or ao.amazonOrderId like :query')
   ->setParameter('query', $someQuery);

You just left-join the entity on the specific subclass of itself. (You can adapt my simple query to your use case.)

I've tried this exactly once, but it seems to work.

Answer 2

Hm, I had similiar problems in my last doctrine project.

One time it was just a single field, so I moved it to the parent class – not the nicest solution, but worked. In some other case there where too many properties so these would have cluttered the parent class. I did a native sql query for searching and fetching me the record ids and then used a WHERE IN (...) dql in order to fetch the entities.

A compromise might be the doctrine ResultSetMapping which can map a native sql query to entities directly, although every time I worked with it I found it quite clumsy to use and the overhead for two queries (fetch ids & fetch entites) as outlined above to be neglectable.

Maybe you could accomplish something with the INSTANCEOF operator in your WHERE clause, although I dont think doctrine would be smart enough to recognize it the way you want.