RoR: model on basis of a view on a LEFT JOIN?

[harm.hoekstra]

Hi,

I want to make a RoR webapp and on one screen the user should see
products and his/her own rating:

PRODUCT YOUR RATING
product100 <no rating yet, add rating widget>
product101 ****
product102 **
product103 <no rating yet, add rating widget>
...

So I have a listing over products, but I need to join in the ratings of
the current user (not of other users).

I think there are 2 approaches:

A. Add a getter method to the model to get the rating (filtered on
current user)
B. Make a view from SELECT * from products LEFT JOIN ratings ON
products.id = ratings.product_id AND
ratings.user_id = {user.id}

I guess method A will result in 20 queries being fired when a list of
20 are shown. Not that the webapp will have heavy traffic, but its a
more clean approach if the database does the heavy lifting in one
swoop, I think.

So I incline to B, but are there any pitalls when making a activerecord
model on basis of a VIEW ?

Or perhaps even better solutions are already thought out in Rails.

Thanks in advance,

Harm

 

[Christopher Schneider]

Hi,

I want to make a RoR webapp and on one screen the user should see
products and his/her own rating:

PRODUCT YOUR RATING
product100 <no rating yet, add rating widget>
product101 ****
product102 **
product103 <no rating yet, add rating widget>
...

So I have a listing over products, but I need to join in the
ratings of
the current user (not of other users).

I think there are 2 approaches:

A. Add a getter method to the model to get the rating (filtered on
current user)
B. Make a view from SELECT * from products LEFT JOIN ratings ON
products.id = ratings.product_id AND
ratings.user_id = {user.id}

I guess method A will result in 20 queries being fired when a list of
20 are shown. Not that the webapp will have heavy traffic, but its a
more clean approach if the database does the heavy lifting in one
swoop, I think.

So I incline to B, but are there any pitalls when making a
activerecord
model on basis of a VIEW ?

Or perhaps even better solutions are already thought out in Rails.

Thanks in advance,

Harm

Assuming you have a model called Product, and a model called Rating
that belongs_to Product (and Product has_many Ratings).

When you lookup the products with Product.find(id) or whatever, just
use the :include => :Rating to make rails do the join. You can then
in the conditions use the tables you are joining on and it won't blow
up. So you should be able to end up with something like:

Product.findall, :conditions => ["Ratings.user_id = ?",
user.id] , :include => :Rating)

I'd have to make sure that worked, but I'm relatively sure that would
work, although I seem to remember about running into caching issues
with it. Do a forced reload if you have missing data. To do that,
you have to do: a_product.ratings(1) the 1 passed in forces it to
rehit the database.