I'm working with some data that works like this:
There are many stores, and many products.
Products come in many categories.
Each store carries different products, through a join table called
"inventory".
I'm surprised by the results I get trying to do some queries for a store's
inventory by product category. Here is the sample console output:
In my test setup I have two toys:
pry(main)> Product.where(category: "Toys").all
=> [ #<Product @values={:id=>1, :name=>"Buzz Lightyear!",
:category=>"Toys", :description=>"..."}>,
#<Product @values={:id=>2, :name=>"Hot Wheels", :category=>"Toys",
:description=>"..."}>
]
I have two stores, and have assigned toy 1 (Buzz Lightyear!) to each store,
which you can see reflected in the inventory table:
pry(main)> Inventory.all
=> [ #<Inventory @values={:id=>3, :product_id=>1, :store_id=>58}>,
#<Inventory @values={:id=>4, :product_id=>1, :store_id=>272}>
]
But when I try to query for a store inventory and limit the query to a
specific category, I get unexpected results:
pry(main)> Inventory.where(store_id: 58).join(:products, category: "Toys").
all
=> [ #<Inventory @values={:id=>1, :product_id=>1, :store_id=>58,
:name=>"Buzz Lightyear!", :category=>"Toys", :description=>"..."}>,
#<Inventory @values={:id=>2, :product_id=>1, :store_id=>58,
:name=>"Hot Wheels", :category=>"Toys", :description=>"..."}>]
In this case, you can see the second result is odd. The result has a
product_id of 1, which is the product "Buzz Lightyear", but it shows the
name "Hot Wheels" which is the name of the other toy in the db. That toy is
not in the inventory for store 58 though.
I imagine there's something about SQL join that I'm failing to understand,
but this result really surprises me. Specifically if you interact with
these results in ruby, like this:
inventory = Inventory.where(store_id: 58).join(:products, category: "Toys").
all
inventory.each{|i| puts i.product }
i.product will be the same record for each result of the query.
What's going on here, and how should I perform the query I'm attempting to
(all inventory for store X with product.category Y)?
Thanks!
Andrew
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
