In the last lesson, we have learned:
Can you explain to me a little bit about those concepts?
Today we will learn about Active Record Query Interface. This includes a lot of things like:
Active Record Query Interface is a set of methods provided by Active Record to interact with database in object-oriented fashion.
Active Record provides us with a lot of methods to retrieve objects from the database. Here are some of them.
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')
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
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
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.
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.
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.
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')
For negative conditions, we can use "not" like this:
foods = Food.where.not(name: 'Nasi Uduk')
# SELECT * FROM foods WHERE name != 'Nasi Uduk'
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
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
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
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 }
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 means combining two or more tables based on a related column between them. Consider the following tables:
id | name | price | restaurant_id |
---|---|---|---|
1 | Nasi Uduk | 20000 | 1 |
2 | Kerak Telor | 15000 | 1 |
id | name | address |
---|---|---|
1 | Restaurant 1 | Address 1 |
2 | Restaurant 2 | Address 2 |
Now, how to produce a data consisting order id, customer name and order date like this?
foods.id | foods.name | restaurants.name |
---|---|---|
1 | Nasi Uduk | Restaurant 1 |
2 | Kerak Telor | Restaurant 1 |
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
In SQL, there are four different types of joins:
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.
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
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])
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'
}
})
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.
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.
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'
}
})
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
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)
We can use conditional with scope:
class Order < ApplicationRecord
scope :created_before, -> (time) { where("created_at < ?", time) if time.present? }
end
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
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'
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')
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")
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)
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
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?
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)
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:
2. Search form for Order should include these fields:
3. Search form for Restaurant should include these fields:
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:
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.