Have you tried to construct a complex query in Rails to do joins and order by different columns and have fallen into writing SQL because you could not construct a query using ActiveRecord’s querying DSL, then this blog post might help.
Rails provides a way to merge complex queries using a method called
merge and it works like this.
To understand what kind of queries we can create lets consider few models:
These models are associated in the follow way. A Person has
belongs_to :location, a Location
has_many :people and
belongs_to :region and a Region
has_many :locations. Person, Location and Region also have a
Lets say we want to get all the people that are in a specific Region given a region name. This requires us to do an INNER and a LEFT JOIN across three tables and the query to do so looks like this:
To construct such a query you can split the problem into smaller queries and merge them using
Here is the code:
If you look at the problem now it is split in such a way that Location now has a method called
in_region which returns all Locations within a Region. Using that we can
merge this query with the query for finding people within locations that are within a region:
This opens up the possibility to do even more advance queries while using ActiveRecord’s DSL. It introduces smaller queries that can be reused by your Rails app, makes your code more cohesive by having Location class take care of the responsibility of finding Locations within a region and also makes the code easy to test by introducing smaller queries that can be tested instead of constructing a big monolothic query.