Getting Started with Active Record Querying: Part III

Lindsey Maddox
Aug 23, 2017 · 3 min read

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.

)
Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade