Products show up even if Include in Latest Products is not checked

[lexijade]

All of my products show up whether or not the "Include in latest products" box is checked. Checking the box does nothing. My guess is I messed up the coding for that at some point, but have no idea how or where to look to fix it. But it would be really nice to be able to actually pick what shows up in that list and not just the 4 most recent products created.

In a related question, is there a way to have it show products that were recently updated vs just created. For example if I update a product and have it selected as "Include in latest products", can I make that item show up in latest even if it wasn't just created?

[bsmither]

In CubeCart's Store Settings, Advanced tab, enable Debug mode and enter your local IP address in the next field (www.showmyip.com). Save.

View the storefront.

Below the page of content, there will be a debug section showing all the database queries made. Find the query that looks like the following (line split apart for clarity):

SELECT
  I.* 
  FROM `CubeCart_inventory` AS I 
  JOIN `CubeCart_category` AS C 
  ON C.cat_id=I.cat_id 

AND C.`status`=1 
AND I.status = '1' 
AND I.latest = '1' 
AND ((I.stock_level > 0 AND I.use_stock_level = 1) OR I.use_stock_level = 0)

ORDER BY I.date_added DESC,
I.product_id DESC
LIMIT 100

where the LIMIT is set in Store Settings, Layout tab, "Number of 'Latest Products' to display" setting.

This query is constructed in the file /classes/cubecart.class.php, near line 100.

So, the query does use the value in the CubeCart_inventory, 'latest' column.

The result of the query is cached, so be sure that after changes made in admin, that CubeCart's cache is cleared.

A hard edit to the query, from I.data_added to I.updated will order the result by the date the product was last Saved in admin.

[havenswift-hosting]

@lexijade take a look at this plugin which allows you to specifically choose which products are shown in the Latest Products section and in which order.  It has a load of other very useful functionality as well !

https://www.cubecart.com/extensions/plugins/enhanced-sorting

[lexijade]

On 1/11/2023 at 11:13 PM, bsmither said:

In CubeCart's Store Settings, Advanced tab, enable Debug mode and enter your local IP address in the next field (www.showmyip.com). Save.

View the storefront.

Below the page of content, there will be a debug section showing all the database queries made. Find the query that looks like the following (line split apart for clarity):

SELECT
  I.* 
  FROM `CubeCart_inventory` AS I 
  JOIN `CubeCart_category` AS C 
  ON C.cat_id=I.cat_id 

AND C.`status`=1 
AND I.status = '1' 
AND I.latest = '1' 
AND ((I.stock_level > 0 AND I.use_stock_level = 1) OR I.use_stock_level = 0)

ORDER BY I.date_added DESC,
I.product_id DESC
LIMIT 100

where the LIMIT is set in Store Settings, Layout tab, "Number of 'Latest Products' to display" setting.

This query is constructed in the file /classes/cubecart.class.php, near line 100.

So, the query does use the value in the CubeCart_inventory, 'latest' column.

The result of the query is cached, so be sure that after changes made in admin, that CubeCart's cache is cleared.

A hard edit to the query, from I.data_added to I.updated will order the result by the date the product was last Saved in admin.

I don't understand what I'm looking for in the debug and once I find that, how it helps me fix it.

My limit is set to 4. That part is working correctly.

[bsmither]

You are looking for that query, seen as all on one line, to make sure that AND I.latest = '1' is part of that query. If not, but the rest of the query is as I have shown above, then we can probably find where the code got changed to not have that condition. The LIMIT 4 will also be there instead of LIMIT 100.

 

 

[lexijade]

SELECT I.* FROM `CubeCart_inventory` AS I JOIN `CubeCart_category` AS C ON C.cat_id=I.cat_id AND C.`status`=1 AND I.status = '1' ORDER BY I.date_added DESC, I.product_id DESC LIMIT 4

That's the line I get on the debug, so clearly that's part of the issue since it's missing the latest part.

I noticed a new problem, which may also be a factor. When I check the "include in latest products" box it doesn't save that when I update the product. I can make other changes at the same time and it'll save those, but it will revert back to unchecked for the latest product box.

and this is Line 95-104 in cubecart.class.php

 

        $products = array();

        $where = $GLOBALS['catalogue']->outOfStockWhere(array('I.status' => '1', 'I.latest' => '1'), 'I');

        if ($GLOBALS['config']->get('config', 'catalogue_latest_products')) {
            $query = sprintf("SELECT I.* FROM `%1\$sCubeCart_inventory` AS I JOIN `%1\$sCubeCart_category` AS C ON C.cat_id=I.cat_id AND C.`status`=1 AND $where ORDER BY I.date_added DESC, I.product_id DESC", $GLOBALS['config']->get('config', 'dbprefix'));
            $latestProducts = $GLOBALS['db']->query($query, (int)$GLOBALS['config']->get('config', 'catalogue_latest_products_count'));
            foreach ($GLOBALS['hooks']->load('class.cubecart.latest_products') as $hook) {
                include $hook;
            }

 

[bsmither]

Good.

Now, using an external database utility, such as phpMyAdmin found in your hosting control panel, look at the database table CubeCart_inventory. Make sure that table has a column named 'latest'.

 

[lexijade]

It does not. How do I add that?

[bsmither]

Enter these commands in phpMyAdmin (or whatever you use):

ALTER TABLE `CubeCart_inventory` CHANGE COLUMN `featured` `featured` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Featured product'; #EOQ
ALTER TABLE `CubeCart_inventory` ADD COLUMN `latest` tinyint(1) unsigned NOT NULL DEFAULT 1 COMMENT 'Included on Homepage' AFTER `featured`; #EOQ
UPDATE `CubeCart_inventory` SET `latest`=`featured`; #EOQ

Be sure to prepend the table's prefix if there is a prefix being used.

This feature was added in CC610, so, if your first installation of CubeCart was before that version, then an upgrade may have missed it. Unfortunately, the upgrade script setup/db/upgrade/6.1.0.sql has been changed several times, after CC610 was released.

[lexijade]

That worked! Thank you so much!