4.9. Many-to-Many – n:n Association

Up to now, we have always associated a database directly with another database. For many-to-many, we will associate two databases via a third database. As example for this kind of relation, we use an order in an online shop. In this type of shop system, a Product can appear in several orders (Order) and at the same time an order can contain several products. This is referred to as many-to-many. Let's recreate this scenario with code.

Preparation

Create the shop application:
$ rails new shop
  [...]
$ cd shop
A model for products:
$ rails generate model product name 'price:decimal{7,2}'
  [...]
$
A model for an order:
$ rails generate model order delivery_address
  [...]
$
And a model for individual items of an order:
$ rails generate model line_item order_id:integer product_id:integer quantity:integer
  [...]
$ 
Then, create the database:
$ rake db:migrate
  [...]
$

The Association

An order (Order) consists of one or several items (LineItem). This LineItem consists of the order_id, a product_id and the number of items ordered (quantity). The individual product is defined in the product database (Product).

Note

All models have been generated in minimal form here. After all, the point is not creating a realistic shop system, but helping you understand.
Associating the models happens as always in the directory app/models. First, in the file app/models/order.rb:
class Order < ActiveRecord::Base
  attr_accessible :delivery_address

  has_many :line_items
  has_many :products, :through => :line_items
end
Then in the counterpart in the file app/models/product.rb:
class Product < ActiveRecord::Base
  attr_accessible :name, :price

  has_many :line_items
  has_many :orders, :through => :line_items
end
Finally, the file app/models/line_item.rb:
class LineItem < ActiveRecord::Base
  attr_accessible :order_id, :product_id, :quantitiy

  belongs_to :order
  belongs_to :product
end

Logically, the Association Works Like has_many

As we implement the associations via has_many, most things will already be familiar to you from Section 4.8, “has_many – 1:n Association”. I am going to discuss a few examples. For more details, see Section 4.8, “has_many – 1:n Association”.
First we populate our product database with the following values:
$ rails console
Loading development environment (Rails 3.2.9)
>> milk = Product.create(name: 'Milk (1 liter)', price: 0.45)
   (0.1ms)  begin transaction
  SQL (5.7ms)  INSERT INTO "products" ("created_at", "name", "price", "updated_at") VALUES (?, ?, ?, ?)  [["created_at", Sun, 18 Nov 2012 13:56:17 UTC +00:00], ["name", "Milk (1 liter)"], ["price", #<BigDecimal:7ff7290dab88,'0.45E0',9(45)>], ["updated_at", Sun, 18 Nov 2012 13:56:17 UTC +00:00]]
   (3.8ms)  commit transaction
=> #<Product id: 1, name: "Milk (1 liter)", price: #<BigDecimal:7ff72966cb28,'0.45E0',9(45)>, created_at: "2012-11-18 13:56:17", updated_at: "2012-11-18 13:56:17">
>> butter = Product.create(name: 'Butter (250 gr)', price: 0.75)
   (0.1ms)  begin transaction
  SQL (0.6ms)  INSERT INTO "products" ("created_at", "name", "price", "updated_at") VALUES (?, ?, ?, ?)  [["created_at", Sun, 18 Nov 2012 13:56:46 UTC +00:00], ["name", "Butter (250 gr)"], ["price", #<BigDecimal:7ff7296b6bb0,'0.75E0',9(45)>], ["updated_at", Sun, 18 Nov 2012 13:56:46 UTC +00:00]]
   (2.6ms)  commit transaction
=> #<Product id: 2, name: "Butter (250 gr)", price: #<BigDecimal:7ff7294d4e28,'0.75E0',9(45)>, created_at: "2012-11-18 13:56:46", updated_at: "2012-11-18 13:56:46">
>> flour = Product.create(name: 'Flour (1 kg)', price: 0.45)
   (0.1ms)  begin transaction
  SQL (0.5ms)  INSERT INTO "products" ("created_at", "name", "price", "updated_at") VALUES (?, ?, ?, ?)  [["created_at", Sun, 18 Nov 2012 13:57:13 UTC +00:00], ["name", "Flour (1 kg)"], ["price", #<BigDecimal:7ff7296d8a30,'0.45E0',9(45)>], ["updated_at", Sun, 18 Nov 2012 13:57:13 UTC +00:00]]
   (2.3ms)  commit transaction
=> #<Product id: 3, name: "Flour (1 kg)", price: #<BigDecimal:7ff7296fa310,'0.45E0',9(45)>, created_at: "2012-11-18 13:57:13", updated_at: "2012-11-18 13:57:13">
>> 
Now we create a new Order object with the name order:
>> order = Order.new(delivery_address: '123 Acme Street, ACME STATE 12345')
=> #<Order id: nil, delivery_address: "123 Acme Street, ACME STATE 12345", created_at: nil, updated_at: nil>
>> 
Logically, this new order does not yet contain any products:
>> order.products
=> []
>>
As often, there are several ways of adding products to the order. The simplest way: as the products are integrated as array, you can simply insert them as elements of an array:
>> order.products << milk
=> [#<Product id: 1, name: "Milk (1 liter)", price: #<BigDecimal:7ff7291d4700,'0.45E0',9(45)>, created_at: "2012-11-18 13:56:17", updated_at: "2012-11-18 13:56:17">]
>> order.products
=> [#<Product id: 1, name: "Milk (1 liter)", price: #<BigDecimal:7ff72925b020,'0.45E0',9(45)>, created_at: "2012-11-18 13:56:17", updated_at: "2012-11-18 13:56:17">]
>>
But if the customer wants to buy three liters of milk instead of one liter, we need to enter it in the LineItem (in the linking element) table. ActiveRecord already build an object for us:
>> order.line_items
=> [#<LineItem id: nil, order_id: nil, product_id: 1, quantity: nil, created_at: nil, updated_at: nil>]
>> 
But the object is not yet saved the database. After we do this via save, we can change the quantity in the LineItem object:
>> order.save
   (0.2ms)  begin transaction
  SQL (0.6ms)  INSERT INTO "orders" ("created_at", "delivery_address", "updated_at") VALUES (?, ?, ?)  [["created_at", Sun, 18 Nov 2012 14:07:28 UTC +00:00], ["delivery_address", "123 Acme Street, ACME STATE 12345"], ["updated_at", Sun, 18 Nov 2012 14:07:28 UTC +00:00]]
  SQL (0.3ms)  INSERT INTO "line_items" ("created_at", "order_id", "product_id", "quantity", "updated_at") VALUES (?, ?, ?, ?, ?)  [["created_at", Sun, 18 Nov 2012 14:07:28 UTC +00:00], ["order_id", 1], ["product_id", 1], ["quantity", nil], ["updated_at", Sun, 18 Nov 2012 14:07:28 UTC +00:00]]
   (3.0ms)  commit transaction
=> true
>> order.line_items
=> [#<LineItem id: 1, order_id: 1, product_id: 1, quantity: nil, created_at: "2012-11-18 14:07:28", updated_at: "2012-11-18 14:07:28">]
>> order.line_items.first.update_attributes(quantity: 3)
   (0.1ms)  begin transaction
   (0.3ms)  UPDATE "line_items" SET "quantity" = 3, "updated_at" = '2012-11-18 14:07:54.080872' WHERE "line_items"."id" = 1
   (3.0ms)  commit transaction
=> true
>>
Alternatively, we can also buy butter twice directly by adding a LineItem:
>> order.line_items.create(product_id: butter.id, quantity: 2)
   (0.1ms)  begin transaction
  SQL (0.5ms)  INSERT INTO "line_items" ("created_at", "order_id", "product_id", "quantity", "updated_at") VALUES (?, ?, ?, ?, ?)  [["created_at", Sun, 18 Nov 2012 14:09:14 UTC +00:00], ["order_id", 1], ["product_id", 2], ["quantity", 2], ["updated_at", Sun, 18 Nov 2012 14:09:14 UTC +00:00]]
   (3.1ms)  commit transaction
=> #<LineItem id: 2, order_id: 1, product_id: 2, quantity: 2, created_at: "2012-11-18 14:09:14", updated_at: "2012-11-18 14:09:14">
>> 

Warning

At this point, please do not make the mistake of thinking that the object order already has the butter you ordered. Let's have a look at the products:
>> order.products
=> [#<Product id: 1, name: "Milk (1 liter)", price: #<BigDecimal:7ff72d50df80,'0.45E0',9(45)>, created_at: "2012-11-18 13:56:17", updated_at: "2012-11-18 13:56:17">]
>> 
But in the database, it is of course correct:
>> Order.first.products
  Order Load (0.2ms)  SELECT "orders".* FROM "orders" LIMIT 1
  Product Load (0.2ms)  SELECT "products".* FROM "products" INNER JOIN "line_items" ON "products"."id" = "line_items"."product_id" WHERE "line_items"."order_id" = 1
=> [#<Product id: 1, name: "Milk (1 liter)", price: #<BigDecimal:7ff72d5c0068,'0.45E0',9(45)>, created_at: "2012-11-18 13:56:17", updated_at: "2012-11-18 13:56:17">, #<Product id: 2, name: "Butter (250 gr)", price: #<BigDecimal:7ff72d5bebf0,'0.75E0',9(45)>, created_at: "2012-11-18 13:56:46", updated_at: "2012-11-18 13:56:46">]
>> 
In this specific case, you would need to reload the object from the database via the method reload:
>> order.reload
  Order Load (0.3ms)  SELECT "orders".* FROM "orders" WHERE "orders"."id" = ? LIMIT 1  [["id", 1]]
=> #<Order id: 1, delivery_address: "123 Acme Street, ACME STATE 12345", created_at: "2012-11-18 14:07:28", updated_at: "2012-11-18 14:07:28">
>> order.products
  Product Load (0.3ms)  SELECT "products".* FROM "products" INNER JOIN "line_items" ON "products"."id" = "line_items"."product_id" WHERE "line_items"."order_id" = 1
=> [#<Product id: 1, name: "Milk (1 liter)", price: #<BigDecimal:7ff72d595ed0,'0.45E0',9(45)>, created_at: "2012-11-18 13:56:17", updated_at: "2012-11-18 13:56:17">, #<Product id: 2, name: "Butter (250 gr)", price: #<BigDecimal:7ff72d594260,'0.75E0',9(45)>, created_at: "2012-11-18 13:56:46", updated_at: "2012-11-18 13:56:46">]
>>
Let's enter a second order with all available products into the system:
>> order2 = Order.create(delivery_address: '2, Test Road')
   (0.1ms)  begin transaction
  SQL (0.6ms)  INSERT INTO "orders" ("created_at", "delivery_address", "updated_at") VALUES (?, ?, ?)  [["created_at", Sun, 18 Nov 2012 14:11:05 UTC +00:00], ["delivery_address", "2, Test Road"], ["updated_at", Sun, 18 Nov 2012 14:11:05 UTC +00:00]]
   (3.1ms)  commit transaction
=> #<Order id: 2, delivery_address: "2, Test Road", created_at: "2012-11-18 14:11:05", updated_at: "2012-11-18 14:11:05">
>> order2.products << Product.all
  Product Load (0.3ms)  SELECT "products".* FROM "products" 
   (0.1ms)  begin transaction
  SQL (0.4ms)  INSERT INTO "line_items" ("created_at", "order_id", "product_id", "quantity", "updated_at") VALUES (?, ?, ?, ?, ?)  [["created_at", Sun, 18 Nov 2012 14:11:22 UTC +00:00], ["order_id", 2], ["product_id", 1], ["quantity", nil], ["updated_at", Sun, 18 Nov 2012 14:11:22 UTC +00:00]]
  SQL (0.2ms)  INSERT INTO "line_items" ("created_at", "order_id", "product_id", "quantity", "updated_at") VALUES (?, ?, ?, ?, ?)  [["created_at", Sun, 18 Nov 2012 14:11:22 UTC +00:00], ["order_id", 2], ["product_id", 2], ["quantity", nil], ["updated_at", Sun, 18 Nov 2012 14:11:22 UTC +00:00]]
  SQL (0.2ms)  INSERT INTO "line_items" ("created_at", "order_id", "product_id", "quantity", "updated_at") VALUES (?, ?, ?, ?, ?)  [["created_at", Sun, 18 Nov 2012 14:11:22 UTC +00:00], ["order_id", 2], ["product_id", 3], ["quantity", nil], ["updated_at", Sun, 18 Nov 2012 14:11:22 UTC +00:00]]
   (2.6ms)  commit transaction
  Product Load (0.2ms)  SELECT "products".* FROM "products" INNER JOIN "line_items" ON "products"."id" = "line_items"."product_id" WHERE "line_items"."order_id" = 2
=> [#<Product id: 1, name: "Milk (1 liter)", price: #<BigDecimal:7ff72d5471e0,'0.45E0',9(45)>, created_at: "2012-11-18 13:56:17", updated_at: "2012-11-18 13:56:17">, #<Product id: 2, name: "Butter (250 gr)", price: #<BigDecimal:7ff72d543bd0,'0.75E0',9(45)>, created_at: "2012-11-18 13:56:46", updated_at: "2012-11-18 13:56:46">, #<Product id: 3, name: "Flour (1 kg)", price: #<BigDecimal:7ff72d540408,'0.45E0',9(45)>, created_at: "2012-11-18 13:57:13", updated_at: "2012-11-18 13:57:13">]
>> order2.save
   (0.1ms)  begin transaction
   (0.1ms)  commit transaction
=> true
>>
Now we can try out the other direction of a many-to-many association. Let's search for all orders that contain the first product:
>> Product.first.orders
  Product Load (0.4ms)  SELECT "products".* FROM "products" LIMIT 1
  Order Load (0.2ms)  SELECT "orders".* FROM "orders" INNER JOIN "line_items" ON "orders"."id" = "line_items"."order_id" WHERE "line_items"."product_id" = 1
=> [#<Order id: 1, delivery_address: "123 Acme Street, ACME STATE 12345", created_at: "2012-11-18 14:07:28", updated_at: "2012-11-18 14:07:28">, #<Order id: 2, delivery_address: "2, Test Road", created_at: "2012-11-18 14:11:05", updated_at: "2012-11-18 14:11:05">]
>>  
Of course, we can also work with a joins (see the section called “joins”) and search for all orders that contain the product "Milk (1 liter)":
>> Order.joins(:products).where(:products => {name: 'Milk (1 liter)'})
  Order Load (0.2ms)  SELECT "orders".* FROM "orders" INNER JOIN "line_items" ON "line_items"."order_id" = "orders"."id" INNER JOIN "products" ON "products"."id" = "line_items"."product_id" WHERE "products"."name" = 'Milk (1 liter)'
=> [#<Order id: 1, delivery_address: "123 Acme Street, ACME STATE 12345", created_at: "2012-11-18 14:07:28", updated_at: "2012-11-18 14:07:28">, #<Order id: 2, delivery_address: "2, Test Road", created_at: "2012-11-18 14:11:05", updated_at: "2012-11-18 14:11:05">]
>> exit
$

Updates about this book will be published on my Twitter feed.