Search class query anomolies

[MrBaseball34]

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.

[MrBaseball34]

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);