ph-lee

Those coming from PHP background or oher languages and have not had experience with object-relational mapping may at first not seem confident with the concept, still prefering to write raw SQL rather than using the tools available.

One tip I can give is to check the SQL queries that Active Record has generated and submitted to the databse. This can be done by viewing the console where the server was started (ie the command line where ruby script/server was executed). All SQL submitted to the database are displayed here when running in development mode. You can also check the logs in log/development.log, here all the text from the script/server console are stored for debugging purposes.

Hope this helps users become more confident in using Active Record rather than writing the SQL by hand.

This is a back-to-basics article I decided to revisit. Here we’ll demonstrate how to query two related tables using ActiveRecord with joins and conditions rather than having to write the SQL by hand. Instead we let Activerecord do all the hard work. I like to learn by example so let’s set the scene.

We’re an estate agent and have an application which stores client details and property details. There are many clients and many properties, a client can have many properties but a property can only belong to one client. A simple one-many relationship. Here are some quick models.

models/client.rb

1
2
3

class Client < ActiveRecord::Base
   has_many :properties
end

models/property.rb

1
2
3

class Property < ActiveRecord::Base
   belongs_to :client
end

Heres some information the above two tables may contain for the purpose of this example…

Client table structure

• id (primary key)
• first_name
• last_name

Property table structure

• id (primary key)
• client_id (foreign key)
• house_number
• address_first_line

#Note that client_id is needed to keep a relationship between the two tables. This foreign key is handled by rails ActiveRecord. Since we specified the relationship in our models it will use client_id to look up primary keys in the client table (ie. client.id).

controllers/home.rb

1
2
3
4
5

class HomeController < ApplicationController
   def my_very_specific_query
      my_client = Client.find(:first, :joins => [:properties], :conditions => {:first_name => "Fred", :properties => {:house_number => 3}})
   end
end

The above is very specific query which suits the purpose of this example but rather useless in the real world. It finds the first client in the database (sort by id in ascending order by default) where their first name is “Fred” AND owns a property with the house number three.

:joins performs a INNER JOIN, (however if you need you can overide this by writing the exact join by specifying the SQL). This will let you specify conditions for your query which regard related tables.

:conditions lets you specifying the query conditions that will return when true. We have used a hash in this case specifying conditions from the model/table we wish to return (in this case Client) and condition in a related table (Property). Of course extra joins and conditions can be added to suit the need of your query. For further info please see the API on ActiveRecord

You should now be able to use ActiveRecord to query multiple tables using :joins and :conditions

Hope you found this useful.