CODEWITHSUNDEEP
phpormyiimany-to-many
shulmey
shulmey

Reputation: 101

Yii Multiple Many-Many query

The best way to describe my question is to explain my situation:

I am using yii, so any response should be oriented towards that framework.

I have a database with products and categories in a many-many relationship, so a product table, a category table, and a category_product table for the relationships between them.

I also have a user table. Users are allowed to purchase certain categories of products, so there is another many-many relationship described by the category_user table, where an existing relationship indicates that the user is allowed to purchase any product in that category.

My question is this: If I currently have the user and product, is there an easy way to determine if the user can purchase the product, meaning that there exists a relationship of the user and product to the same category. I'm having a really hard time finding an efficient or simple way to accomplish this other than just grabbing an array of the category id's related to the user and another array of the category id's related to the product and then searching for a match within those.

Upvotes: 1

Views: 516

Answers (2)

Elbek
Elbek

Reputation: 3484

I see 2 ways you can do it:

  1. just use the ORM feature to load references:

    isbelongTo($user, $product){ foreach($user->categories as $category){ foreach($category->products as $pr){ if($pr->id==$product->id) return true; } } return false; }

  2. But believe I wouldn't do it this way, because it has to load a bunch of stuff, rather than using pure sql and it doesn't make the system 'smart'. Also ORM is used to load objects and not records, and in our case, we do not care about objects, so can use pure sql:

    isbelongTo($user, $product){ $connection=Yii::app()->db; $sql = 'select * from category_user cu inner join category_product cp on cu.categoryId = cp.categoryId where cp.productId = :productId cu.userId = :userId'; $command=$connection->createCommand($sql); $command->bindParam(':productId',$product->id,PDO::PARAM_STR); $command->bindParam(':usertId',$user->id,PDO::PARAM_STR); $value=$command->queryScalar(); return $value > 0; //if greater then 0 that means it belong to, so returns true }

I would definitely choose the 2nd approach!

Upvotes: 2

Asgaroth
Asgaroth

Reputation: 4334

Maybe ussing Relational Query with through, and you can call exist or something similar

Upvotes: 0

Related Questions