Shineyrock

like 1 03
Queries in Rails, Part 2
1. June 2016
2. Daily
3. read 841
4. response 3
In this second article, we’ll dive a little deeper into Active Record queries in Rails. In case you are still new to SQL, I’ll add examples that are simple enough that you can tag along and pick up the syntax a bit as we go.
That being said, it would definitely help if you run through a quick SQL tutorial before you come back to continue to read. Otherwise, take your time to understand the SQL queries we used, and I hope that by the end of this series it won’t feel intimidating anymore.
Most of it is really straightforward, but the syntax is a bit weird if you just started out with coding—especially in Ruby. Hang in there, it’s no rocket science!
Topics
- Includes & Eager Loading
- Joining Tables
- Eager Loading
- Scopes
- Aggregations
- Dynamic Finders
- Specific Fields
- Custom SQL
Includes & Eager Loading

These queries include more than one database table to work with and might be the most important to take away from this article. It boils down to this: instead of doing multiple queries for information that is spread over multiple tables, includes tries to keep these to a minimum. The key concept behind this is called “eager loading” and means that we are loading associated objects when we do a find.
If we did that by iterating over a collection of objects and then trying to access its associated records from another table, we would run into an issue that is called the “N + 1 query problem”. For example, for each agent.handler in a collection of agents, we would fire separate queries for both agents and their handlers. That is what we need to avoid since this does not scale at all. Instead, we do the following:
Rails
```
agents = Agent.includes(:handlers)
```
If we now iterate over such a collection of agents—discounting that we haven't limited the number of records returned for now—we'll end up with two queries instead of possibly a gazillion.
SQL
```
SELECT "agents".* FROM "agents"
SELECT "handlers".* FROM "handlers" WHERE "handlers"."id" IN (1, 2)
```
This one agent in the list has two handlers, and when we now ask the agent object for its handlers, no additional database queries need to be fired. We can take this a step further, of course, and eager load multiple associated table records. If we needed to load not only handlers but also the agent’s associated missions for whatever reason, we could use includes like this.
Rails
```
agents = Agent.includes(:handlers, :mission)
```
Simple! Just be careful about using singular and plural versions for the includes. They depend on your model associations. A has_many association uses plural, while a belongs_to or a has_one needs the singular version, of course. If you need, you can also tuck on a where clause for specifying additional conditions, but the preferred way of specifying conditions for associated tables that are eager loaded is by using joins instead.
One thing to keep in mind about eager loading is that the data that will be added on will be sent back in full to Active Record—which in turn builds Ruby objects including these attributes. This is in contrast to “simply” joining the data, where you will get a virtual result that you can use for calculations, for example, and will be less memory draining than includes.
Joining Tables
Joining tables is another tool that lets you avoid sending too many unnecessary queries down the pipeline. A common scenario is joining two tables with a single query that returns some sort of combined record. joins is just another finder method of Active Record that lets you—in SQL terms—JOIN tables. These queries can return records combined from multiple tables, and you get a virtual table that combines records from these tables. This is pretty rad when you compare that to firing all kinds of queries for each table instead. There are a few different kinds of data overlap you can get with this approach.
The inner join is the default modus operandi for joins. This matches all the results that match a certain id and its representation as a foreign key from another object or table. In the example below, put simply: give me all missions where the mission’s id shows up as mission_id in an agent’s table. "agents"."mission_id" = "missions"."id". Inner joins exclude relationships that don’t exist.
Rails
```
Mission.joins(:agents)
```
SQL
```
SELECT "missions".* FROM "missions" INNER JOIN "agents" ON "agents"."mission_id" = "mission"."id"
```
So we are matching missions and their accompanying agents—in a single query! Sure, we could get the missions first, iterate over them one by one, and ask for their agents. But then we would go back to our dreadful “N + 1 query problem”. No, thank you!
What’s also nice about this approach is that we won’t get any nil cases with inner joins; we only get records returned that match their ids to foreign keys in associated tables. If we need to find missions, for example, that lack any agents, we would need an outer join instead. Since this currently involves writing your own OUTER JOIN SQL, we will look into this in the last article. Back to standard joins, you can also join multiple associated tables, of course.
Rails
```
Mission.joins(:agents, :expenses, :handlers)
```
And you can add onto these some where clauses to specify your finders even more. Below, we are looking only for missions that are executed by James Bond and only the agents that belong to the mission 'Moonraker' in the second example.
```
Mission.joins(:agents).where( agents: { name: 'James Bond' })
```
SQL
```
SELECT "missions".* FROM "missions" INNER JOIN "agents" ON "agents"."mission_id" = "missions"."id" WHERE "agents"."name" = ?  [["name", "James Bond"]]
```
Rails
```
Agent.joins(:mission).where( missions: { mission_name: 'Moonraker' })
```
SQL
```
SELECT "agents".* FROM "agents" INNER JOIN "missions" ON "missions"."id" = "agents"."mission_id" WHERE "missions"."mission_name" = ?  [["mission_name", "Moonraker"]]
```
With joins, you also have to pay attention to singular and plural use of your model associations. Because my Mission class has_many :agents, we can use the plural. On the other hand, for the Agent class belongs_to :mission, only the singular version works without blowing up. Important little detail: the where part is simpler. Since you are scanning for multiple rows in the table that fulfill a certain condition, the plural form always makes sense.
Scopes
Scopes are a handy way to extract common query needs into well-named methods of your own. That way they are a bit easier to pass around and also possibly easier to understand if others have to work with your code or if you need to revisit certain queries in the future. You can define them for single models but use them for their associations as well.
The sky is the limit really—joins, includes, and where are all fair game! Since scopes also return ActiveRecord::Relations objects, you can chain them and call other scopes on top of them without hesitation. Extracting scopes like that and chaining them for more complex queries is very handy and makes longer ones all the more readable. Scopes are defined via the “stabby lambda” syntax:
Rails
```
class Mission < ActiveRecord::Base
  has_many: agents

  scope :successful, -> { where(mission_complete: true) }
end

Mission.successful
```
```
class Agent < ActiveRecord::Base

  belongs_to :mission

  scope :licenced_to_kill, -> { where(licence_to_kill: true) }
  scope :womanizer,        -> { where(womanizer: true) }
  scope :gambler,          -> { where(gambler: true) } 
end

# Agent.gambler
# Agent.womanizer
# Agent.licenced_to_kill
# Agent.womanizer.gambler

Agent.licenced_to_kill.womanizer.gambler
```
SQL
```
SELECT "agents".* FROM "agents" WHERE "agents"."licence_to_kill" = ? AND "agents"."womanizer" = ? AND "agents"."gambler" = ?  [["licence_to_kill", "t"], ["womanizer", "t"], ["gambler", "t"]]
```
As you can see from the example above, finding James Bond is much nicer when you can just chain scopes together. That way you can mix and match various queries and stay DRY at the same time. If you need scopes via associations, they are at your disposal as well:
```
Mission.last.agents.licenced_to_kill.womanizer.gambler
```
```
SELECT  "missions".* FROM "missions"  ORDER BY "missions"."id" DESC LIMIT 1
SELECT "agents".* FROM "agents" WHERE "agents"."mission_id" = ? AND "agents"."licence_to_kill" = ? AND "agents"."womanizer" = ? AND "agents"."gambler" = ?  [["mission_id", 33], ["licence_to_kill", "t"], ["womanizer", "t"], ["gambler", "t"]]
```
You can also redefine the default_scope for when you are looking at something like Mission.all.
```
class Mission < ActiveRecord::Base
  default_scope { where status: "In progress" }
end

Mission.all
```
SQL
```
 SELECT "missions".* FROM "missions" WHERE "missions"."status" = ?  [["status", "In progress"]]
```
Aggregations

This section is not so much advanced in terms of the understanding involved, but you will need them more often than not in scenarios that can be considered a bit more advanced than your average finder—like .all, .first, .find_by_id or whatever. Filtering based on basic calculations, for example, is most likely something that newbies don’t get in touch with right away. What are we looking at exactly here?
- sum
- count
- minimum
- maximum
- average
Easy peasy, right? The cool thing is that instead of looping through a returned collection of objects to do these calculations, we can let Active Record do all this work for us and return these results with the queries—in one query preferably. Nice, huh?
- count
Rails
```
Mission.count

# => 24
```
SQL
```
SELECT COUNT(*) FROM "missions"
```
- average
Rails
```
Agent.average(:number_of_gadgets).to_f

# => 3.5
```
SQL
```
SELECT AVG("agents"."number_of_gadgets") FROM "agents"
```
Since we now know how we can make use of joins, we can take this one step further and only ask for the average of gadgets the agents have on a particular mission, for example.
Rails
```
Agent.joins(:mission).where(missions: {name: 'Moonraker'}).average(:number_of_gadgets).to_f

# => 3.4
```
SQL
```
SELECT AVG("agents"."number_of_gadgets") FROM "agents" INNER JOIN "missions" ON "missions"."id" = "agents"."mission_id" WHERE "missions"."name" = ?  [["name", "Moonraker"]]
```
Grouping these average number of gadgets by missions' names becomes trivial at that point. See more about grouping below:
Rails
```
Agent.joins(:mission).group('missions.name').average(:number_of_gadgets)
```
SQL
```
SELECT AVG("agents"."number_of_gadgets") AS average_number_of_gadgets, missions.name AS missions_name FROM "agents" INNER JOIN "missions" ON "missions"."id" = "agents"."mission_id" GROUP BY missions.name
```
- sum
Rails
```
Agent.sum(:number_of_gadgets) 

Agent.where(licence_to_kill: true).sum(:number_of_gadgets) 

Agent.where.not(licence_to_kill: true).sum(:number_of_gadgets)
```
SQL
```
SELECT SUM("agents"."number_of_gadgets") FROM "agents"

SELECT SUM("agents"."number_of_gadgets") FROM "agents" WHERE "agents"."licence_to_kill" = ?  [["licence_to_kill", "t"]]

SELECT SUM("agents"."number_of_gadgets") FROM "agents" WHERE ("agents"."licence_to_kill" != ?)  [["licence_to_kill", "t"]]
```
- maximum
Rails
```
Agent.maximum(:number_of_gadgets)

Agent.where(licence_to_kill: true).maximum(:number_of_gadgets) 
```
SQL
```
SELECT MAX("agents"."number_of_gadgets") FROM "agents"

SELECT MAX("agents"."number_of_gadgets") FROM "agents" WHERE "agents"."licence_to_kill" = ?  [["licence_to_kill", "t"]]
```
- minimum
Rails
```
Agent.minimum(:iq)

Agent.where(licence_to_kill: true).minimum(:iq) 
```
SQL
```
SELECT MIN("agents"."iq") FROM "agents"

SELECT MIN("agents"."iq") FROM "agents" WHERE "agents"."licence_to_kill" = ?  [["licence_to_kill", "t"]]
```
Attention!

All of these aggregation methods are not letting you chain on other stuff—they are terminal. The order is important to do calculations. We don’t get an ActiveRecord::Relation object back from these operations, which makes the music stop at that point—we get a hash or numbers instead. The examples below won’t work:
Rails
```
Agent.maximum(:number_of_gadgets).where(licence_to_kill: true)

Agent.sum(:number_of_gadgets).where.not(licence_to_kill: true)

Agent.joins(:mission).average(:number_of_gadgets).group('missions.name')
```
Grouped

If you want the calculations broken down and sorted into logical groups, you should make use of a GROUP clause and not do this in Ruby. What I mean by that is you should avoid iterating over a group which produces potentially tons of queries.
Rails
```
Agent.joins(:mission).group('missions.name').average(:number_of_gadgets)

# => { "Moonraker"=> 4.4, "Octopussy"=> 4.9 }
```
SQL
```
SELECT AVG("agents"."number_of_gadgets") AS average_number_of_gadgets, missions.name AS missions_name FROM "agents" INNER JOIN "missions" ON "missions"."id" = "agents"."mission_id" GROUP BY missions.name
```
This example finds all the agents that are grouped to a particular mission and returns a hash with the calculated average number of gadgets as its values—in a single query! Yup! The same goes for the other calculations as well, of course. In this case, it really makes more sense to let SQL do the work. The number of queries we fire for these aggregations is just too important.
Dynamic Finders
For every attribute on your models, say name, email_address, favorite_gadget and so on, Active Record lets you use very readable finder methods that are dynamically created for you. Sounds cryptic, I know, but it doesn’t mean anything other than find_by_id or find_by_favorite_gadget. The find_by part is standard, and Active Record just tucks on the name of the attribute for you. You can even get to add an ! if you want that finder to raise an error if nothing can be found. The sick part is, you can even chain these dynamic finder methods together. Just like this:
Rails
```
Agent.find_by_name('James Bond')

Agent.find_by_name_and_licence_to_kill('James Bond', true)
```
SQL
```
SELECT  "agents".* FROM "agents" WHERE "agents"."name" = ? LIMIT 1  [["name", "James Bond"]]

SELECT  "agents".* FROM "agents" WHERE "agents"."name" = ? AND "agents"."licence_to_kill" = ? LIMIT 1  [["name", "James Bond"], ["licence_to_kill", "t"]]
```
Of course you can go nuts with this, but I think it loses its charm and usefulness if you go beyond two attributes:
Rails
```
Agent.find_by_name_and_licence_to_kill_and_womanizer_and_gambler_and_number_of_gadgets('James Bond', true, true, true, 3)
```
SQL
```
SELECT  "agents".* FROM "agents" WHERE "agents"."name" = ? AND "agents"."licence_to_kill" = ? AND "agents"."womanizer" = ? AND "agents"."gambler" = ? AND "agents"."number_of_gadgets" = ? LIMIT 1  [["name", "James Bond"], ["licence_to_kill", "t"], ["womanizer", "t"], ["gambler", "t"], ["number_of_gadgets", 3]]
```
In this example, it is nevertheless nice to see how it works under the hood. Every new _and_ adds an SQL AND operator to logically tie the attributes together. Overall, the main benefit of dynamic finders is readability—tucking on too many dynamic attributes loses that advantage quickly, though. I rarely use this, maybe mostly when I play around in the console, but it’s definitely good to know that Rails offers this neat little trickery.
Specific Fields

Active Record gives you the option to return objects that are a bit more focused about the attributes they carry. Usually, if not specified otherwise, the query will ask for all the fields in a row via * (SELECT "agents".*), and then Active Record builds Ruby objects with the complete set of attributes. However, you can select only specific fields that should be returned by the query and limit the number of attributes your Ruby objects need to “carry around”.
Rails
```
Agent.select("name") 

=> #<ActiveRecord::Relation [#<Agent 7: nil, name: "James Bond">, #<Agent id: 8, name: "Q">, ...]>
```
SQL
```
SELECT "agents"."name" FROM "agents"
```
Rails
```
Agent.select("number, favorite_gadget") 

=> #<ActiveRecord::Relation [#<Agent id: 7, number: '007', favorite_gadget: 'Walther PPK'>, #<Agent id: 8, name: "Q", favorite_gadget: 'Broom Radio'>, ... ]>
```
SQL
```
SELECT "agents"."number", "agents"."favorite_gadget" FROM "agents"
```
As you can see, the objects returned will just have the selected attributes, plus their ids of course—that is a given with any object. It makes no difference if you use strings, as above, or symbols—the query will be the same.
Rails
```
Agent.select(:number_of_kills)

Agent.select(:name, :licence_to_kill)
```
A word of caution: If you try to access attributes on the object that you haven’t selected in your queries, you will receive a MissingAttributeError. Since the id will be automatically provided for you anyway, you can ask for the id without selecting it, though.
Custom SQL

Last but not least, you can write your own custom SQL via find_by_sql. If you are confident enough in your own SQL-Fu and need some custom calls to the database, this method might come in very handy at times. But this is another story. Just don’t forget to check for Active Record wrapper methods first and avoid reinventing the wheel where Rails tries to meet you more than halfway.
Rails
```
Agent.find_by_sql("SELECT * FROM agents")

Agent.find_by_sql("SELECT name, licence_to_kill FROM agents") 
```
Unsurprisingly, this results in:
SQL
```
SELECT * FROM agents

SELECT name, licence_to_kill FROM agents
```
Since scopes and your own class methods can be used interchangeably for your custom finder needs, we can take this one step further for more complex SQL queries.
Rails
```
class Agent < ActiveRecord::Base

  ...

  def self.find_agent_names
    query = <<-SQL
      SELECT name
      FROM agents
    SQL
    self.find_by_sql(query)
  end
end
```
We can write class methods that encapsulate the SQL inside a Here document. This lets us write multi-line strings in a very readable fashion and then store that SQL string inside a variable which we can reuse and pass into find_by_sql. That way we don’t plaster tons of query code inside the method call. If you have more than one place to use this query, it’s DRY as well.
Since this is supposed to be newbie-friendly and not an SQL tutorial per se, I kept the example very minimalistic for a reason. The technique for way more complex queries is quite the same, though. It’s easy to imagine having a custom SQL query in there that stretches beyond ten lines of code.
Go as nuts as you need to—reasonably! It can be a life saver. A word about the syntax here. The SQL part is just an identifier here to mark the beginning and end of the string. I bet you won’t need this method all that much—let’s hope! It definitely has its place, and Rails land wouldn’t be the same without it—in the rare cases that you will absolutely want to fine-tune your own SQL with it.
Final Thoughts

I hope you got a bit more comfortable writing queries and reading the dreaded ol’ raw SQL. Most of the topics we covered in this article are essential for writing queries that deal with more complex business logic. Take your time to understand these and play around a bit with queries in the console.
I’m pretty sure that when you leave tutorial land behind, sooner or later your Rails cred will rise significantly if you work on your first real-life projects and need to craft your own custom queries. If you are still a bit shy of the topic, I’d say simply have fun with it—it really is no rocket science!
martijn broeders

founder/ strategic creative at shineyrock web design & consultancy
e-mail: .(JavaScript must be enabled to view this email address)
phone: 434 210 0245

Like

disLike

1

Add a comment...

Comments

Name*

Email*

location

URL

Comment {comment}

Remember my personal information Notify me of follow-up comments?

1

nadeem ogen

November 23, 2018 at 01:00 AM
nice topic which you have choose. second is, the information which you have provided is better then other blog. so nice work keep it up. And thanks for sharing.
- Like
- disLike
- 3
Comments

Name*

Email*

location

URL

Comment
2

Gopi

November 02, 2016 at 07:27 AM
Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
- Like
- disLike
- 19
Comments

Name*

Email*

location

URL

Comment
3

shalini

June 07, 2016 at 02:13 AM
this blog is very informative and it is very exclusive it is really nice and thanks for sharing those information
- Like
- disLike
- 5
Comments

Name*

Email*

location

URL

Comment

9 years ago Cathleen Wonderful, what a website it is! This…

9 years ago Sharyn Ѕimply a smiling ѵisitant heгe to…

9 years ago shelby What's up it's me, I am also visiting…

9 years ago Lottie I read this paragraph completely…

8 years ago earlene This text is invaluable. How can I find…

8 years ago amelie I needed to thank you for this great…

8 years ago noe vance Hi, for all time i used to check…

8 years ago brayden Pretty! This was an extremely wonderful…

8 years ago Peden Hello! I'm at work browsing your blog…

8 years ago cameron Very great post. I just stumbled upon…

8 years ago juliane mckibben WOW just what I was looking for. Came…

8 years ago carissa That is a good tip especially to those…

8 years ago carris This piece of writing presents clear…

8 years ago marissa Great write-up, I am normal visitor of…

8 years ago ena As a Newbie, I am always exploring…

8 years ago beth Thank you for another fantastic post.…

blog

Queries in Rails, Part 2

Topics

Includes & Eager Loading

Rails

SQL

Rails

Joining Tables

Rails

SQL

Rails

SQL

Rails

SQL

Scopes

Rails

SQL

SQL

Aggregations

Rails

SQL

Rails

SQL

Rails

SQL

Rails

SQL

Rails

SQL

Rails

SQL

Rails

SQL

Attention!

Rails

Grouped

Rails

SQL

Dynamic Finders

Rails

SQL

Rails

SQL

Specific Fields

Rails

SQL

Rails

SQL

Rails

Custom SQL

Rails

SQL

Rails

Final Thoughts

martijn broeders

nadeem ogen

Gopi

shalini

By - category

RSS

By - date

December, 2023

October, 2022

September, 2022

August, 2022

April, 2022

January, 2022

December, 2021

November, 2021

July, 2021

June, 2021

May, 2021

April, 2021

March, 2021

February, 2021

May, 2020

April, 2020

March, 2020

February, 2020