Put products out of stock at the end of the list

[Juanaco]

Hi there, we want to change the order of the products in each section, we want to put the products out of stock at the end of the list in each page in our website, any tricks to make it?

Thanks

[lordignus]

Better to do this with an override instead of modifying the core class, but for the sake of a short answer...

in classes/Category.php, in the getProducts function, look for this line:

$sql .= ' ORDER BY ' . (!empty($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . bqSQL($orderBy) . '` ' . pSQL($orderWay) . '
			LIMIT ' . (((int) $pageNumber - 1) * (int) $productPerPage) . ',' . (int) $productPerPage;

and replace it with this one:

$sql .= ' ORDER BY stock.quantity = 0 ASC,' . (!empty($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . bqSQL($orderBy) . '` ' . pSQL($orderWay) . '
        LIMIT ' . (((int) $pageNumber - 1) * (int) $productPerPage) . ',' . (int) $productPerPage;

That's in 1.7.8.0, so will depend on your version but the concept is the same, we're just adding stock.quantity = 0 ASC into the ORDER BY clause in the sql query that fetches category products.

[Ali Samie]

I found this,

Although this is not practical, becasue it sorts based on quantity, and as a shop owner, they need only out of stock at the end of the list

[Ali Samie]

I think it is better to be two queries.

One to get only available products by default order One to get out of stock products. and then put the result together