Doctrine2: Polymorphic Queries: Searching on properties of subclasses

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?

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.

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.

If you need to join a subclass to get a fields only present into this subclass, you may want to use this syntax :

...->from('From', 'X')->leftJoin('App\Entity\Class\Name', 'Y', \Doctrine\ORM\Query\Expr\Join::WITH, 'Y.id = X.id')

Note that, as it's left join, it comes with a performance hit.

Also note that doctrine will automaticaly join all child entity of a mapped superclass no matter what you're doing.

There's a github issue about this behaviour : https://github.com/doctrine/orm/issues/5980

I would like to add that if you don't need to access any fields of the subclasses, you can just join the superclass and filter with a where INSTANCE OF subclass. I suggest you adding the discriminator columns and the superclass id into an index as :

* @ORM\Table(indexes={@ORM\Index(name="idx_partition_type", columns={"id", "type"})})
* ...
* @ORM\DiscriminatorColumn(name="type", columnDefinition="CHAR(1) NOT NULL")
abstract class superclass{
   ...
}

This would be usefull only for non-instantiable, abstract class. Else, if your class isn't abstract, doctrine will leftJoin any related child entity no matter what happens.

I just wanna share all of this because it helped me.