Many to Many, with another column

Question

I'm wondering if anyone has faced this issue.
Let's assume I have two tables: products and carts (related many to many).

Now, joining table has additional column - amount (how many products of particular type is in the cart).

My problem is that I cannot access "amount" column via following relation:

public function relations()
{       
    return array(
           'products'=>array(self::MANY_MANY, 'Product', 'cart_products(cart_id, product_id)'),

    );
}

I also tried:

'products'=>array(self::HAS_MANY, 'Product','product_id','through'=>'cart_products'),

with no luck.

Answer 1

Yii's MANY_MANY implementation has limitations which might be addressed in later Yii version.

The solution to this is to use one more model class for the MANY_MANY table. For example create AR class CartProduct and then the relation for your cart would become:

public function relations()
{       
    return array(
           'cartProducts'=>array(self::HAS_MANY, 'CartProduct', 'cart_id'),
           'products'=>array(self::HAS_MANY, 'Product', 'product_id', 'through' => 'CartProduct'),
    );
}

This way you will have reference to CartProducts model in the cartProducts magic public property of the Cart instance and will be able to update your "amount".

Now its good time that I say that I do not like your approach to the problem - having "amount" column that holds the count of the products in the cart, because you have to keep two truths (the actual amount of products that are mapped to the cart in the db and the counter cache "amount" field in the relating table"). Inless you have very very heavy application that needs this db denormalization, I would do a "counter relation" to get the products count, instead of caching that value in column, like this (add the following relation to your Cart model):

public function relations()
{       
    return array(
           'products'=>array(self::MANY_MANY, 'Product', 'cart_products(cart_id, product_id)'),
           'productsAmount'=>array(self::STAT, 'Product', 'cart_products(cart_id, product_id)'),

    );
}

This way, you can call the cart->protuctsAmount property and it will return the actual amount with a simple and fast count query (which will be cached I think) instead of relying on cached value that have to be regenerated by your code or by db triggers every time you change the cart products.