Don’t be afraid to NOT use the ORM…

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:

  1. 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.

  2. 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.

  3. 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.

Post a Comment

Your email address is never published nor shared. Required fields are marked *

Ready to talk?

Whether you want to create a new digital product or make an existing one even better, we'd love to talk it through.

Get in touch