How to execute complex Arel query in Rails 3 from Model

Question

Hi there,

I'm on Rails 3,and I have a SQL query composed of a few joins that I've built up in Arel. I want to run this query from a method in one of my models, but I'm not sure of how to do this. The arel object turns out to be of type Arel::InnerJoin, and I want to retrieve an array of all objects returned from that query. Do I run ModelName.find_by_sql(my_arel_query_object) in order to do that? Or do I run my_arel_query_object.each {...} and iterate over each tuple in order to pop them into an array manually?

I hope I'm making myself clear. Any insight would be greatly appreciated. Thanks.

Updated: Here is the code I use within my user model:

def get_all_ingredients
    restaurants = Table(:restaurants)
    meals = Table(:meals)
    ingredients = Table(:ingredients)

    restaurants_for_user = restaurants.where(restaurants[:user_id].eq(self.id))
    meals_for_user = restaurants_for_user.join(meals).on(restaurants[:id].eq(meals[:restaurant_id]))  
    ingredients_for_user = meals_for_user.join(ingredients).on(meals[:id].eq(ingredients[:meal_id])) 

    return Ingredient.find_by_sql(ingredients_for_user.to_sql)
end

What I'm trying to do here is get all ingredients used in all the meals offered for each restaurant the user owns. The ingredients_for_user variable represents the Arel query that I wish to run. I'm just not sure how to run & return all the ingredients, and the Ingredient.find_by_sql... just doesn't seem right.

end

Answer 1

Arel objects (or, more specifically, Arel::Relation objects) represent a query in relational algebra. The query is executed the first time you try to access its elements, and it acts as a result set as before.

For example, if you have a query like

users.join(:photos).on(users[:id].eq(photos[:user_id]))

you can, iterate over the photos in a view:

<% users.each do |user| %>
  <% users.photos.each do |photo| %>
    <%= photo.name %>
  <% end %>
<% end %>

The example was taken from the Arel's README, which may help you understand better.