Add all On Sale Items to a Category

[BRPhotoroom]

We are having some trouble with sale items on our site. Our original developers set the site up with a "clearance" page (inessa.com/clearance).  It functions properly, automatically showing any item with a reduced price. But we are now finding that because of the size of the site, and the number of items on the page, the load time is getting out of hand.

 

We have two options:

 

Integrate the pagination feature of a normal category (http://www.inessa.com/antique-chandeliers-60), something our new DEV team says will cost $1600 and two weeks of work.

 

Or the second option I was thinking was to just create a new "Clearance" category and add all the on-sale items to it. Problem being, there are several hundred on-sale items, and I can't think of a good way to round them all up to create this category.

 

If anyone has a tip on how to perform either option, I'd be extremely grateful.

[PascalVG]

- What PrestaShop version do you use?

 

What kind of discount(s) do you give your products?

- Quantity dependent (on Price tab at product)

- Cart Rule

- Catalog Price rule

 

 

You could make some SQL query that checks if a product has any discount at this moment, and if so, add a row to the ps_category_product table for that product, with the id of the 'DISCOUNT' category that you created.

 

 

My 2 cents,

pascal

[vekia]

prestashop by default has got page that displays page with products that have currently dropped prices,

it's a page called "SPECIALS" and it contains pagination

 

this is what you're looking for?

 

 

btw 1600$ for pagination sounds like a joke

[BRPhotoroom]

prestashop by default has got page that displays page with products that have currently dropped prices,

it's a page called "SPECIALS" and it contains pagination

 

this is what you're looking for?

 

 

btw 1600$ for pagination sounds like a joke

That is exactly what I was looking for vekia, Thanks!

 

It looks like it outputs to http://www.inessa.com/prices-drop

 

 

Is there a way to modify the query so that it excludes sold items?

Also is there a way to raise the pagination bar to the top?

[PascalVG]

You probably have to modify (or override, if you know how) file classes/Product.php:   (Make backup!!!)

 

Find function:

public static function getPricesDrop($id_lang, $page_number = 0, $nb_products = 10, $count = false,

$order_by = null, $order_way = null, $beginning = false, $ending = false, Context $context = null)

 

In there, you see some SQL code:

		$sql = '
		SELECT
			p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`,
			MAX(product_attribute_shop.id_product_attribute) id_product_attribute,
			pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`,
			pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name,
			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 AS new
		FROM `'._DB_PREFIX_.'product` p
		'.Shop::addSqlAssociation('product', 'p').'
		LEFT JOIN '._DB_PREFIX_.'product_attribute pa ON (pa.id_product = p.id_product)
		'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on=1').'
		'.Product::sqlStock('p', 0, false, $context->shop).'
		LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (
			p.`id_product` = pl.`id_product`
			AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
		)
		LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product`)'.
		Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
		LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
		LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
		WHERE product_shop.`active` = 1
		AND product_shop.`show_price` = 1

                AND stock.`quantity` > 0

		'.($front ? ' AND p.`visibility` IN ("both", "catalog")' : '').'
		'.((!$beginning && !$ending) ? ' AND p.`id_product` IN ('.((is_array($tab_id_product) && count($tab_id_product)) ? implode(', ', $tab_id_product) : 0).')' : '').'
		'.$sql_groups.'
		GROUP BY product_shop.id_product
		ORDER BY '.(isset($order_by_prefix) ? pSQL($order_by_prefix).'.' : '').pSQL($order_by).' '.pSQL($order_way).'
		LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products;

		$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);

		if (!$result)
			return false;

Add the red line. Hope that does the trick.

 

What do you mean exactly with ' raise the pagination bar to the top'? Where exactly do you want it?

 

pascal.

[PascalVG]

(N.B. Totally agree with Vekia that pagination quote of $1,600 and two weeks is laughable...)

[ToffeeApple]

I still can't get this to work. I have a product that I've added a specific price to (drop by 10%), when I view the product page it clearly shows the specials price so I know that has been applied. My price-drops page however is empty.

I think my issue is in the page itself. Should it exist as a standard page in the Pages list? It didn't so I added it as shown in the attached but I'm not even sure if simply sticking the "prices-drop" in as the friendly url is what I should do? As when i view the page at http://www.buckleberries.com/content/13-prices-drop its just a blank page.

I'm on version 1.7.6.3, any help would be appreciated.

 

Peter