On many occasions we need to load multiple records with associations from a database. The easiest way is to access this is via iteration (.each) written in Ruby. This is not the best approach as it can result in issuing 1+n queries (complexity O(n)) instead of single query (O(1)).
Using iteration isn’t optimal because as the count of records increases, the rendering of the page gets slower and slower, and can easily result in 30 second to 5 minute rendering times.
Our Example Models
To show the code in action, I’ve created a database of items and item sets from World of Warcraft.
https://gist.github.com/dayweek/1b6497854158e21ecb440e231e29563d
preload()
preload() executes new SQL statement for each association we reference.
https://gist.github.com/dayweek/da74fc359aa9e1e28ffd77d1c6c152a6
However you cannot use the WHERE condition in the association.
https://gist.github.com/dayweek/ca2998bdec0ce4ea3099934c33a6b20f
The biggest advantage of loading all associations with a single query is there is no need to execute SQL when we iterate over the associated records. This applies to the following methods as well.
eager_load()
eager_load() uses always LEFT OUTER JOIN to load the association. The same query works here.
https://gist.github.com/dayweek/25c25b8f2f9ab5f851eda0018989e453
joins()
joins() can take two types of parameter. Joins() with symbol as a parameter does always INNER JOIN:
https://gist.github.com/dayweek/289bbd4e62ec45eec0ed31a178af42c2
The second type of parameter is a custom string. You should use this only when absolutely necessary. You can perform LEFT OUTER JOIN for example.
https://gist.github.com/dayweek/73f1d2c6971d4b51731ebf27b1fa3302
includes()
The easiest way to preload association(s) is to use the includes() method.
https://gist.github.com/dayweek/bb33c72e548eac715ce142807b1a5ee6
In the next example we add constraints to ItemSet. This works well as expected, loading the associated Item as well.
https://gist.github.com/dayweek/0247fd363301ea91c3a3e4b6ad872027
Each argument in includes() is an association, this means it’s easy to include multiple associations. In this example, ActiveRecord issues three queries (one for the model and one per each association).
https://gist.github.com/dayweek/ba2d925ce0f9accda99d2873bdf5d056
includes() uses internally preload() or eager_load(). It depends on whether you use references() or not (see the next section). You can ask – why not to use LEFT JOIN every time? Using the WHERE…IN statement is faster than “joins.”
references()
Perhaps you’ve seen the references() method used along with includes(). Our goal is to get items from The Gladiator set with armor greater than 22.
https://gist.github.com/dayweek/24073f0220139fad6b640ffd982e180a
OH NO! ;-( We need to tell ActiveRecord that we reference included table which changes the query from two SELECTs to LEFT JOIN.
https://gist.github.com/dayweek/8e9be7340e2c66a1773be558c67c869c
Now ActiveRecord did some crazy LEFT JOIN to prefetch the data. It does LEFT JOIN for every argument in includes().
Conclusion
I’ve shown you the most efficient ways to use preloading data from associations in Ruby instead of writing SQL statements manually. The presented methods are more elegant, maintainable and less error prone. However, I urge you to employ the methods in your application for yourself to see what works best for you.
References:
http://blog.arkency.com/2013/12/rails4-preloading/
http://blog.bigbinary.com/2013/07/01/preload-vs-eager-load-vs-joins-vs-includes.html
Ishael Bloodlight from DeviantArt