Rails and sql: rewriting find_by_sql

Question

I have a Product and Creator table, I also have a table called CreatorProduct which joins the creator with the product. A product can have many creators and a creator can have many products. What I want to do is find the products that have creators, some products might not have creators.

I have written the code below but how do I write it in a more rails friendly way? This works for my in rails console but when I put it in my code I get undefined method `includes' for #

Product.find_by_sql("select * from Products where id in (select product_id from Creator_Products intersect select id from Products)")

Thanks!

Answer 1

I would probably just use the find function and use a similar where to what you have now:

Product.find(:all, :conditions => ["exists (select * from Creators where product_id = Products.Id)"])

Otherwise I guess with the way active record joins the data you could probably get the same by including the creator information (assuming you have the has_many set up) and then make sure that the creator information exists...

Product.find(params[:id],:include => [:Creator],:conditions => ["Creator.product_id!=null"])

If you don't have the relationships set up already you need to define then in your models:

class Product< ActiveRecord::Base
has_many :CreatorProduct
has_many :Creators, :through => :CreatorProduct