0

I have a Box, which has many Compartments which contain many Items which belong to a Category. Items are polymorphic (:itemable) as they can belong to Boxes without Compartments - this might be my difficulty.

I can call all Items back for all Boxes in the DB, in the Box controller with:

@category_items = Item.includes(:category).group("categories.name").sum("quantity * value")

but how can I filter these down to those that belong only to the current Box id?

ritchielee
  • 268
  • 1
  • 9

2 Answers2

0

Try 

compartment_ids = %(SELECT id FROM compartments WHERE box_id = :box_id)

@category_items = Item.includes(:category).where("(itemable_type = 'box' AND itemable_id = :box_id) OR (itemable_type = 'compartment' AND itemable_id IN (#{compartment_ids}))", :box_id => box_id).group("categories.name").sum("quantity * value")
cdesrosiers
  • 8,862
  • 2
  • 28
  • 33
  • Thank you, a step nearer. I got a syntax error with itemable_id IN ?, so switched to itemable_id = ?, this works okay until I have more than one Compartment - then I get an invalid statement i.e. itemable_type = 'Compartment' AND itemable_id = [19,20,21], any ideas on how to deal with that set of IDs? – ritchielee Sep 20 '12 at 18:15
  • Sorry, I botched the format of the IN clause. It should be of the form itemable IN (item1, item2, ...). See if this works. – cdesrosiers Sep 20 '12 at 19:29
  • Note that this involves an embedded 'SELECT' query within the main SQL query. – cdesrosiers Sep 20 '12 at 19:42
  • Ah, - I'd not seen your code edit. This now works a treat - Thank You! – ritchielee Sep 21 '12 at 13:52
0

Try something along these lines:

@category_items= Item.includes(:category).filter(params[:box], [:box_id,:compartment_id, "categories.name"])
Deej
  • 5,334
  • 12
  • 44
  • 68