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.