Create
> User.create(:name => 'jkai', :movie => 'gone with the wind')
Create Relationships
> jkai = User.first
> jkai.cars
> jkai.cars.create(:year =>2012, :condition => "awesome")
> jkai.cars
> Car.where(:id => 2).first.user
Joins
> User.where(:name => 'jkai').joins(:cars)
=> "SELECT users.*
FROM users
INNER JOIN cars
ON cars.user_id = users.id
WHERE users.name = 'jkai' "
> User.joins(:cars).where(:cars => {:condition => 'awesome'})
=> "SELECT users.*
FROM users
INNER JOIN cars
ON cars.user_id = users.id
WHERE cars.condition = 'awesome' "
> Product.joins(:users).where(:users => {:name => 'richard'})
Pluck
> Person.pluck(:id)
=> SELECT people.id FROM people
> User.select([:email, :username])
=> SELECT email, username from user
Where
> User.where(:id => 1).first
> User.where("id => 1").first
> User.where("id > 1").first
> User.where("id <> 1").first
Include
> products = Product.includes(:user).limit(22)
> products.eaach { |p| p.user }
Find - only finds by PK
> User.find(1)
**Order **
> Product.limit(5).order(:price)
> Product.limit(5).order("price DESC")
> Product.limit(5).order("price DESC").limit(10).each { |x| puts x.price }
> Product.limit(5).order("price ASC").limit(10).each { |x| puts x.price }
> Product.limit(5).order(:price).first.price
> Product.limit(5).order(:price).last.price
Limt
> Product.limit(33)
> Product.first # more efficient
> Product.first(33) # first 33 products
> Product.last
Offset (Paginate)
> Product.order("price DESC").limit(10).offset(0).first
> Product.order("price DESC").limit(10).offset(10).first # Next set of 10
> Product.order("price DESC").limit(10).offset(20).first # Next set of 10
No user associated with a product
> Product.where(:user_id => nil)
Outer Join
> User.joins("LEFT OUTER JOIN products ON users.id = products.user_id").where(:products => {:id => nil})
Where product.id is nil
> User.joins(:products).count
Group
> Product.group(:price).count # Returns Hash
=> SELECT COUNT(*), price FROM products GROUP BY price
> Product.where(:name => "widget").group(:name)
Distinct
> prods = Product.where(:name => "widget")
> prods.count(:name, :distinct => true)
Having
> prods = Product.group(:price).having("count(price) > 5")
> prods.all.count
Mix and Match
> Product.where(:price => 5).
where(:name => "apple").
order(:created_at).
limit(55).
joins(:users).
where(:users => {:name => 'richard'})
ActiveRecord::Relation
> first, all, count, each