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?

Answer 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">, 

Answer 2

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

Answer 3

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