I have 2 tables, products
and stores
, and a pivot table in the middle called product_store
.
Here is the relevant portion of the table structures (from MySQL):
products
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`global` tinyint(1) NOT NULL DEFAULT '0',
stores
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
product_store
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned NOT NULL,
`store_id` int(10) unsigned NOT NULL,
`show` tinyint(1) NOT NULL,
The logic of the global
vs show
flags are as follows:
- If the
global
flag is 1, then assume this product is on every store, except when there is aproduct_store
entry withshow
== 0. - If the
global
flag is 0, then assume this product is only on stores where there is aproduct_store
entry withshow
== 1.
In other words, global
== 1 means show this product everywhere, show
== 1 means show the product in this store, show
== 0 means do not show the product in this store, and show
takes precedence over global
.
What I want to be able to do is fetch products based on a known store_id, where:
- products.global == 1 AND there is NO product_store entry for the product_id, store_id
- OR
- there is a product_store entry for the product_id, store_id AND product_store.show == 1
Currently I have this query, with a GROUP BY
and a nested SELECT
but it pulls many records from the database which are then consolidated by the GROUP BY
and it's quite slow. I am thinking that there must be something that's faster, without the nested select.
SELECT products.id, products.name, products.global, product_store.show, product_store.store_id
FROM products
LEFT JOIN product_store ON products.id=product_store.product_id
WHERE ((products.global=1 AND
((SELECT COUNT(*) FROM product_store WHERE product_store.store_id=226 AND product_store.product_id=products.id) = 0)
)
OR (product_store.store_id=226 AND product_store.show=1)
)
GROUP BY products.id
;
226
is the store ID in this case, I only need to run this query for one store at a time so this can be considered as a constant.
Note that if I do the above query with the GROUP BY
then I'm getting about 14 records which is correct (when I do the count myself). The system has a few thousand products and a few hundred stores so that product_store join table is rather large. If I do the above query without the GROUP BY
then I'm getting ~ 1500 records which appears to be the list of all product/product store combinations that fit the LEFT JOIN
clause.
Just to re-iterate, the above query works fine (although in MySQL versions > 5.7.5 there will be issues with the GROUP BY
) but it appears to be inefficient and I am trying to figure out something better and faster.
SOME FURTHER DETAILS ON TABLE CONTENTS
Here are some quick queries on the relevant products and stores, etc. First to show ALL relevant matching products -- there are 15, all of which have global=1.
SELECT products.id, MD5(products.name), products.global
FROM products
-- .. omitted some classification information here
;
+-----+----------------------------------+--------+
| id | md5(products.name) | global |
+-----+----------------------------------+--------+
| 555 | 56e597c43cff47af57f006b7de7ff552 | 1 |
| 591 | 7ba5549546ce20e1dee02c7f6454b16f | 1 |
| 558 | 7b66d8dce0c112b3966d1940de44f9ad | 1 |
| 556 | bd3d39b9f5194184ea0853c70d7faa07 | 1 |
| 590 | ef6eccbd3795fbe70890220c09ed880d | 1 |
| 559 | b763e2b9ba305538b497da97fba066ca | 1 |
| 593 | e26744d617ed12ebffea4b996fecbbef | 1 |
| 594 | 06ba8ab6649c83577db053e4a89c1655 | 1 |
| 596 | 79f9241643e84e2a27bfc0b260432e82 | 1 |
| 595 | 85fe772dc61d27292c09a7604cf76dd7 | 1 |
| 597 | fd6c21db00e1b679c0d0d2ef8f3f6e00 | 1 |
| 560 | c8056818e3ed5885188d78a9440fa77c | 1 |
| 561 | 33d79c194e39e8e84cf743eefcd909df | 1 |
| 562 | 1c74d85823e509ab4b09e82314b09604 | 1 |
| 557 | b6a8027035b63bd4d4bd21169434426d | 1 |
+-----+----------------------------------+--------+
Now to look at the join with the product_store table. There is only one entry there for store_id 226, and it has show=0.
SELECT products.id, MD5(products.name), products.global, product_store.show
FROM products
INNER JOIN product_store ON products.id=product_store.product_id
-- omitted the classification information as per the above
WHERE product_store.store_id=226
;
+-----+----------------------------------+--------+------+
| id | MD5(products.name) | global | show |
+-----+----------------------------------+--------+------+
| 555 | 56e597c43cff47af57f006b7de7ff552 | 1 | 0 |
+-----+----------------------------------+--------+------+
... I therefore need to be able to construct a query that selects all 15 products where global=1 EXCEPT the product where product_store.show=0, therefore I need the set of 14 remaining products.
Would a UNION select work somehow? Or some form of intersect?