Using virtual fields in Doctrine_Query

Question

Is there a way to insert logic based on virtual fields into a Doctrine_Query?

I have defined a virtual field in my model, "getStatus()" which I would ultimately like to utilize in a Where clause in my Doctrine_Query.

...
->AndWhere('x.status = ?',$status);

"status", however, is not a column in the table it is instead computed by business logic in the model.

Filtering the Collection after executing the query works in some situations, but not when a Doctrine_Pager is thrown in the mix, as it computes it's offsets and such before you have access to the Collection.

Am I best off ditching Doctrine_Pager and rebuilding that functionality after modifying the Doctrine_Collection?

Answer 1

If you can do it in SQL you can do it in Doctrine. All doctrine is doing is working out what you are putting into the DQL parser, be it strings or values and turning that into SQL then hydrating objects from the result.

You can't use Doctrine_Pager to page on non query objects, however you could use sfPager and pass it the results of the Doctrine_Collection as an array? In the worst case you could pass it the results of the query minus any limits in the query and let it handle the paging, however this is really inefficient.

It might be quicker to write the pager "old skool" like you would in plain old PHP.

Answer 2

I don't really know what business logic you're applying to work out the status, but if it's not live (as in, computed per request), I'd compute it on save (using a Doctrine Record Listener or simply a preSave/preInsert hook in the model) and store it in the table, or set up a symfony task to refresh it periodically and run that as a cronjob. That would let you query it in Doctrine just fine and boost performance as a fringe benefit.

Alternatively, if status is dependent on the state of related objects, you can put an event trigger on them that updates the status of the parent object when they're modified. It's hard to recommend a best approach without more context. :)