Tables:
products
id - integer
name - string
factories
id - integer
name - string
factory_product
factory_id - integer
product_id - integer
I want to get products that are produced by all factories.
How to do that?
I need SQL code.
CodePudding user response：
Try this query:
SELECT product_id
FROM factory_product
GROUP BY product_id
HAVING COUNT(factory_id) = (SELECT COUNT(*) FROM factories);
Factories Table:
| id | name |
|---|---|
| f1 | fa |
| f2 | fb |
| f3 | fc |
| f4 | fd |
| f5 | fe |
Product Table:
| id | name |
|---|---|
| p1 | pa |
| p2 | pb |
| p3 | pc |
Factory_Product Table:
| factory_id | product_id |
|---|---|
| f1 | p1 |
| f1 | p2 |
| f1 | p3 |
| f2 | p1 |
| f2 | p3 |
| f3 | p1 |
| f3 | p3 |
| f4 | p1 |
| f4 | p3 |
| f4 | p2 |
| f5 | p1 |
| f5 | p2 |
| f5 | p3 |
The Output my Query Producing:
| product_id |
|---|
| p1 |
| p3 |
CodePudding user response：
By use of belongsToMany relation
here this function add in your products model
public function withFactories() { return $this->belongsToMany(factories::class, factory_product::class,'product_id', 'factory_id'); }add function in your controller
$all_product = products::with('withFactories)->get();here must have foreign key in factory_product table
here you can get value of product value & Factories detail in withFactories array