parm530
9/12/2017 - 2:44 PM

ActiveRecord Querying

Beginner's guide to ActiveRecord Querying

ACTIVERECORD = M in MVC (controlling models and their interaction and persistance in the database)

  • It is an ORM (Object Relational Mapping System)
  • ALL ABOUT FOLLOWING CONVENTIONS
  • Database table: Plural with underscore in name: book_clubs
  • Model class: Singular with camelCase in name: BookClub

All models inherit from ApplicationRecord (which inherits from ActiveRecord::Base)

class Products < ApplicationRecord
  
end

CRUD

CREATE

user = User.create(name: "", occupation: "")
user.name 
user.occupation

Can also pass create a block:

user = User.new do |u|
  u.name = ''
  u.occupation = ''
end
  • Calling user.save will commit the record to the database!

READ

users = User.all                        # returns all users
user = User.first                       # returns the first user
parm = User.find_by(name: "Parm")       # returns a specified user

UPDATE

user = User.find_by(name: "Parm")     # find the user
user.name = "Kishan"                  # update the name field
user.save                             # save the new changes to the database

  • Shorthand
user = User.find_by(name: "Parm")
user.update(name: "Kishan")
  • If you need to update fields for ALL records:
User.update_all "occupation = students, field2 = blah2"

DELETE

user = User.find_by(name: "Parm")   # find the user
user.destroy                        # destroy the record

QUERYING

Finder methods:

  • find: argument is the id of the record you wish to return Client.find(10) # is eqivalent to the sql query: SELECT * FROM clients WHERE (clients.id = 10) LIMIT 1

  • You can find more than one clients by passing an array of id's to be searched for: Client.find([1,10,9,4]) # = SELECT * FROM clients WHERE (clients.id IN (1,10))

  • find_by: argument is the column name of the record you wish to return

Client.find_by(name: "lil") # is equivalent to:
Client.where(name: "lil").take
  • which runs the SQL: SELECT * FROM clients WHERE (clients.name = 'lil') LIMIT 1
  • WHERE: allows you to use specify conditions to narrow down your search! Client.where("field1 = ?", params[:field1])
  • The ? will be replaced with the value of the argument
  • Always use the above format to prevent SQL injections
  • You can add replace the ? using key/value notation:
Client.where("created_at >= :start_date AND created_at <= :end_date", {start_date: params[:start_date]
, end_date: params[:end_date]})

Hash Conditions

Client.where(locked: true) # SELECT * FROM clients WHERE clients.locked = true

  • In the case of a belongs_to relationship, an association key can be used to specify the model if an Active Record
  • object is used as the value:
Article.where(author: author) # author is the AR object, will return all articles that belong to this author
Author.joins(:articles).where(articles: {author: author})

Finding records using the IN expression

Client.where(id: [1,2,3]) = 
SELECT * FROM clients WHERE clients.id IN (1,2,3)

NOT conditions

  • Are built using where.not Client.where.not(locked: true)

ORDERING

Client.order(:field)

  • you can specify in ascending or decending order!
Client.order(created_at: :asc)
Client.order(created_at: :desc)
# you can chain order after a subseqent order
  • NOTICE that all the above finders will return all the columns from the record
  • What if you only wanted to return a few fields? Client.select("column1", "column2", ...) # SELECT column1, column2 FROM clients
  • To apply a GROUP BY clause to the sql fired by a finder method: Order.select("column1", "column2").group("column1")
  • Having: need to be chanined on to the GROUP BY field: Order.select().group().having("condition")

Joining tables

  • AR provides 2 methods for specifying JOIN clauses: joins and left_outer_joins
  • joins method:
  • Supplying the raw sql: Author.joins("INNER JOIN posts ON posts.author_id = authors.id AND posts.published = 't'")
  • Joining a single association:
  • Category has many articles and an article belongs to a category
Category.joins(:articles)
SELECT categories.* FROM categories INNER JOIN articles ON articles.category_id = categories.id

Specifying conditions on the joined table

Client.joins(:orders).where("orders.created_at" => ...)

Left outer joins: used to select a set of records whether or not they have associated records:

Author.left_outer_joins(:posts).select("")...

Eager loading:

  • mechanism for loading the associated records of the objects returned by Model.find using few queries as possible
  • consider this code:
clients = Client.limit(10)        # 1 SQL Call

clients.each do |client|
  puts client.address.postcode    # 10 SQL calls
end                               # total of 11 sql calls
  • Using includes tells AR to load all the associations in advance!
clients = Client.includes(:address).limit(10)     # 1 SQL call 
clients.each do |client|
  puts client.address.postcode                    # 1 SQL call
end                                               # total of 2 calls
  • You can specify multiple associations with a single model:
Article.includes(:category, :comments)
  • Loads all the articles and the associated category and comments for each article.

SCOPES

  • defined as a class method:
scope :name, -> {query}
scope :published, -> { where(published: true)}
  • is equivalent to
def self.published
  where(published: true)
end
  • Call the method as is it were a class method: Article.published
  • Passing in args: scope :name, ->(arg) {}

Other AR methods: minimum, maximum, sum, average, count


Using select

Client.select(:col1, :col2, etc...)

# which runs: SELECT col1, col2 FROM clients

Method Chaining

Person
.select()
.join()
.where()

is equivalent to:

SELECT ...
FROM people
JOIN table name ON table name.person_id = people.id
WHERE ...

Using RAW SQL

Client.find_by_sql("SELECT ... FROM ... WHERE ... ORDER BY...")