Rails JOIN TABLES

Question

Hi, I have a following SQL QUERY:

SELECT articles.name, articles.price, users.zipcode FROM articles INNER JOIN users ON users.id = articles.user_id WHERE vectors @@ to_tsquery('crime') ORDER BY articles.price ASC

And I Would like to write it inside of a find method from an ActiveRecord Class named Articles (Articles belongs_to user). Basically i wanna search for Articles and access the zipcode propertie from the User (user has_many Articles)

I wrote the following version, but i'm not sure that its working because in the response i dont receive any information about the user zipcode.

a = Article.find(:all,:conditions=>"vectors @@ to_tsquery('crime')",:joins= >:user,:order=>:price,:include=>:user)

But i have no idea how to access the zipcode information. how can I access this information ? this is the right approach ?

Regards,

Victor

Answer 1

If you've coupled Articles and Users like you say above, this should be pretty easy:

@articles = Article.find(:all, :conditions => "…", :include => :user)

Then, in your view, you can do:

<ul>
<% for each article in @articles do %>
  <li><%= article.user.zipcode %></li>
</ul>
<% end %>

This works because Rails creates a property for the parent object (User) in the model (Article) - you can read more about that in the API docs. This even works without the "include" key above, but leaving it out would mean a database query in every step of the loop.