In my application (osCommerce) I had to modify a query that look like this:
SELECT sql_cache distinct p.products_image,
p.products_subimage1,
pd.products_name,
p.products_quantity,
p.products_model,
p.products_ordered,
p.products_id,
p.products_price,
p.products_date_added,
p.products_weight,
p.products_length,
p.products_width,
p.products_height,
p.products_tax_class_id,
p.products_status,
IF(s.status, s.specials_new_products_price, NULL) AS specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) AS final_price
FROM products p
LEFT JOIN specials s
ON p.products_id = s.products_id
LEFT JOIN products_to_categories p2c
ON p.products_id=p2c.products_id
LEFT JOIN products_description pd
ON p.products_id=pd.products_id
INNER JOIN filter_association_products fap
ON p.products_id =fap.products_id
LEFT JOIN products_attributes pa
ON p.products_id = pa.products_id
WHERE p.products_status = '1'
AND date_sub(curdate(),INTERVAL 3000 day) <= p.products_date_added
AND find_in_set(fap.filter_id,'126, 130')
ORDER BY p.products_date_added DESC,
pd.products_name
to end like this, for accurate results:
AND fap.filter_id IN (126, 130)
GROUP BY p.products_id
HAVING COUNT(DISTINCT fap.filter_id) = 2;
The issue I have now is that there is the following query that is using this new query and giving me wrong results.
SELECT COUNT( DISTINCT p.products_id ) AS total
FROM products p
LEFT JOIN specials s ON p.products_id = s.products_id
LEFT JOIN products_to_categories p2c
ON p.products_id = p2c.products_id
LEFT JOIN products_description pd
ON p.products_id = pd.products_id
INNER JOIN filter_association_products fap ON p.products_id = fap.products_id
LEFT JOIN products_attributes pa
ON p.products_id = pa.products_id
WHERE p.products_status = '1'
AND DATE_SUB( CURDATE( ) , INTERVAL 3000 DAY ) <= p.products_date_added
AND fap.filter_id IN ( 126, 130 )
GROUP BY p.products_id
HAVING COUNT( DISTINCT fap.filter_id ) =2
ORDER BY p.products_date_added DESC , pd.products_name
Which instead of giving a result of 1 row with the count of all the product_id
s in the original query, now gives a result of multiple rows (the same amount of rows as the expected total products count) with the number 1 in each of them.
The main issue seems to be the GROUP BY p.products_id
HAVING COUNT( DISTINCT fap.filter_id ) =2
Is there any way to modify the original query so that the count query will work correctly while still using AND fap.filter_id IN ( 126, 130 )
?
2
Answers
You need to use a subquery to get the results that you want:
You need to aggregate at the product id level to get the products that meet the original condition. Counting the number of such products requires another aggregation.
As Gordon suggested, using a subquery should generally work.
Since this query looks like a product listing query and since you are using osCommerce, you will bump into another core issue when you modify the query to use a subquery:
The count query of the split page results will fail as it does not support subqueries.
To resolve this also, you will need to modify the core code in
catalog/includes/classes/split_page_results.php
Change:
To:
More details here: http://forums.oscommerce.com/topic/290110-class-splitpageresults/