How to properly organize relationships between tables in Yii?

Question

MySQL

Hello! I have 2 tables:

CREATE TABLE `orders` (
  `id` INT(255) NOT NULL AUTO_INCREMENT,
  `user_name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `goods` (
  `id` INT(255) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`id`)
)

And also 3 table, which is a link:

CREATE TABLE `orders_goods_assigment` (
  `id` INT(255) NOT NULL AUTO_INCREMENT,
  `order_id` INT(255) NOT NULL,
  `goods_id` INT(255) NOT NULL,
  PRIMARY KEY (`id`)
)

I tried to implement a relationship between the orders and goods tables like this (in the Order model):

public function relations() {           
  return [                              
    'goods' => [                        
      self::MANY_MANY,                  
      'Goods',                          
      'orders_goods_assigment(order_id, goods_id)'
    ]                                   
  ];                                    
}

Unfortunately, if I have, for example, several identical products refer to the same order, then Yii considers that there is only one such product.

INSERT INTO `orders_goods_assigment` (`order_id`, `goods_id`) VALUES (1, 2);
INSERT INTO `orders_goods_assigment` (`order_id`, `goods_id`) VALUES (1, 2);
INSERT INTO `orders_goods_assigment` (`order_id`, `goods_id`) VALUES (1, 2);

If we add these 3 rows to the table, then when loading the goods field in the Order model , Yii will show us an array with 1 record (instead of 3 identical ones).

$order = Order::model()->findByPk(1);
print_r($order->goods); # Array([0] => Goods)

Tell me please, what did I do wrong?

Answer 1

I would add the quantity to orders_goods_assignment