mysql - generating a row depending on 5 distinct tables rails 3.0.1 ruby 1.8.7 -

i have generate report needs compare data 5 tables (user,order,product,rating,orderproduct) this:

user has many orders (user_id in order) product has many children of through field father_id
user , products has many ratings (user_id , product_id in rating)
order , product has many of each other through orderproducts(order_id , product_id in orderproduct)

i need bring registers in products, per user products , user not in rating

in other words: bring users buyed , not rated product , products buyer didn't rate

on top of that, need bring father of products when father_id not null.

edit1: so, edited models' names, , managed think little clearer: need bring products not rated in order , show buyer(user related order in question)

i can't think clearer right now, sry it.

edit2: https://gist.github.com/rmatuoka/c73e41a2aa475d3b5a29

if downvote, @ least tell me why :)

thanks attention.

this should pretty close... please check table , field names needed guess @ of them (for instance, i'm assuming products table has attribute parent_id links next product level). run @ scale, need have right indexes in place , may need tune design bit.

@users_not_rated = user.find_by_sql("   select pp.product_id father_id, p.product_id, u.user_id products p   inner join products pp on p.parent_id = pp.product_id   inner join order_products op on p.product_id = op.product_id   inner join orders o on op.order_id = o.order_id   inner join users u on o.user_id = u.user_id   not exists (     select r.user_id, r.product_id ratings r     r.user_id = u.user_id     , r.product_id = p.product_id   ) ") 

basically, join , return rows without rating user , product.