What We Learned So Far

In the last lesson, we have learned:

1. Active Record Validations
2. Active Record Callbacks
3. Active Record Associations

Can you explain to me a little bit about those concepts?

What We Will Learn Today

Today we will learn about Active Record Query Interface. This includes a lot of things like:

1. Retrieving objects from database
2. Conditions
3. Order, grouping, limit, and offset
4. Join tables
5. Scope
6. Miscellaneous things you can do with Active Record

What is Active Record Query Interface?

Active Record Query Interface is a set of methods provided by Active Record to interact with database in object-oriented fashion.

Retrieving Objects

Active Record provides us with a lot of methods to retrieve objects from the database. Here are some of them.

Retrieving a Single Object (1)

This is how to retrieve a single object from the database.

# To retrieve an object by its id
Food.find(1)
# SELECT * FROM foods WHERE (foods.id = 1) LIMIT 1

# However, passing more than one parameter to find
# will return a collection
Food.find(1, 3)
# SELECT * FROM foods WHERE foods.id IN (1, 3)

# To retrieve an object by its attributes
Food.find_by(name: 'Nasi Uduk', price: 10000)
# SELECT * FROM foods WHERE (foods.name = 'Nasi Uduk' AND foods.price = 10000) LIMIT 1

# There is a big difference between find and find_by, though
# Try both of these statements in your rails console

# Pass Food's id that is not already in your database
Food.find(100)

# Pass Food's name that is not already in your database
Food.find_by(name: 'Nasi Lemak') 

Retrieving a Single Object (2)

This is how to retrieve a single object from the database.

# To retrieve an object with no specific order
Food.take
# SELECT * FROM foods LIMIT 1

# To retrieve the first object ordered by primary key
Food.first
# SELECT * FROM foods ORDER BY foods.id ASC LIMIT 1

# However, passing a parameter to first, will retrieve a collection
Food.first(3)
# SELECT * FROM foods ORDER BY foods.id ASC LIMIT 3

# To retrieve the last object ordered by primary key
Food.last
# SELECT * FROM foods ORDER BY foods.id DESC LIMIT 1

# Likewise, passing a parameter to last, will retrieve a collection
Food.last(3)
# SELECT * FROM foods ORDER BY foods.id DESC LIMIT 3

Retrieving Multiple Objects in Batches

The find_each method retrieves records in batches and then yields each one to the block.

Order.find_each do |order|
  order.total_price
end

Conditions

The where method allows you to specify conditions to limit the records returned, representing the WHERE-part of the SQL statement. Conditions can either be specified as a string, array, or hash.

Pure String Conditions

Food.where("name = 'Nasi Uduk' AND price = 10000")
# SELECT * FROM foods WHERE name = 'Nasi Uduk' AND price = 10000

Pure string conditions is simply putting any conditions that we used to write after the "WHERE" part in SQL query.

You may be tempted to put your conditions in pure string and use string interpolation for dynamic variable.

# DON'T EVER DO THIS!
name = 'Nasi Uduk'
price = 10000
Food.where("name = '#{name}' AND price = #{price}")

Using pure string conditions with string interpolation makes your app vulnerable to SQL injection attack.

Array Conditions

So, how to safely pass dynamic variables to where condition? We use array conditions like this:

name = 'Nasi Uduk'
price = 10000
Food.where("name = ? AND price = ?", name, price)

# or we can use
Food.where("name = :name AND price = :price", { name: 'Nasi Uduk', price: 10000 })

To learn more about SQL injection attack, take a look at this page.

Hash Conditions

We can also use hash conditions like this:

foods = Food.where(name: 'Nasi Uduk', price: 10000)

# We can also use hash conditions for associated models
foods = Food.joins(:restaurant).where(restaurant: { name: 'Warung Bang Rojali' })

# We can also use range for hash_conditions
foods = Food.where(price: 10000..20000)
# SELECT * FROM foods WHERE price BETWEEN 10000 AND 20000

foods = Food.where(name: ['Nasi Uduk', 'Kerak Telor'])
# SELECT * FROM foods WHERE name IN ('Nasi Uduk', 'Kerak Telor')

Not Conditions

For negative conditions, we can use "not" like this:

foods = Food.where.not(name: 'Nasi Uduk')
# SELECT * FROM foods WHERE name != 'Nasi Uduk'

Ordering

To sort the results of a query, we can use:

foods = Food.order(:name) # by default in ascending order
foods = Food.order(name: :desc) # change it to descending order

foods = Food.order(:name, :price)
# ordering by name ascending, then by price ascending

foods = Food.order(:name, :price :desc)
# ordering by name ascending, then by price descending

Selecting Specific Fields

By default, Active Record's find method will select all fields from the result set. To select specific fields only, we can use:

foods = Food.select(:name, :price)
# or
foods = Food.select("name, price")

# Be careful though, 
# calling field that is not selected in your collection
# will raise an error
foods.first.image_url

Limit and Offset

This is pretty straight forward:

foods = Food.where(price: 15000).limit(5).offset(10)
# Get 5 rows of foods with price 15000 starting from 11st row

Group

To do SQL's GROUP BY operation, we can use:

food1 = Food.create(name: "Food 1", price: 20000, description: "Food 1", image_url: "Food 1.jpg")
food2 = Food.create(name: "Food 2", price: 30000, description: "Food 2", image_url: "Food 2.jpg")
food3 = Food.create(name: "Food 3", price: 20000, description: "Food 3", image_url: "Food 3.jpg")

Food.all.group(:price)
# will return food3 and food2

Food.all.select(:price).group(:price)
# will return food3 and food2 with only price attribute

Food.all.group(:price).count
# will return a hash of price and its count
# { 20000 => 2, 30000 => 1 }

Having

We can specify grouping with "having" clauses.

food1 = Food.create(name: "Food 1", price: 20000, description: "Food 1", image_url: "Food 1.jpg")
food2 = Food.create(name: "Food 2", price: 30000, description: "Food 2", image_url: "Food 2.jpg")
food3 = Food.create(name: "Food 3", price: 20000, description: "Food 3", image_url: "Food 3.jpg")

Food.all.group(:price).having("price > 25000")
# will return food2 only

Food.all.group(:price).having("price > 25000").count
# { 30000 => 1 }

Joining Tables (1)

Joining tables means combining two or more tables based on a related column between them. Consider the following tables:

Joining Tables (2)

Now, how to produce a data consisting order id, customer name and order date like this?

Using SQL, we are going to write a query like this:

SELECT foods.id, foods.name, restaurants.name
FROM foods
INNER JOIN restaurants ON foods.restaurant_id = restaurants.id

Different Types of Joins

In SQL, there are four different types of joins:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

Joining Tables in Rails

In Rails, we can do this:

foods = Food.joins(:restaurant)
# or
foods = Food.joins("restaurants")

However, using joins will only return attributes of the model with which we invoke "joins" method.

Joining Multiple Associations

Given our current app so far, we can do the following:

Food.joins(:restaurant, :category)

# It will produce this query:
# SELECT foods.* FROM foods
#  INNER JOIN restaurants ON foods.restaurant_id = restaurants.id
#  INNER JOIN categories ON foods.category_id = categories.id

Joining Nested Associations

Given our current app so far, we can do the following:

# Single level
LineItem.joins(foods: :restaurant)

# It will produce this query:
# SELECT line_items.* FROM line_items
#  INNER JOIN foods ON line_items.food_id = foods.id
#  INNER JOIN restaurants ON foods.restaurant_id = restaurants.id

LineItem.joins(:order, foods: :restaurant)
# It will produce this query:
# SELECT line_items.* FROM line_items
#  INNER JOIN orders ON line_items.order_id = order.id
#  INNER JOIN foods ON line_items.food_id = foods.id
#  INNER JOIN restaurants ON foods.restaurant_id = restaurants.id

# Imagine that we have a model called Owner that has many Restaurant
LineItem.joins(food: [{ restaurants: :owner }, :category])

Joining Tables with Conditions

We can also add conditions to join tables

LineItem.joins(food: :restaurant).where(quantity: 2)
LineItem.joins(food: :restaurant).where(foods: {name: 'Nasi Uduk'})
LineItem.joins(food: :restaurant).where(
  foods: {
    restaurants: {
      name: 'Restaurant 1'
    }
  })

Eager Loading Associations (1)

Consider this scenario:

foods = Food.all
foods.each do |food|
  puts food.restaurant.name
end

If you execute the code above in your Rails console, you will see that the query to load Restaurant's name is executed as many times as the number of Food. In programming, this is called N+1 queries problem.

Eager Loading Associations (2)

To avoid N+1 queries problem, in Rails you can use eager loading associations.

foods = Food.includes(:restaurant).all
foods.each do |food|
  puts food.restaurant.name
end

If you execute the code above in your Rails console, you will see that now it takes only two queries to display all the Restaurant's name.

Eager Loading Associations (3)

Just as joining tables, you can use multiple associations and conditions on eager loading associations.

LineItem.includes(foods: :restaurant).where(quantity: 2)
LineItem.includes(foods: :restaurant).where(foods: {name: 'Nasi Uduk'})
LineItem.includes(foods: :restaurant).where(
  foods: {
    restaurants: {
      name: 'Restaurant 1'
    }
  })

Scope

Let's say that we add a new attribute called "state" to our Order. There are four possible states for an Order: draft, submitted, paid, and delivered. In our model, we can do scoping like this:

class Order < ApplicationRecord
  scope :paid_order, -> { where(state: 'paid') }
end

The code above is the equivalent of defining a class method "self.paid_order" for your model. To invoke it, we can use:

Order.paid_order
# or
orders = Order.all
orders.paid_order

Chaining and Passing Argument

We can chain scopes:

class Order < ApplicationRecord
  scope :paid_order, -> { where(state: 'paid') }
  scope :paid_order_in_jakarta, -> { paid_order.where("address like '%Jakarta%'") }
end

We can pass an argument to a scope:

class Order < ApplicationRecord
  scope :created_before, -> (time) { where("created_at < ?", time) }
end

# We can invoke it with parameter
Order.created_before(Time.zone.now)

Scope with Conditional

We can use conditional with scope:

class Order < ApplicationRecord
  scope :created_before, -> (time) { where("created_at < ?", time) if time.present? }
end

Default Scope

If we wish for a scope to be applied across all queries to the model, we can use default_scope method within the model.

class Order < ApplicationRecord
  # This is just an example if you have an attribute "deleted_at" in your Order
  default_scope { where(deleted_at: nil) }
end

Merging Scope

You can merge scope when invoking your model.

class Order < ApplicationRecord
  scope :paid_order, -> { where(state: 'paid') }
  scope :created_before, -> (time) { where("created_at < ?", time) }
end

Order.paid_order.created_before(Time.zone.now)
# SELECT * FROM orders WHERE state = 'paid' AND created_at < '2017-11-01 07:00:00'

Find or Build New Object

It's common that you need to find a record and create it if it does not exist yet. For this, you can use:

Restaurant.find_or_create_by(name: 'RM Sederhana')

# or

Restaurant.find_or_create_by!(name: 'RM Sederhana')
# With find_or_create_by! however,
# Rails will raise error if the new record is invalid

At times, you may just want to initialize the record if it is not found, instead of directly create it.

Restaurant.find_or_initialize_by(name: 'RM Sederhana')

Finding by SQL

If somehow you need to manually create the raw SQL, you can always resort to this method:

Food.find_by_sql("SELECT * FROM foods WHERE foods.price < 100000 AND foods.price > 10000")
Food.connection.select_all("SELECT * FROM foods WHERE foods.price < 100000 AND foods.price > 10000")

Pluck

Consider this code:

food_names = Food.select(:name).map { |f| f.name }
# or
food_names = Food.select(:name).map(&:name)

With pluck, we can simplify it to:

food_names = Food.pluck(:name)

# pluck also works for multiple attributes

food_attributes = Food.pluck(:name, :price)

# of course it also works with other form of collections

expensive_foods = Food.where("price > 50000").pluck(:name)

Ids

For ids of objects, you can use special method called "ids" to pluck them all.

food_ids = Food.ids
expensive_food_ids = Food.where("price > 50000").ids

Existence of Objects

To check for the existence of objects, we can use:

Food.exists?(1)
Food.exists?(id: [1, 2, 3])
Food.exists?(name: ['Nasi Uduk', 'Kerak Telor'])
Food.where(name: 'Nasi Uduk').exists?

# or

Food.any?
Food.where(name: 'Nasi Uduk').any?
Food.where("price > 50000").many?

Calculations

Some basic calculations available for Active Record:

Food.count
Food.where("price > 50000").count
Food.all.average(:price)
Food.all.minimum(:price)
Food.all.maximum(:price)
Food.all.sum(:price)

Exercise (1)

Without using additional gems, build a search form in admin index page for Food, Order, and Restaurant with specs.

1. Search form for Food should include these fields:

• name, searching for "ayam" will return all Foods with name containing the word "ayam" in any part of its name. These name should show up in your search result: "Ayam Rica Rica", "Nasi Goreng Ayam", "Steak Ayam BBQ".
• description, the rule is just the same as name field.
• minimum price, will return all Foods with price greater than or equal to the minimum price.
• maximum price, will return all Foods with price less than or equal to the minimum price.

Exercise (2)

2. Search form for Order should include these fields:

• name, the rule is just the same as Food's name field.
• address, the rule is just the same as name field.
• email, the rule is just the same as name field.
• payment type, drop down of payment type, returns Order with the exact payment type as selected.
• minimum total price, will return all Orders with total price greater than or equal to the minimum total price field.
• maximum total price, will return all Orders with total price less than or equal to the maximum total price field.

Exercise (3)

3. Search form for Restaurant should include these fields:

• name, the rule is just the same as Food's name field.
• address, the rule is just the same as name field.
• minimum food count, will return all Restaurants with number of foods greater than or equal to the minimum total food count field.
• maximum food count, will return all Restaurants with number of foods less than or equal to the maximum total food count field.

Exercise (4)

4. Explore the Chartkick gem (site, github). Create a new controller called Dashboard with just one method called index. In your Dashboard index page, show six different charts displaying:

• The number of order every date
• The total price of order every date
• The number of order for each food
• The total price of order for each food
• The number of order from each restaurant
• The total price of order from each restaurant

For this exercise, you need to create a seed of at least 20 orders dated from November 1st to November 8th 2017, ordering foods from at least 5 restaurants.