Rails 5 ActiveRecord where, find_by, or, not and Eager Loading

The Where Clause

Find all incomplete tasks with priority 3.

  Task.where(complete: false).where(priority: 3).all
  Task Load (0.3ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."complete" = ? AND "tasks"."priority" = ?  [["complete", false], ["priority", 3]]
 => #<ActiveRecord::Relation []> 

No results. Find all incomplete tasks with priority 9.

  Task.where(complete: false).where(priority: 9)
  Task Load (0.1ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."complete" = ? AND "tasks"."priority" = ?  [["complete", false], ["priority", 9]]
 => #<ActiveRecord::Relation [#<Task id: 3, name: "Buy a puppy", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 2, priority: 9>]> 

This gives us one record. Searching for null values generates SQL with 'IS NULL'.

  Task.where(complete: false).where(priority: nil)
  Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."complete" = ? AND "tasks"."priority" IS NULL  [["complete", false]]
 => #<ActiveRecord::Relation []> 

This syntax is correct. We can search for priority in an array like this:

 Task.where(complete: false).where(priority: [4,9])
  Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."complete" = ? AND "tasks"."priority" IN (4, 9)  [["complete", false]]
 => #<ActiveRecord::Relation [#<Task id: 1, name: "Get rich quick", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 1, priority: 4>, #<Task id: 3, name: "Buy a puppy", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 2, priority: 9>]> 

This returns two records, one with priority 4 and the other with priority 9. This generates SQL with IN(4,9). We can also use a range for the priority like this:

 Task.where(complete: false).where(priority: 4..9)
  Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."complete" = ? AND ("tasks"."priority" BETWEEN ? AND ?)  [["complete", false], ["priority", 4], ["priority", 9]]
 => #<ActiveRecord::Relation [#<Task id: 1, name: "Get rich quick", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 1, priority: 4>, #<Task id: 3, name: "Buy a puppy", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 2, priority: 9>]> 

This returns all the tasks within that range. It uses the SQL BETWEEN clause. Use of the range retrieves all records within that range. Here is an example:

 Task.where(priority: 4..9)
  Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE ("tasks"."priority" BETWEEN ? AND ?)  [["priority", 4], ["priority", 9]]
 => #<ActiveRecord::Relation [#<Task id: 1, name: "Get rich quick", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 1, priority: 4>, #<Task id: 2, name: "Write a book", complete: true, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 1, priority: 5>, #<Task id: 3, name: "Buy a puppy", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 2, priority: 9>, #<Task id: 4, name: "Dance in the rain", complete: true, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 2, priority: 7>]> 

This returns all tasks that has priority within the given range 4 through 9. In this case, tasks with priority 4, 5, 7 and 9. We can find all tasks with id 1 and 4 like this:

 Task.where(id: [1,4])
   Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."id" IN (1, 4)
  => #<ActiveRecord::Relation [#<Task id: 1, name: "Get rich quick", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 1, priority: 4>, #<Task id: 4, name: "Dance in the rain", complete: true, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 2, priority: 7>]> 

This returns two tasks, one with id 1 and the other with id 4.

The find_by

You cannot use find_by_column with a range in Rails 5.

Task.find_by_priority(2..10)
  Task Load (0.1ms)  SELECT  "tasks".* FROM "tasks" WHERE "tasks"."priority" = ? LIMIT ?  [["priority", nil], ["LIMIT", 1]]
 => nil 

This generates wrong SQL query. However, you can call find_by_column with single value for that column like this:

 Task.find_by_priority(9)
  Task Load (0.1ms)  SELECT  "tasks".* FROM "tasks" WHERE "tasks"."priority" = ? LIMIT ?  [["priority", 9], ["LIMIT", 1]]
 => #<Task id: 3, name: "Buy a puppy", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 2, priority: 9>

This works. It returns one record. Note the limit 1 in the generated SQL query. You can also use find_by(column: value) like this:

 Task.find_by(priority: 9)
   Task Load (0.1ms)  SELECT  "tasks".* FROM "tasks" WHERE "tasks"."priority" = ? LIMIT ?  [["priority", 9], ["LIMIT", 1]]
  => #<Task id: 3, name: "Buy a puppy", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 2, priority: 9> 

This also works. It returns one record. Again, note the limit 1 in the generated SQL query.

 Task.find_by(priority: [2..10])
  Task Load (0.2ms)  SELECT  "tasks".* FROM "tasks" WHERE ("tasks"."priority" BETWEEN 2 AND 10) LIMIT ?  [["LIMIT", 1]]
 => #<Task id: 1, name: "Get rich quick", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 1, 
 priority: 4> 

Finds only one record. The generated SQL query has limit 1. You can load all the tasks like this:

Task.all.to_a
  Task Load (0.1ms)  SELECT "tasks".* FROM "tasks"
 => [#<Task id: 1, name: "Get rich quick", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 1, priority: 4>, #<Task id: 2, name: "Write a book", complete: true, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 1, priority: 5>, #<Task id: 3, name: "Buy a puppy", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 2, priority: 9>, #<Task id: 4, name: "Dance in the rain", complete: true, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 2, priority: 7>] 

Eager loading

A project has many tasks. We can load all the tasks that belongs to a project by using includes like this:

 Project.all.includes(:tasks)
  Project Load (2.0ms)  SELECT "projects".* FROM "projects"
  Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."project_id" IN (1, 2)
 => #<ActiveRecord::Relation [#<Project id: 1, name: "Wealth Building", created_at: "2016-03-11 23:40:42", updated_at: "2016-03-11 23:40:42">, #<Project id: 2, name: "Be Happy", created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43">]> 

You see two queries, one for retrieving the projects and another for retrieving the tasks. This avoids the N+1 query problem. You cannot go in the other direction. You will get an error:

 Task.all.includes(:projects)
  Task Load (0.1ms)  SELECT "tasks".* FROM "tasks"
ActiveRecord::AssociationNotFoundError: Association named 'projects' was not found on Task; perhaps you misspelled it?

Using Not Condition

Let's find task with priority NOT in 9 by using not. You can chain not with where clause like this:

Task.where.not(priority: 9)
  Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE ("tasks"."priority" != ?)  [["priority", 9]]
 => #<ActiveRecord::Relation [#<Task id: 1, name: "Get rich quick", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 1, priority: 4>, #<Task id: 2, name: "Write a book", complete: true, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 1, priority: 5>, #<Task id: 4, name: "Dance in the rain", complete: true, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 2, priority: 7>]> 

The generated SQL query has != for the not. This returns tasks with priority 4, 5 and 7. The not can also take an array of values like this:

Task.where.not(priority: [4,9])
  Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE ("tasks"."priority" NOT IN (4, 9))
 => #<ActiveRecord::Relation [#<Task id: 2, name: "Write a book", complete: true, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 1, priority: 5>, #<Task id: 4, name: "Dance in the rain", complete: true, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 2, priority: 7>]> 

The generated SQL query has priority NOT in (4,9). This returns two records with priority 5 and 7.

Using OR Condition

You can chain or condition with where like this:

Task.where(project_id: 1).or(Task.where(priority: 9))
  Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE ("tasks"."project_id" = ? OR "tasks"."priority" = ?)  [["project_id", 1], ["priority", 9]]
 => #<ActiveRecord::Relation [#<Task id: 1, name: "Get rich quick", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 1, priority: 4>, #<Task id: 2, name: "Write a book", complete: true, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 1, priority: 5>, #<Task id: 3, name: "Buy a puppy", complete: false, created_at: "2016-03-11 23:40:43", updated_at: "2016-03-11 23:40:43", project_id: 2, priority: 9>]> 

The generated SQL has OR condition. This returns tasks that belongs to project_id 1 or that has priority 9. The first two tasks belongs to project with id 1 and the third task belongs to project with id 2 but it has priority 9.

Rails 4 and 5

The find_all_by_column(value) is not available in Rails 4.x and 5 anymore.

Task.find_all_by_complete(false)
NoMethodError: undefined method `find_all_by_complete' for #<Class:0x007ff3eafc2b60>
Did you mean?  find_or_create_by

This gives an error.

Summary

In this article, you learned how to use the where clause to chain conditions, or condition, not condition, find_by and eager loading.


Related Articles


Software Compatibility Best Practices

I spoke to some of the most talented and experienced software developers. I have created a guide that is filled with valuable insights and actionable ideas to boost developer productivity.

You will gain a better understanding of what's working well for other developers and how they address the software compatibility problems.

Get the Guide Now