[SOLVED] Out of Stock Items Last

Progenix wrote: ↑

Wed Jul 26, 2023 12:27 pm

Hi guys,

I'm having a problem with getting out of stock products to show last. The (free) ocmod plugin I'm using to display out of stock products last contains the following:

Code: Select all

<file path="catalog/model/catalog/product.php">
    <operation>
      <search>
        <![CDATA[$query = $this->db->query($sql);]]>
      </search>
      <add position="before">
        <![CDATA[$sql = str_replace('ORDER BY ', 'ORDER BY IF(p.quantity > 0, 0, 1), ', $sql);]]>
      </add>
    </operation>
  </file>

Simple, right? Well it works, to an extent. The products DO show in the order I would expect, but when trying to view a product I get the following mess of an error:

Fatal error: Uncaught exception 'Exception' with message 'Error: Unknown column 'p.quantity' in 'order clause'<br />Error No: 1054<br/>SELECT * FROM oc_product_image WHERE product_id = '4247' ORDER BY IF(p.quantity > 0, 0, 1), sort_order ASC' in /home/progenix/public_html/system/storage/modification/system/library/db/mysqli.php:47 Stack trace: #0 /home/progenix/public_html/system/library/db.php(16): DB\MySQLi->query('SELECT * FROM o...', Array) #1 /home/progenix/public_html/system/storage/modification/catalog/model/catalog/product.php(641): DB->query('SELECT * FROM o...') #2 [internal function]: ModelCatalogProduct->getProductImages('4247') #3 /home/progenix/public_html/vqmod/vqcache/vq2-system_storage_modification_system_engine_loader.php(178): call_user_func_array(Array, Array) #4 [internal function]: Loader->{closure}(Array, Array) #5 /home/progenix/public_html/system/engine/proxy.php(25): call_user_func_array(Object(Closure), Array) #6 /home/progenix/public_html/vqmod/vqcache/vq2-system_storage_modification_catalog_c in /home/progenix/public_html/system/storage/modification/system/library/db/mysqli.php on line 47

I'm not a dev by any stretch of the imagination, but I'm pretty certain that it won't find p.quantity in the oc_product_image table :p

How on earth is the mod breaking everything so badly, considering how little code there actually is which makes absolutely no reference to any of the files or tables mentioned in the error, yet everything works perfectly the second I disable the mod?

I'm on OC 2.3.0.2, a slightly edited version of the default theme, and quite a few extensions (none of which I expect to be the issue, considering everything works without that basically single line ocmod.

What I HAVE noticed is that product.php doesn't contain so much as "this->db->query" so I imagine that's part of the issue?

$query = $this->db->query($sql);

straightlight wrote: ↑

Wed Jul 26, 2023 3:40 pm

You're tracking a single line of:

Code: Select all

$query = $this->db->query($sql);

whereas this model contains multiple lines of these lines. You must ensure that the field prefix still applies to the product table based on the line position you chose in order to pull the quantity field in this case. Also, see that the quantity field does physically still exist on your product table on your database.

ADD Creative wrote: ↑

Thu Jul 27, 2023 4:30 am

Make sure you have the correct file. There should be three occurrences in catalog/model/catalog/product.php.

I think your issue is that another extension is adding more and the modification will be adding code before all those lines as well. You could check by looking in system/storage/modification/catalog/model/catalog/product.php.

Maybe a better search would be.

Code: Select all

if (isset($data['order']) && ($data['order'] == 'DESC')) {

ADD Creative wrote: ↑

Thu Jul 27, 2023 4:23 pm

As a workaround you could try changing the part of the modification you posted to the following.