CODEWITHSUNDEEP
ruby-on-railsactiverecord
Mark Tabler
Mark Tabler

Reputation: 1429

How do I select only the associated objects in a Rails "where" query?

I have a model Category, which has_many Products, and a Product in turn has_many Categories. When a user searches for a Category, I'd like to return the products of the matching Categories without losing my Arel object. Here's what I have so far:

Category.where("upper(title) like ?", search_term.upcase).map {|category| category.products}.flatten

This does the trick of returning the products, but of course what I have is an array and not Arel. I can get as far as adding an :includes(:products) clause, so I do indeed get the products back but I still have them attached to their categories. How do I adjust my query so that all I get back is an Arel that only addresses products?

Upvotes: 0

Views: 1934

Answers (1)

DanneManne
DanneManne

Reputation: 21180

If it is products that you want then you should probably start with the Product object when you are searching. For example ,you could do it like this:

Product.joins(:categories).where("upper(categories.title) like ?", search_term.upcase)

The reason I use joins instead of includes is that joins will perform an INNER JOIN instead of LEFT OUTER JOIN which is what you need to only return the products that are actually associated with the found categories.

To make it a little more elegant you could wrap it all up in a scope in your Product model like this:

# In Product.rb
scope :in_categories_like, Proc.new{ |search_term|
  joins(:categories).where("upper(categories.title) like ?", search_term.upcase) 
}

# In use
@products = Product.in_categories_like(params[:search_term])

Upvotes: 4

Related Questions