I’ve been racking my brains the past couple of days with a Doctrine issue within a Symfony project. I was constructing a bank statement-esque page, which iterated over a “StatementEntry” table and displayed the results. Entries could either be of model Type A or model Type B, with the relevant model ID stored in the StatementEntry table. The relationship was defined by a “type” column. Type A and B models also had other relationships after the initial one.
The problem I discovered was that obviously Doctrine didn’t know what the conditional relationships were, since it was dependant on the type column. I researched some different approaches:
- Doctrine’s RawSql
This approach worked for generating the correct SQL query without a problem, but the issue then arose of how Doctrine could hydrate the result set. Answer was – it couldn’t :-) Well, I lie – I could use Doctrine::HYDRATE_SCALAR but I had issues with other related models, where I was joining to a table twice but under different aliases, which failed, since the scalar hydration would override earlier joins on the same table.
- Column aggregation
Column aggregation looked great initially – I could have a “TypeAEntry” and a “TypeBEntry”, and Doctrine would have handled the typing of the Entry automatically. I couldn’t however see a way to query “in reverse” – get all Entries and their associated models.
- Write a raw SQL query, and use Doctrine’s PDO instance to retrieve the data.
Option 3) was the one I settled for in the end. It meant of course that I had to specify column names, and perform all the joins myself, but it did mean I could specify precisely what data I wanted back. This came back in the form of a normal array. I’d tried to stay away from this option intially, but after spending too long researching the other 2 and hitting brick walls, I bit the bullet and dropped down to the raw stuff. And hey presto!
Note – this approach will only work if you want the data to be read-only. If you want to then use Doctrine’s model goodness, you’ll need to work out how to transform that *back* into a model.