15

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?

timdev
  • 61,857
  • 6
  • 82
  • 92

3 Answers3

30

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.

Ian Phillips
  • 2,027
  • 1
  • 19
  • 31
  • This works nicely even for entities with composite keys. – Wilt Mar 09 '15 at 10:57
  • If I could vote this answer up once more I would do it! – Wilt Jun 24 '15 at 09:40
  • Thanks for this! In DQL for the record: SELECT o FROM Order LEFT JOIN AmazonOrder ao WITH o.id = ao.id – omgitsdrobinoha Nov 11 '15 at 15:53
  • In Doctrine 2.1, throws Fatal error: Maximum function nesting level of '1000' reached – Vasily802 Nov 26 '15 at 05:07
  • You can even query for a specific set of subclasses. Left join all the subclasses you need and add a where clause that requires your results to be an instance of one of the subclasses. `SELECT o FROM Order LEFT JOIN AmazonOrder ao WITH o.id = ao.id LEFT JOIN EbayOrder eo WITH o.id = eo.id WHERE (o INSTANCE OF AmazonOrder OR o INSTANCE OF EbayOrder)`This way any OtherOrder Entities that might exist will be excluded. – patrickj Sep 01 '16 at 19:52
  • 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')```. That's useful if there is no association between superclass and subclass – vincent PHILIPPE Dec 08 '20 at 11:53
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.

Max
  • 15,693
  • 14
  • 81
  • 131
  • Thanks Max. I've avoided the issue for now by working around it with UI. It's not optimal, and I'll revisit it soon. I'd really like to avoid moving stuff to the superclass. Sounds like I was right in thinking there was no straightforward solution. If I come up with anything clever when I revisit the issue, I'll let you know. – timdev Oct 12 '11 at 19:47
0

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.

vincent PHILIPPE
  • 975
  • 11
  • 26