ph-lee

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.

Whilst deploying a VirtualBox of Ubuntu to act as a Ruby on Rails development server I hit several issues. One of which was installing the mysql gem with the command

sudo gem install mysql

Giving me the error of

With some googling I found the solution at mentalized.net needing to install ruby1.8-dev as it was missing in my case. To solve enter the following command…

sudo apt-get install ruby1.8-dev

That solved one problem but I hit another trying to install the mysql gem.

This one took more googling but eventually found codeweblog giving a solution. Again I was missing further dependent libraries in this case libmysqlclient15-dev. To install that enter the command…

sudo apt-get install libmysqlclient15-dev

Installing the mysql gem should now work.

Hope this helps.

Normally one would deploy their rails application in the root directory (top-level) ie where the domain is, for example…

www.your-domain.com

However, you may want to deploy your app in a subdirectory such as…

www.your-domain.com/your_app

You may want to do this if you want to deploy several applications on the same domain or temporarily for testing purposes. So heres what to do. Naviagate to the environment.rb file found here…

root | configuration | environment.rb

Open the file in your favourite text editor. Add the relative url configuration to your config as shown below. I added it to the end of the do statement.

Rails::Initializer.run do |config|
config.action_controller.relative_url_root = "/your_app"
end

This setups the environment for rails to now run with url root of your_app.

Happy deploying!

Moving my project from my laptop to my desktop I needed to setup my ruby on rails environment. One of the pugins I used and needed was hpricot_scrub. A great addition for parsing webpages for the project I’m doing. Anyway upon the command “gem install hpricot_scrub” I came across this error.

ERROR:  While executing gem … (Zlib::BufError)

With a little bit of googling I found this page.

Looks like gems needed updating with the command gem update --system