RoR ActiveRecord Query producing unexpected result

Question

I have a test app with models as follows

ActiveRecord::Schema.define(version: 20140102023300) do

  create_table "customers", force: true do |t|
    t.string   "name"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "order_products", force: true do |t|
    t.integer  "customer_id"
    t.integer  "order_id"
    t.integer  "product_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "orders", force: true do |t|
    t.text     "description"
    t.integer  "customer_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "products", force: true do |t|
    t.string   "description"
    t.float    "item_price"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

end

The associations are as follows

class Customer < ActiveRecord::Base
  has_many :orders
end

class Order < ActiveRecord::Base
  belongs_to :customer
  has_many :order_products
end

class OrderProduct < ActiveRecord::Base
  belongs_to :order
end

Then I have seeded the database with seeds.rb as follows:

customers = Customer.create([{:name => 'Alex'}, {:name => 'Clare'}, {:name => 'Bob'}, {:name => 'Ian'}])

orders = Order.create([  
  {:description => 'description for order 1',:customer_id => 1},
  {:description => 'description for order 2',:customer_id => 2},
  {:description => 'description for order 3',:customer_id => 2},
  {:description => 'description for order 4',:customer_id => 1},
  {:description => 'description for order 5',:customer_id => 4}
  ])

orderproducts = OrderProduct.create([
  {:customer_id => 1, :order_id => 1, :product_id => 1},
  {:customer_id => 1, :order_id => 1, :product_id => 2},
  {:customer_id => 1, :order_id => 2, :product_id => 4},
  {:customer_id => 1, :order_id => 2, :product_id => 7},
  {:customer_id => 1, :order_id => 2, :product_id => 3},
  {:customer_id => 2, :order_id => 1, :product_id => 5},
  {:customer_id => 2, :order_id => 1, :product_id => 6},
  {:customer_id => 2, :order_id => 2, :product_id => 3},
  {:customer_id => 2, :order_id => 2, :product_id => 4},
  ])

products = Product.create([
  {:description => 'teddy bear', :item_price => 19.95},
  {:description => 'doll', :item_price => 24.00},
  {:description => 'lego', :item_price => 16.00},
  {:description => 'toy truck', :item_price => 29.95},
  {:description => 'crayons pk10', :item_price => 17.90},
  {:description => 'sketch pad', :item_price => 21.50},
  {:description => 'football', :item_price => 10.55},
  ])

Then in Rails Console I am loading up a variable with the list of customers:

@customers = Customer.all

When I run this command: @customers.find(1).orders I correctly get

Order id: 1, description: "description for order 1", customer_id: 1, created_at: "2014-01-02 02:51:36", updated_at: "2014-01-02 02:51:36">, 

Order id: 4, description: "description for order 4", customer_id: 1, created_at: "2014-01-02 02:51:36", updated_at: "2014-01-02 02:51:36">]

I then want to query the items within each order. For simplicity lets say I want to find the products within order 1, for Customer 1.

I tried this query: @customers.find(1).orders.find(1).order_products but I get this result:

OrderProduct id: 1, customer_id: 1, order_id: 1, product_id: 1, created_at: "2014-01-02 02:51:36", updated_at: "2014-01-02 02:51:36">,

OrderProduct id: 2, customer_id: 1, order_id: 1, product_id: 2, created_at: "2014-01-02 02:51:36", updated_at: "2014-01-02 02:51:36">, 

OrderProduct id: 6, customer_id: 2, order_id: 1, product_id: 5, created_at: "2014-01-02 02:51:36", updated_at: "2014-01-02 02:51:36">, 

OrderProduct id: 7, customer_id: 2, order_id: 1, product_id: 6, created_at: "2014-01-02 02:51:36", updated_at: "2014-01-02 02:51:36">

It looks like it is returning the results for the orders.find(1).order_products part of the query because all the results are order_id: 1. The problem is that this result set includes results for customer_id: 1 and 2.

What I actually want to see is the products within order 1, for customer 1. Can you help me understand the correct query that I need please?

Looks like you have some issues with your seed data. I've posted a solution that suggests 1) a fix as-is, and 2) a way to rectify the seed data so that your original query works. — zeantsoi
– zeantsoi, Commented Jan 2, 2014 at 5:06

zeantsoi · Accepted Answer · 2014-01-02 05:10:03Z

1

Your query is basically returning all a list of OrderProduct that belong to an Order – the way you've structured your query, it's basically agnostic to the User the Order belongs to. For instance – according to your schema – customer #1 has order #1, but order #1 also belongs to customer #2. Because the query is for the order products that belong to an order, order #1 will return entries for both customer #1 and customer #2.

Because OrderProduct has foreign keys to both Customer and Order, you can try a more explicit query for retrieving the order products that belong to a specific customer and order:

OrderProduct.where(:customer_id => 1, :order_id => 1)

UPDATE:

You have some issues in your seeds.rb that are resulting in convoluted relationships. You've defined that Order belongs to Customer, but your database seed suggests that orders belong to multiple customers.

orderproducts = OrderProduct.create([
  {:customer_id => 1, :order_id => 1, :product_id => 1},
  {:customer_id => 1, :order_id => 1, :product_id => 2},
  {:customer_id => 1, :order_id => 2, :product_id => 4},
  {:customer_id => 1, :order_id => 2, :product_id => 7},
  {:customer_id => 1, :order_id => 2, :product_id => 3},
  {:customer_id => 2, :order_id => 1, :product_id => 5}, # Order 1 already belongs to Customer 1
  {:customer_id => 2, :order_id => 1, :product_id => 6}, # Order 1 already belongs to Customer 1
  {:customer_id => 2, :order_id => 2, :product_id => 3}, # Order 2 already belongs to Customer 1
  {:customer_id => 2, :order_id => 2, :product_id => 4}, # Order 2 already belongs to Customer 1
  ])

Once you've ensured that each order is associated with a single customer, you can issue the your original query to retrieve the order products belonging to a user's order:

@customers.find(1).orders.find(1).order_products
#=> OrderProduct id: 1, customer_id: 1, order_id: 1, product_id: 1, created_at: "2014-01-02 02:51:36", updated_at: "2014-01-02 02:51:36">,
#=> OrderProduct id: 2, customer_id: 1, order_id: 1, product_id: 2, created_at: "2014-01-02 02:51:36", updated_at: "2014-01-02 02:51:36">,

edited Jan 2, 2014 at 5:10

answered Jan 2, 2014 at 3:42

zeantsoi

26.3k7 gold badges71 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AxleMax Over a year ago

Brilliant zeantsoi!! I am always very grateful for the guidance of experts. Thankyou so much. :-)

AxleMax Over a year ago

Zeantsoi, can I ask one more question please? Would you remove the customer_id from the OrderProcuct model's schema altogether? (and the corresponding seed data)

AxleMax Over a year ago

A note for other Ruby Noobies like me: @customers.find(1).orders.find(1).order_products does return the products for that order but @customers.find(1).orders.find(2).order_products fails because the next order_id for this customer is 4. So @customers.find(1).orders.find(4).order_products works. If you want to enumerate / iterate the orders for customer, use @customers.find(1).orders[0] to access the array (ActiveRecord::Associations::CollectionProxy: object)

zeantsoi Over a year ago

I would, as your Customer model currently has no defined relationship to OrderProduct (so the relationship effectively is non-existent, unless through the intervening Order model). You should probably look into has_many :through – this is a perfect use case for that particular relationship.

Srikanth Venugopalan · Accepted Answer · 2014-01-02 03:48:23Z

0

If you know the customer_id and order_id, you can query OrderProduct as such

OrderProduct.where(customer_id: 1, order_id: 1)

However you can also exploit active record associations to have something like

Customer.find(1).orders.first.order_products # to get all order_products of the first order of the customer.

OR

Customer.find(1).where(order_id: 1).order_products

Refer to this excellent guide from the docs

answered Jan 2, 2014 at 3:48

Srikanth Venugopalan

9,0495 gold badges39 silver badges76 bronze badges

4 Comments

zeantsoi Over a year ago

Wow... the first part of your answer looks oddly familiar. But the second part is simply rehashing the code from the OP's question – and it won't work. The relationship between Customer and Order is many-to-many, so the query you've suggested (i.e., rehashed from the question) may return order products belonging to more than one user.

AxleMax Over a year ago

I appreciate the reply to this question. I found it very useful. In response to zeantsoi's comment, I have tested the second part of this answer and it does not work for me.

Srikanth Venugopalan Over a year ago

@zeantsoi - :).. I saw your post after I posted mine. But you did beat me to it. On Customer-Order, how do you see a many-many? Order is associated with a single customer, no? At least the schema seems to suggest that.

zeantsoi Over a year ago

The models define a belongs-to/has-many relationship, but the seeds.rb suggests a many-to-many. This is primarily an issue with the data, rather than the code.

T-CatSan · Accepted Answer · 2014-01-02 04:02:13Z

0

It looks like you order_products is meant to be a join table for orders and products. You might want to take a look at the has_many :through association as described here: http://guides.rubyonrails.org/association_basics.html#the-has-many-through-association

answered Jan 2, 2014 at 4:02

T-CatSan

1,5411 gold badge9 silver badges3 bronze badges

Collectives™ on Stack Overflow

RoR ActiveRecord Query producing unexpected result

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related