Jump to content

Search class query anomolies


MrBaseball34
 Share

Recommended Posts

I'm trying to build an advanced search facility and found that in the Search class,
things look little weird.

First, you get the list of eligible products:

$eligibleProducts = $db->ExecuteS('
   SELECT DISTINCT cp.`id_product`
   FROM `'._DB_PREFIX_.'category_group` cg
   INNER JOIN `'._DB_PREFIX_.'category_product` cp ON cp.`id_category` = cg.`id_category`
   INNER JOIN `'._DB_PREFIX_.'category` c ON cp.`id_category` = c.`id_category`
   INNER JOIN `'._DB_PREFIX_.'product` p ON cp.`id_product` = p.`id_product`
   WHERE c.`active` = 1 AND p.`active` = 1
   AND cg.`id_group` '.(!$cookie->id_customer ?  '= 1' : 'IN (
     SELECT id_group FROM '._DB_PREFIX_.'customer_group
     WHERE id_customer = '.(int)$cookie->id_customer.'
   )').'
   AND '.implode(' AND ', $whereArray));



Then, you iterate over the result set to build an array of product_ids:

$productPool = '';
   foreach ($eligibleProducts AS $product)
     if (!empty($product['id_product']))
       $productPool .= (int)$product['id_product'].',';
   if (empty($productPool))
     return ($ajax ? array() : array('total' => 0, 'result' => array()));
   $productPool = ((strpos($productPool, ',') === false) ? (' = '.(int)$productPool.' ') : (' IN ('.rtrim($productPool, ',').') '));



Then the $productPool value is used in both the ajax and normal query:

if ($ajax)
   {
     if (!$result = $db->ExecuteS('
     SELECT DISTINCT p.id_product, pl.name pname, cl.name cname,
       cl.link_rewrite crewrite, pl.link_rewrite prewrite '.$score.'
     FROM '._DB_PREFIX_.'product p
     INNER JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)$id_lang.')
     INNER JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.(int)$id_lang.')
     WHERE p.`id_product` '.$productPool.'
     ORDER BY position DESC LIMIT 10'))
       return false;

     return $result;
   }

   $queryResults = '
   SELECT SQL_CALC_FOUND_ROWS p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`, pa.`id_product_attribute`,
     tax.`rate`, i.`id_image`, il.`legend`, m.`name` manufacturer_name '.$score.', DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0 new
   FROM '._DB_PREFIX_.'product p
   INNER JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)$id_lang.')
   LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
                                              AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
                                                 AND tr.`id_state` = 0)
     LEFT JOIN `'._DB_PREFIX_.'tax` tax ON (tax.`id_tax` = tr.`id_tax`)
   LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
   LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
   LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
   LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
   WHERE p.`id_product` '.$productPool.'
   '.($orderBy ? 'ORDER BY  '.$orderBy : '').($orderWay ? ' '.$orderWay : '').'
   LIMIT '.(int)(($pageNumber - 1) * $pageSize).','.(int)$pageSize;

   $result = $db->ExecuteS($queryResults);



Now, wouldn't it have been easier to just use a subquery instead?
ajax query:

if ($ajax)
   {
     if (!$result = $db->ExecuteS('
     SELECT DISTINCT p.id_product, pl.name pname, cl.name cname,
       cl.link_rewrite crewrite, pl.link_rewrite prewrite '.$score.'
     FROM '._DB_PREFIX_.'product p
     INNER JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)$id_lang.')
     INNER JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.(int)$id_lang.')
     WHERE p.`id_product` IN (SELECT DISTINCT cp.`id_product`
                              FROM `'._DB_PREFIX_.'category_group` cg
                              INNER JOIN `'._DB_PREFIX_.'category_product` cp ON cp.`id_category` = cg.`id_category`
                              INNER JOIN `'._DB_PREFIX_.'category` c ON cp.`id_category` = c.`id_category`
                              INNER JOIN `'._DB_PREFIX_.'product` p ON cp.`id_product` = p.`id_product`
                              WHERE c.`active` = 1 AND p.`active` = 1
                              AND cg.`id_group` '.(!$cookie->id_customer ?  '= 1' : 'IN (
                                SELECT id_group FROM '._DB_PREFIX_.'customer_group
                                WHERE id_customer = '.(int)$cookie->id_customer.'
                              )').'
                              AND '.implode(' AND ', $whereArray).')
     ORDER BY position DESC LIMIT 10'))
       return false;

     return $result;
   }



normal query is posted in followup because of post character limitations.

Share this post


Link to post
Share on other sites

normal query:

   $queryResults = '
   SELECT SQL_CALC_FOUND_ROWS p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`, pa.`id_product_attribute`,
     tax.`rate`, i.`id_image`, il.`legend`, m.`name` manufacturer_name '.$score.', DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0 new
   FROM '._DB_PREFIX_.'product p
   INNER JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)$id_lang.')
   LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
                                              AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
                                              AND tr.`id_state` = 0)
   LEFT JOIN `'._DB_PREFIX_.'tax` tax ON (tax.`id_tax` = tr.`id_tax`)
   LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
   LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
   LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
   LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
   WHERE p.`id_product` IN (SELECT DISTINCT cp.`id_product`
                            FROM `'._DB_PREFIX_.'category_group` cg
                            INNER JOIN `'._DB_PREFIX_.'category_product` cp ON cp.`id_category` = cg.`id_category`
                            INNER JOIN `'._DB_PREFIX_.'category` c ON cp.`id_category` = c.`id_category`
                            INNER JOIN `'._DB_PREFIX_.'product` p ON cp.`id_product` = p.`id_product`
                            WHERE c.`active` = 1 AND p.`active` = 1
                            AND cg.`id_group` '.(!$cookie->id_customer ?  '= 1' : 'IN (
                              SELECT id_group FROM '._DB_PREFIX_.'customer_group
                              WHERE id_customer = '.(int)$cookie->id_customer.'
                            )').($orderBy ? ') ORDER BY  '.$orderBy : '').($orderWay ? ' '.$orderWay : '').'
   LIMIT '.(int)(($pageNumber - 1) * $pageSize).','.(int)$pageSize;

   $result = $db->ExecuteS($queryResults);

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More