Getting Started with Active Record Querying: Part III
Hi again. In this final post, we’re going to review a more advanced use of Active Record — subqueries.
Subqueries
Getting Started
Coming from an analyst background, I’m tempted to just write raw sql when things get complicated. But it’s actually not too hard to write subqueries with active record. You just set your subquery to a variable and filter against it in a new query.
From
Our first example uses a subquery in the from clause. If you only use active record, you may not have seen from. This is actually what your first model reference calls in sql. For instance, Recipe.all is equivalent to Select * from Recipes.
Let’s say I want to identify complaints that need attention. In this case, we’re querying for an average number of complaint occurrences by complaint type and by account.
First, we’ll look for the number of complaints per account and complaint type
a = Account.select(“accounts.id, complaints.id, count(complaints.id) as ct”).joins(:complaints).group("accounts.id,complaints.id")
We set the result of the subquery to a variable and reference it in the appropriate place in your parent query (in this case, from).
In this particular case, I want to know the average of the complaint counts for each account. Here’s how we’d write the query, while referencing the subquery:
Account.select("accounts.name, avg(ct) as complaint_count").from(a)
Here, we’re treating the subquery as a superficial table — as if count was its own column. Since we’re grouping by name, we’re aggregating each complaint type and averaging it.
Where
My where example is a little more complicated — bear with me. I find where nested queries useful for obtaining aggregated information on counts.
Let’s say you wanted to fill your pantry with ingredients used in your favorite recipes. You want to find the recipes first, then aggregate on the amount of each ingredient needed.
We’ll start with the interior query (subquery):
r = Recipe.select("recipes.id, count(meals.id) as ct").joins(:meals).group("recipes.id").having("count(meals.id) > ?", 3)
Here’s how we’d write the main query, while referencing the subquery:
Ingredient.select("ingredients.name, sum(ingredients.amount)").joins(:recipes).where(recipes: { id: r })".group("ingredients.name")
This simply means that the recipe’s id is in the subquery results. If you were referencing explicit results, you’d do something like where(recipes: { id: [2,3,4] }).
Raw SQL
So you really need to use raw SQL? Below are a few(not all) approaches you can use.
Set the sql commands as a string variable and run:
MyModel.find_by_sql(sql)to get model relations.
If you are doing aggregations, you can call this on your query:
result = MyModel.connection.execute(sql)
which will return a database specific result object. From the object, you can simply query result(0,0) to get the value.
Note: be careful where you use these approaches, as they are subject to SQL injection.
Conclusion
Today, we looked at some more advanced uses of active record — with a particular focus on how to write subqueries. In Part I, we explored joins and in Part II, we looked at grouping and filtering results.
There are many other situations we didn’t cover — such as extracting portions of dates and substrings — which you may encounter if you’re querying the database regularly. You can consult your database documentation to learn more about these techniques.
