#activerelation

Active Record vs ActiveRecord vs ActiveRelation

Active Record

a design pattern for relational databases

is cross plattform (implemented in different language)

let's you work with data as objects, not just static rows

simplifies database operations, including select, insert, updates, delete

ActiveRecord

a Ruby library that let's implements principles of Active Record as pattern.

ActiveRelation (Arel)

New in Rails 3

Simplifies the generation of complex SQL queries

used by ActiveRecord for queries and management of relationship

E.g.

ActiveRecord

user = User.new user.first_name = "Mark" user.save # SQL INSERT user.last_name = "Twain" user.save # SQL UPDATE user.delete #SQL DELETE

ActiveReleation

users = User.where(:first_name => "Mark") users = users.order("last_name ASC").limit(3) users = users.include(:books_written)

Rails Console

allows you to interact with your model the way irb let's you interact with Ruby interpreter. $rails console || rails c

loads rails environment into irb

E.g.: from rails console: Subject.new will create a subject object with attributes mimicking table fields.

CREATE / UPDATE / DELETE / FIND Records

Creating Records

Two ways to create new records.

new / save

Instantiate an object

Set values

Save

create

all three steps as a single command

E.g.

# Number 1 (new / save) subject = Subject.new #creates a new record object (not yet committed to the database) subject.name = "Something"

Alternative way to write it is: subject = Subject.new(:name => "First Subject", :position => 1, :visible => true) subject.save #saves it to the database subject.new_record? #check if the record is still not committed to the database

# Number 2 (create) subject = Subject.create(:name => "Second Subject", :position => 2) # this will commit a record to db and create an object that reflects the newly created record

The first method is used more frequently, since it gives us the flexibility to change an object before committing it to the db.

Update Records

Two ways to do so.

save

find record

set values

save

update_attributes

find record

set values

save

E.g.

# Number 1 (find / update / save) subject = Subject.find(1) subject.name = "Updated Name" #note that name is not updated in the db until `subject.save` is executed subject.save # Number 2 (find / update_attr) subject = Subject.find(2) subject.update_attributes(:name => "Revised Subject", :visible => true) #returns true/false based on if the update succeeded or not

Delete Records

Two ways:

find / destroy

find: subject = Subject.find(2) #by ID in the subjects table

destroy: subject.destroy

The object is accessible after the destroy operation, so the info in it can be used for different purposes (e.g. reporting)

find / delete

bypasses some built-in mechanisms

faster, but doesn't leave a copy to work with

FIND Records

By primary key

User.find(3)

Returns an object or an error

Dynamic finders

find_by_[database field]: User.find_by_first_name("Tyson")

Returns an object or a nil

Find all method

User.all

Returns an array of objects

First/Last method

User.first or User.last

Returns object or nil

Note: all the methods above make a call to the db Immediately

ActiveRelation (Query Methods)

Note: you can use .to_sql method on the AcriveRelation object to output the SQL that was generated.

Note: the where method returns an array, empty array, if no records were matched.

Note: .scoped method is used to instantiate an empty ActiveRelation object and with no additional constrains will match all records in the database. E.g.: User.scoped

Conditions (what conditions data has to meet in order to satisfy the request)

Old Query Methods (depricated in Rails 3.1 and removed in Rails 3.2)

User.find(:first_name, :conditions => ["first_name = ? AND email = '[email protected]'", "Ali"]

User.find(:all, :conditions => {:first_name => 'Bob'})

New (ActiveRelation) Query Methods

where(conditions)

Returns an ActiveRelation object, which can be chained

Name.where(:first_name => 'Ali').order("email ASC")

does not execute a database call immediately

condition can be of three different types:

String "first_name = 'Ali' AND last_name = 'Muhammad'

Flexible, raw SQL

susceptible to SQL injections

Array ["first_name = ? AND last_name = 'Muhammad']

Flexible, escaped SQL

Safe from SQL injections

Hash {:first_name => "Ali", :last_name => "Muhammad"}

Simple, escaped SQL

Safe from SQL injections

Only supports equality, range and subject checking

Order, Limit, Offset

Old Query Methods:

User.find(:all, :order => "email ASC", :limit => 20, :offset => 40)

New (ActiveRelation)

order(sql_fragment)

limit(integer)

offset(integer) #skip over a certain number of records

E.g.: 1) order: user.order("[user.]email ASC, [user.]last_name DESC") 2) limit: user.limit(1) 3) offset: user.offset(1).limit(1)

Named Scopes

Let's you store queries in the model.

You call them same as regular ActiveRecord methods

Can accept parameters

Note: prior Rails 3.0 the method was called named_scope, now it is simply called scope.

E.g.:

in a model class do the following:

scope :klichko where({:first_name => 'Klichko'}) #where ":klichko" is a named scope name

with the parameter:

This post was written in response to http://modernsage.wordpress.com/2011/02/05/rails-3-0-arel-acrobatics/.  The original post has code examples that are clearly for illustrative purposes and not from the codebase (code where AR models are not descending from AR::Base, for example), so it's possible that some important details have been left out.  However, for the sake of conversation (and because sometimes it's just fun to write a nerdy blog post), I wanted to respond.

In that blog post, my buddy Chris is doing some stuff with Arel/AR that, in my opinion, is backwards. It's causing some confusion that led him to extend ActiveRecord::Base in a way that I feel is unnecessary, and potentially dangerous down the road.

Chris wants to do stuff like:

  Forum.mine.posts.comments

The intent is that he wants to access all the comments owned by a post owned by a forum owned by a particular user.  Working off the Forum model, he wants to scope things through the 'mine' scope, which scopes the Forums to an authenticated user.

Two problems

First, Forum.mine is a backwards way of accessing records belonging to an authenticated user. Normally, a Rails app would authenticate a user and provide helpers at the view and controller level to access that user.  You'll see this in a lot of apps as the method 'current_user'.

The problem with Forums.mine is that in order for it to work, there has to be code inside the model that goes back out to the authentication system.  Authentication is the controller's domain.  It's an awkward coupling of concerns that causes your code to get all tangled up.

A much more straightforward way to retrieve objects is to have the user be at the root when going left to right.  The common way to get Forums owned by a person at the controller level would be:

  current_user.forums

The second problem is that:

  Forum.mine.posts.comments

just doesn't make sense.

Posts, as a collection, don't have comments.  A single Post has comments.  The concept of Comments that belong to many Posts doesn't exist here.  It's a confusion I've seen in new and experienced ActiveRecord developers alike.

This is much more straightforward:

  current_user.forum_comments

In a github repo, I've put an example of how I would write this.  User#forum_comments, in this case, uses a has_many :through relationship to get at the posts, and then does a map and a second query to get the comments.

  class User < ActiveRecord::Base     has_many :forums     has_many :posts, :through => :forums     def forum_comments       Comment.where(:post_id => posts.map(&:id))     end   end

Because Chris didn't want to do this, he implemented an extension of ActiveRecord::Base that allows it to work:

  Forum.mine.posts.comments

It goes through and creates all the necessary scoping to allow that query to return comments.  Even though I don't have any examples, I'm pretty this will break down as more complicated relationships are introduced.

Stinky Magic

The last paragraph of Chris' post says:

You can use it with a single association or with a monster hierarchy of the form A -> B -> C -> D -> E -> F ->DRY, recursive, scalable. Now that’s what I call magic.

In my opinion, this is horribly dangerous.  If you start doing this stuff, and A, B, C, D, and E grow to be a couple million rows (which is not very uncommon at all nowadays) you're gonna have a very non-scalable join to deal with.

I think a developer should err on the side of verbosity and explicitness when defining relationships.  If you really need to do A -> B -> C -> D -> E -> F a lot, that's a sign that some de-normalization may be justified.  Assuming that -> denotes has_many, F would have an e_id on it.  In this case, adding an a_id would allow you to go A -> F directly and much faster.

A little bit of CS (read: Kool-Aid)