Skip to content

Instantly share code, notes, and snippets.

@jessieay
Created July 17, 2012 19:55
Show Gist options
  • Save jessieay/3131622 to your computer and use it in GitHub Desktop.
Save jessieay/3131622 to your computer and use it in GitHub Desktop.
Active Record cheat sheet with examples of queries I've needed most so far
ActiveRecord cheat sheet / EXAMPLES
INSTALL
=======
$ gem install activerecord
in GEMFILE: gem ‘activerecord’
REQUIRE
=======
require ‘active_record’
HOW TO USE
ESTABLISH A DATABASE CONNECTION
=======
ActiveRecord::Base.establish_connection( :adapter => 'sqlite3', :database => 'database_path')
CREATE TABLE IN DATABASE
=======
ActiveRecord::Base.connection.execute <<-SQL
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name VARCHAR NOT NULL,
last_name VARCHAR NOT NULL,
);
SQL
RELATIONSHIPS
=======
one-to-one:
class Customer < ActiveRecord::Base
has_one :detail
end
class Detail < ActiveRecord::Base
belongs_to :customer
end
## to include automatic building of detail when instantiate a customer, add this to customer class:
has_one :detail
after_initialize do
self.build_detail
end
## to automatically destroy detail record when destroy customer record:
has_one :detail, :dependent => :destroy
## to hide child object, add the following to parent class:
delegate :birthday, :gender, :city, :to => :detail
OR
delegate *Detail::ATTR_METHODS, :to => :detail
## (must include ATTR_METHODS = [:birthday, :birthday=, :gender, :gender=, :city, :city=] in detail class to use 2nd option]
one-to-many
class Parent < ActiveRecord::Base
has_many :children
end
class Child < ActiveRecord::Base
belongs_to :parent
end
## to destroy chid objects when parent in destroyed, add the following to the parent class:
has_many :children, :dependent => :destroy
many-to-many
class Magazine < ActiveRecord::Base
has_many :subscriptions
has_many :customers, :through => :subscriptions
end
class Customer < ActiveRecord::Base
has_many :subscriptions
has_many :magazines, :through => :subscriptions
end
## this makes it easy to find a customer’s magazines:
@customer = customer.find(17)
@customer.magazines
## it also makes it easy to find a customer’s subscriptions:
@customer.subscriptions
CREATING RECORDS:
=====================
Single/Parent:
myStudent1 = Student.new(:first_name => 'Ann', :last_name => 'Smith', :birthday => '1986-08-11')
myStudent1.save
Child:
(myStudent1.classes.new(:name => ‘Geometry’)).save
##note alternative method for saving and pluralized ‘classes’##
QUERIES
=====================
Find one by id:
Customer.find(17)
## finds customer with an id of 17
Find related records by id:
Customer.find(17).detail.birthday
## finds birthday field for the customer with id = 17. Birthday field is contained in Details table.
Find by label:
Can do:
- Person.where(:user_name => user_name).first
- Person.where(:last_name => last_name).all
- Person.where(:user_name => user_name, :password => password).first
Better:
- Person.find_by_user_name(user_name)
- Person.find_all_by_last_name(last_name)
- Person.find_by_user_name_and_password(user_name, password)
List all:
Contact.all.each do |contact|
puts contact.first_name
end
## puts of first name for each contact in Contacts table
Conditional:
Student.where(:gender => 'male')
## finds students with the value ‘male’ in the gender column
LIKE:
Contact.where("first_name LIKE 'A%'")
OR
Contact.where(‘first_name LIKE ?', “A%")
## both examples above find contacts where first_name column begins with A. Some people prefer second for stylistic reasons
## Using LIKE to find values in the middle of a string:
Student.where("birthday LIKE '%-07-%'").each do |student|
puts student.first_name + " has a birthday in July"
end
## prints the first name of all students with birthdays in July
Count:
Client.count
## prints count of number of records in Client table
Client.where(:first_name => 'Ryan').count
## prints the the count of clients where first_name is Ryan
Unique:
Client.select(:name).uniq
## only grabs a single record per unique value in the :name field
## A more complicated example using .uniq:
contacts = Address.where(:state => 'CA').includes(:contact).collect do |address|
address.contact
end
contacts.uniq.each do |contact|
puts contact
end
## finds each address in CA and then prints out each contact with an address in CA only once
## Another way doing this (via http://whatcodecraves.com/articles/2009/04/08/adventures-with-active-record-find):
Fruit.all(:select => "DISTINCT fruits.*", ...)
@vvj5
Copy link

vvj5 commented May 27, 2015

Thank you for this, it's very helpful! :)

@DidiRaggio
Copy link

Good stuff!

@rhexcasas
Copy link

thanks. very helpful.

@talesmgodois
Copy link

Hi, I am needing a query which accept a date range and also, the regular hash parameters. The range should be date_start <= date, and date_end >= date. And I need to keep querying to the hash values normally.

@Mackenzie-Frey
Copy link

Thanks for this.

@ArthurZheng
Copy link

Thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment