Active Record

When a friend first told me about Doctrine many, many years ago I thought it sounded like a terrible idea. I have also had a love affair with SQL and the thought of having to use objects and functions to interact with the database instead of writing queries and getting the data you needed was overly complicated and didn't add anything. When I first started using frameworks to code I felt the same way about them - it seemed to add unneeded layers of complexity to have to incorporate code written by someone else to do something I could very easily do on my own.

It wasn't until I started using Laravel that I came to appreciate frameworks - I could write a simple CRUD that would have taken me days writing from scratch in a couple of hours thanks to the functionality built into the framework. And the ORM made things a lot easier as far as database interaction, but only within certain parameters. I realized this when I tried to normalize a table in my database by separating out a field into another related table. It became very complicated and convoluted to do simple things like sort the query by a value in the related table. I ended up denormalizing the database and getting rid of the extra table to keep the code clean. 

At the time I assumed that when you set up a relationship between models when you queried one Laravel would join the other tables in to get all the needed data in one query. It wasn't until I installed debugbar that I realized that laravel would do n+1 queries to get n rows of data with one relationship. This issue is avoidable using Laravel's "eager loading" - which will get the same data in 2 queries. However to get data from multiple tables in one query - using a JOIN - and specifying WHEREs and ORDER BYs on the joined tables, the Query Builder syntax gets quite ugly. 

In my opinion this is largely because Eloquent is an implementation of the Active Record pattern, which represents one row of one table in the database as an object as far as the code is concerned. In Eloquent, if you query multiple rows you are returned a Collection of these model objects. While Active Record is great for dealing with simple databases where one row contains usable data, if you are dealing with a highly relational database where you need data from multiple tables it doesn't hold up so well.

When I started out programming we were using MS SQL Server and the programmers were not allowed to write any queries - we were to use stored procedures written by the DBA. At the time I didn't understand the reason for this, but now I realize that it allows the database structure to be separate from the code - so that database changes won't result in needing to rewrite large sections of code. This, in my opinion, is the main advantage of using an ORM. So what do you do when you need to write actual SQL queries for your code to work properly and efficiently?

One option I investigated was adding a Repository layer to the code. With the repository pattern the models handle the reading from and writing to the database, but the repository interacts with the code. For my needs, the repository would basically act the part of the SPs - the queries would be written in them and the code would call the methods in the Repository to get the data they needed. Basically it just put all of the queries in one place so that if the database was ever changed the queries that needed to be rewritten would all be in one place. I tried implementing this, and it worked, but it added another level of abstraction and complexity. And the way I implemented it was basically no different from writing the queries directly into the models, which works just as well, but for some reason it bothers me to have complex and bloated models.

I am still working through this issue and do not have a solution yet. Dealing with this has made me remember the many issues I had with ORMs and frameworks in general back when I first started using them. Using the tools of the ORM the issues can be addressed - but not in a simple, clean and elegant manner. And in my opinion, the main problem is the Active Record pattern itself - it is great if you need to work with a single row in a single table, but if you need to span multiple tables to get your data, it doesn't hold up so well.