CODEWITHSUNDEEP
ruby-on-railsrubymodelassociations
Rui
Rui

Reputation: 5920

Ruby on Rails: filter associations on model based on several conditions

In my ROR app I the have the models Category, Item, Property and PropertyValuation. The idea is that a category contains items, and an item has several properties. PropertyValuation purpose is to store the property value for the specific items. The models are defined as above:

class Category < ActiveRecord::Base
  attr_accessible :name, :description, :parent, :children, :items, :parent_id

  has_many :children, :class_name => "Category", :foreign_key => "parent_id", :dependent => :nullify
  belongs_to :parent, :class_name => "Category"

  has_many :categorizations
  has_many :items, :through => :categorizations
end

class Item < ActiveRecord::Base
  attr_accessible :name, :description, :property_valuations, :barcode

  has_many :property_valuations, :dependent => :destroy  
  has_many :properties, :through => :property_valuations 

  has_many :categorizations
  has_many :categories, :through => :categorizations

end

class Property < ActiveRecord::Base
  attr_accessible :name, :description, :value_type, :unit, :unit_id

  has_many :property_valuations, :dependent => :destroy  
  has_many :items, :through => :property_valuations
  has_many :property_ranges, :dependent => :destroy

  belongs_to :unit
end

class PropertyValuation < ActiveRecord::Base     
  attr_accessible :property, :item, :value, :categorization

  belongs_to :property
  belongs_to :item
end

Now my question, I've successfully managed to filter categories items by name by doing this:

@category.items.where("lower(items.name) like ?", "%#{params[:keywords].downcase}%")

But now I also want to filter those items depending on the associated property value. Example: I want the category items whose name contains "foo", and where property "A" has value 1, property B has value 2, and so on. How can I implement such a query?

Upvotes: 2

Views: 11260

Answers (3)

Maxime Lapointe
Maxime Lapointe

Reputation: 93

A gem that exists to do that: activerecord_where_assoc (I'm the author)

With it, you can do what you want this way:
I want the category items whose name contains "foo", and where property "A" has value 1, property B has value 2, and so on.

@category.items.where(name: "foo").where_assoc_exists(:property_valuations) { |pv|
  pv.where(value: 1).where_assoc_exists(:property, name: 'A')
}.where_assoc_exists(:property_valuations) { |pv|
  pv.where(value: 2).where_assoc_exists(:property, name: 'B')
}

This shows the greatest power of the gem, it's just reusable without conflicts. Doing this with a joins would be a pain. However, when the need is complex, the result also is dense... This can be solved easily with scopes.

# In items:
scope :with_property, lambda {|name, value| 
  where_assoc_exists(:property_valuations) { |pv|
    pv.where(value: value).where_assoc_exists(:property, name: name)
}}

@category.items.where(name: "foo").with_property('A', 1).with_property('B', 2)

If you wanted to make a more powerful scope that can receive an operator, you could do that by just changing the where(value: value) part to fit your need.

Here are the introduction and examples. Read more details in the documentation.

Upvotes: 0

Jason Noble
Jason Noble

Reputation: 3766

You should join the tables together, and then limit based on your criteria.

# Category items by name
Category.joins(:items).where(:items => { :name => keywords })

You may find http://guides.rubyonrails.org/active_record_querying.html#joining-tables and calling .to_sql helpful.

Upvotes: 2

Logan Serman
Logan Serman

Reputation: 29880

You can chain ActiveRecord scopes, including where. So you can limit the names first, and then chain an additional where to limit the results. This example below will limit the results where property "A" has value 1, as mentioned in your question:

keywords = params[:keywords].downcase
@category.items.where("lower(items.name) like ?", "%#{keywords}%").where(:A => 1)

You can also store scopes in a variable. For example if you wanted to limit the same dataset by properties A and B separately, you might do something like this:

keywords = params[:keywords].downcase
matched_by_name = @category.items.where("lower(items.name) like ?", "%#{keywords}%")
foo = matches_by_name.where(:A => 1)
bar = matches_by_name.where(:B => 1)

Upvotes: 0

Related Questions