rails: how to solve that difficult query within rails

Question

Hi everybody,

I have a query for my Rails environment, which I don't really know how to solve...:

I have users and products where any user owns multiple products:

users m:n products

I solved that with a sales table. for any product a user owns there is a more specific table

sales 1:1 individualspecifications

I need to get all the products from a certain user where the individualspecifications are nil...

Thanks in advance Markus

Answer 1

I think you basically want to do this. In your case, it might be something like:

ids = a_user.products.map(&:id)
Products.find(:all, :conditions => ["id not in (?)", ids])

This assumes a user has many products through sales, which you may or may not have defined.

Answer 2

The question is a little ambiguous. So this solution is based on my interpretation, and feel free to comment if I got it wrong.

The way I understand your database, you have a many to many relationship between users and products through the sales table. And there is a one to one relationship between sales and individual specifications.

In ActiveRecord terms that means your four models are probably set up like this:

class User < ActiveRecord::Base
  has_many :sales
  has_many :products, :through => sales
end

class Sale < ActiveRecord::Base
  belongs_to :user
  belongs_to :product
  belongs_to :individual_specifications
end

class Product < ActiveRecord::Base
  has_many :sales
  has_many :users, :through => sales
end

class IndividualSpecifcation < ActiveRecord::Base
  has_one :sale
end

With this relationship, you could write a simple named scope in Product to get the job done.

class Product < ActiveRecord::Base
  has_many :sales
  has_many :users, :through => sales

  named_scope :missing_specification, :joins => :sales, 
    :conditions => {:sales => {:individual_specification_id => nil}}

end

Now you can just do @user.products.missing_specification to get a list of products that for a certain user that are missing specification.

If I got the has_one/belongs_to relationship between sales and individual specifcation backward then the scope gets a much more complicated:

named_scope :missing_specification, 
  :joins => "JOIN sales ON sales.id = products.id " + 
     "LEFT OUTER JOIN individual_specifications is ON is.sale_id = sales.id",
  :conditions => "is.id is NULL"