Adding sql statement to fetch specific price

[Kalyma]

Hi, i created a new class for fetch the new products, but filtered by dates, so I can have a link for the most recent products in the last week, the last month, etc.

 

Everything works fine, but with one exception: the products doesn't show the sale price.

 

This is the class:

class LatestCore extends ObjectModel {
 public function GetLatest($filter, $lang){
  $sql = ' SELECT pr.*, pl.*, im.*, m.`name` AS `manufacturer_name`
	 FROM `'._DB_PREFIX_.'product` pr
	 LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (pr.`id_product` = pl.`id_product` AND `id_lang` ='.$lang.') 
	 LEFT JOIN `'._DB_PREFIX_.'image` im ON (pr.`id_product` = im.`id_product` AND `cover` =1) 
	 LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = pr.`id_manufacturer`)
	 WHERE DATE(pr.`date_add`) >= DATE_SUB(DATE(NOW()), INTERVAL '.$filter.' DAY)	AND pr.`active` = 1	
	 ORDER BY DATE(pr.`date_add`) DESC';

$results = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);
return $results;
  }
}

I work around a fix, making a left join with the ps_specific_price table, but with this code, the products shows like duplicated and triplicated some times.

$sql = '
 SELECT pr.*, pl.*, `reduction`, `reduction_type`, im.*, m.`name` AS `manufacturer_name`
 FROM `'._DB_PREFIX_.'product` pr
 LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (pr.`id_product` = pl.`id_product` AND `id_lang` ='.$lang.') 
 LEFT JOIN `'._DB_PREFIX_.'specific_price` sp ON (pr.`id_product` = sp.`id_product`)
 LEFT JOIN `'._DB_PREFIX_.'image` im ON (pr.`id_product` = im.`id_product` AND `cover` =1) LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = pr.`id_manufacturer`)
 WHERE DATE(pr.`date_add`) >= DATE_SUB(DATE(NOW()), INTERVAL '.$filter.' DAY)	AND pr.`active` = 1	
 ORDER BY DATE(pr.`date_add`) DESC
';

I know there's something wrong in the left join statement. Anyone has a clue on how can I fix this?

 

This is the live site:

 

http://leonceshop.com/latest.php?filter=7

 

Thanks!

[NemoPS]

Try adding a group by p.id_product

[Kalyma]

Sorry but I don't get it.... can you explain me a little more? Maybe an example?

 

Thanks!

[NemoPS]

sure

$sql = '
SELECT pr.*, pl.*, `reduction`, `reduction_type`, im.*, m.`name` AS `manufacturer_name`
FROM `'._DB_PREFIX_.'product` pr
LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (pr.`id_product` = pl.`id_product` AND `id_lang` ='.$lang.')
LEFT JOIN `'._DB_PREFIX_.'specific_price` sp ON (pr.`id_product` = sp.`id_product`)
LEFT JOIN `'._DB_PREFIX_.'image` im ON (pr.`id_product` = im.`id_product` AND `cover` =1) LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = pr.`id_manufacturer`)
WHERE DATE(pr.`date_add`) >= DATE_SUB(DATE(NOW()), INTERVAL '.$filter.' DAY)    AND pr.`active` = 1    
ORDER BY DATE(pr.`date_add`) DESC

GROUP BY p.id_product
';