skip to Main Content

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_ids 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


  1. You need to use a subquery to get the results that you want:

    select count(*) 
    from (<old query here>) s;
    

    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.

    Login or Signup to reply.
  2. 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:

    $count_query = tep_db_query("select count(" . $count_string . ") as total " . substr($this->sql_query, $pos_from, ($pos_to - $pos_from)));
    

    To:

    $count_query = tep_db_query("select count(*) as total from (" . $this->sql_query . ") AS derivedtable1");
    

    More details here: http://forums.oscommerce.com/topic/290110-class-splitpageresults/

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search