Forum

Thread tagged as: Problem, Error, Shop

Sorting products by 'Price' with Shop returns disordered results

When I run this in my database - the debug output for the product listing page: https://staging.uxusd.com/original-paintings.php?page=1 - the results contain anomalous product results. On about 40 products there are three low prices mixed in with the higher prices. (Sort: 'Price High - Low' is selected)

SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch3_shop_index idx JOIN perch3_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch3_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='price' AND idx.itemID IN ('1', '3', '5', '4', '6', '7', '2', '66', '82', '92', '21', '22', '26', '29', '25', '65', '23', '10', '78', '61', '79', '64', '46', '76', '47', '9', '50', '51', '67', '95', '94', '100', '24', '31', '87', '33', '102', '91', '89', '41', '101', '27', '8', '93', '48', '49', '90', '83', '39') WHERE 1=1 AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey GROUP BY idx.itemID, idx2.indexValue, productID ) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval ORDER BY sortval DESC LIMIT 0, 6

Returns: £99 £99 £450 £425 £375 £375

The other prices excluding anomalies are all in order.
Fred Stidston

Fred Stidston 0 points

  • 3 years ago
Hussein Al Hammad

Hussein Al Hammad 105 points
Registered Developer

3 years ago

Hi Fred,

Can you show us your code? Are you setting the sort-type to numeric?
Fred Stidston

Fred Stidston 0 points

3 years ago

Hi Hussein, I was using

if ($sort == 'name-az') {
                        $field = "title";
                        $order = "ASC";
                    } else if ($sort == 'name-za') {
                        $field = "title";
                        $order = "DESC";
                    } else if ($sort == 'date-incr') {
                        $field = "SKU";
                        $order = "ASC";
                    } else if ($sort == 'date-decr') {
                        $field = "SKU";
                        $order = "DESC";
                    } else if ($sort == 'price-decr') {
                        $field = "price";
                        $order = "DESC";
                    } else if ($sort == 'price-incr') {
                        $field = "price";
                        $order = "ASC";
                    }

...
                    $args = [
                        'template' => 'products/result.html',
                        'count' => $perPage,
                        'start' => $start,
                        'sort' => $field,
                        'sort-order' => $order,
                        'category' => 'products/'.$slug.'/'
                    ];
Hussein Al Hammad

Hussein Al Hammad 105 points
Registered Developer

3 years ago

Ok. You need to use 'sort-type' => 'numeric' when sorting by the price.
Fred Stidston

Fred Stidston 0 points

3 years ago

Sort-type is the ticket!

Thank you