sql - Rails complex joins query -

here basic model heirarchy:

class product   has_many :inventories end class inventory   belongs_to :product   has_many :inventory_events end class inventoryevent   belongs_to :inventory end 

inventoryevent instances store state change + timestamps changes, inventory.inventory_events.last shows current state.

i'm running problems creating query on product model give me inventories current state received.

what have right is:

p = product.first p.inventories.joins(:inventory_events).where(inventory_events: {state: 'received'}).all  => # here inventory ever had state 'received' may  not 'received'. 

my sql knowledge pretty limited, seems kind of limit inventory_events: {} option might work, haven't found way that.

---

edit: here workaround @ moment show end goal. there way model query this.

class inventory   def self.received_items     includes(:inventory_events).select {|i| i.current_state == 'received'}   end   def current_state     inventory_events.last.state   end end  product.first.inventories.received_items => # here correct array of inventories 

you can accomplish through use of scopes , merge method. scopes allow keep conditions modular. merge method allow select inventories have inventoryevent received.

# call inventories product have been recieved product.inventories.received  class inventoryevent   def self.received     where("state = ?", "received")   end    def self.most_recent     order("inventory_events.created_at desc").first   end end  class inventory   def self.received     joins(:inventory_events).     merge(inventoryevent.received).     merge(inventoryevent.most_recent)   end end