CODEWITHSUNDEEP
mysqlruby-on-railsactiverecordassociationshas-many
Alex
Alex

Reputation: 63

Rails - query a has_many association

I'm having trouble querying a has_many association. The context is stores.

class Store < ActiveRecord::Base
  has_many :items
end

class Item < ActiveRecord::Base
  belongs_to: store
end

Stores table:

id  name
1   Macys
2   Target
3   Dillards

Items table:

id  store_id    name
1   1           pants
2   1           shirt
3   2           pants
4   2           shirt
5   3           shirt

I'm trying to query for stores that only sell shirts. So I need a query that returns the store record with id of 3.

When I tried to do

Store.includes(:items).where(
  items: { name: %w(shirts)} ).references(:items)

it returns store_ids 1, 2, and 3 (all stores) because they all have shirts.

Upvotes: 4

Views: 9496

Answers (4)

Alex
Alex

Reputation: 63

I ended up using:

Store.joins(:items).group('items.store_id').having("max(items.name) = 
  min(items.name) and min(items.name) = 'shirt'")

Upvotes: 2

Rohan
Rohan

Reputation: 2727

One way of doing this as mentioned in the post that stores only having items as shirts would be: 

Store.joins(:item).where("items.name = ?", 'shirt').where.not(item_name: Item.where("items.name != ?", "shirt"))

Hope it helps!!

Upvotes: 0

Moamen Naanou
Moamen Naanou

Reputation: 1713

In your Item model, you need to set the counter_cache:

belongs_to :store, counter_cache: true

then your query will be:

Store.joins(:items).where('items_count = ? AND items.name = ?', 1, 'shirt')

Upvotes: 1

Jay-Ar Polidario
Jay-Ar Polidario

Reputation: 6603

Store.includes(:items)
  .where(items: { name: 'shirt' })
  .where.not(id:
    Item.where.not(name: 'shirt').select(:store_id)
  )

Hopefully there's a better way... (if anyone)

Upvotes: 1

Related Questions