jkaihsu
5/6/2013 - 5:17 AM

ActiveRecord_Query_notes.md

ActiveRecord Notes


Queries

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