False order when Float Number

I am trying to sort products in correct order in Checkout cart ; while It is correct for different products but when the case for products having this form

'BrandName' ProductName 1,25 kg

I got flase order like this

'BrandName' ProductName 1,25 kg
'BrandName' ProductName 10,0 kg
'BrandName' ProductName 15,0 kg
'BrandName' ProductName 2,5  kg

I want to correct the order like this

 'BrandName' ProductName 1,25 kg
 'BrandName' ProductName 2,5  kg
 'BrandName' ProductName 10,0 kg
 'BrandName' ProductName 15,0 kg

without affecting the order of The other products sorted Alphabetically. This is a snippet of code

$this->_items->getSelect()->where('catalog_product_index_eav.store_id=' . $storeId . ' AND catalog_product_index_eav.attribute_id=' . $attId);
                $this->_items->getSelect()->order('CONCAT(eav_attribute_option_value.value, main_table.name) ASC');
                $this->_items->getSelect()->order('main_table.sku ASC');
                $this->_items->getSelect()->group('main_table.item_id');

Don’t hesitate if you need more details.

Thanks in advance,

What you’ve got is Lexicographical Order.

Basically, it’s treating your numbers as strings. “a” comes before “b”, “apple” comes before “banana” because “a” comes before “b”.
“1” comes before “2”, so “10” comes before “2” as well.

If you want Numerical Order, you’ll have to convert your strings into numbers.

Thanks but the problem I have to order both those with numerical values and thoses not
More detailed example
I need to have this result
Brand 1 ‘AProduct Name 1’
Brand 1 ‘BProduct Name 2’
Brand 1 ‘CProduct Name 3 1,25 kg’ (It contains 1,25 the name)
Brand 1 ‘CProduct Name 3 2,5 kg’
Brand 1 ‘CProduct Name 3 5,0 kg’

If that whole thing is part of a single string that isnt part of that CONCAT (I don’t know your database structure, so I dont know what that’s concating)… I dont know how you’d be able to sort by it numerically; it would be extremely complex for a SQL-like to handle that.

A kludge would be to add the weight to an array element then use PHP natsort(…). Notice how the commas were replaced with periods:

<?php declare(strict_types=1);
error_reporting(-1);
ini_set('display_errors', '1');

$xxx = [
100 * 1.25 . '^ BrandName ProductName 1,25 kg',
100 * 10.4 . '^ BrandName ProductName 10,4 kg',
100 * 10.0 . '^ BrandName ProductName 10,0 kg',
100 * 10.8 . '^ BrandName ProductName 10,80 kg',
100 * 10.6 . '^ BrandName ProductName 10,6 kg',
100 * 15.0 . '^ BrandName ProductName 15,0 kg',
100 * 2.50 . '^ BrandName ProductName 2,5  kg',
];

fred($xxx);

natsort($xxx);
fred($xxx);

//======================================
function fred($val)
{
  echo '<br><pre>';
    if(0) :
      print_r($val);
    else:  
      foreach($val as $key => $item) :
        $tmp = strstr($item, '^', false);
        $tmp = substr($tmp, 2);
        echo '<br>' .$key .' ==> ' .$tmp;
      endforeach;  
    endif;  
  echo '<pre><br>';
}

Output


0 ==> BrandName ProductName 1,25 kg
1 ==> BrandName ProductName 10,4 kg
2 ==> BrandName ProductName 10,0 kg
3 ==> BrandName ProductName 10,80 kg
4 ==> BrandName ProductName 10,6 kg
5 ==> BrandName ProductName 15,0 kg
6 ==> BrandName ProductName 2,5  kg




0 ==> BrandName ProductName 1,25 kg
6 ==> BrandName ProductName 2,5  kg
2 ==> BrandName ProductName 10,0 kg
1 ==> BrandName ProductName 10,4 kg
4 ==> BrandName ProductName 10,6 kg
3 ==> BrandName ProductName 10,80 kg
5 ==> BrandName ProductName 15,0 kg

Though you’ve highlighted a potential danger there already - a numeric array key must be a whole number.

If you know all the values ahead of time, yes, this works, but if you’re going to make an array of the options, why are you using a database of items in the first place?

You might be able to map the values through a regex, but you’d have to be sure all the values are using the same measures and structure…

There’s always another kludge available :slight_smile:

Previous post edited to show modifications.

The values were multiplied by 100 to make then integers and the caret introduce to use strstr(…); to ignore the prefixes.

And always another ‘but’ :wink:

(The but being “as long as your values are all in less than 1-100th terms”…)

And again, if you know all the values in advance, this is trivial. if you dont though…
"Regex for (\d*(,\d*)?" … okay, as long as none of your product names include numbers.
"You could regex for (\d*(,\d*)? kg)" Sure, if all of the values you’re sorting by are “something kg”.
… etc etc.

If you can guarantee the data form, we can bend over backwards to sort something out.

Or you could… yaknow… put the numbers into a separate field in the table and just sort by those like a sane person.

1 Like

That’s the option I would prefer and would make order by id so much easier without any extra script.

OP, How EXACTLY is the the source data stored?

1 Like

I appreciate your manner of thought that is in algorithmic way very clever . But keep in mind that we don’t know the numbers and that it is dynamic

Mysql

I think @benanamen was asking about your database table structure.

2 Likes

As others have mentioned already, you need to use natural sorting if the strings are like you mentioned.

$data = [
    "'BrandName' ProductName 1,25 kg",
    "'BrandName' ProductName 10,0 kg",
    "'BrandName' ProductName 15,0 kg",
    "'BrandName' ProductName 2,5  kg",
    "Brand 1 ‘BProduct Name 2’",
    "Brand 2 ‘AProduct Name 1’",
    "Brand 1 ‘CProduct Name 3 5,0 kg’",
    "Brand 1 ‘CProduct Name 3 2,5 kg’",
    "Brand 1 ‘CProduct Name 3 1,25 kg’ (It contains 1,25 the name)",
];

natsort($data);

var_dump($data);

This returns:

array (size=9)
  0 => string ''BrandName' ProductName 1,25 kg' (length=31)
  3 => string ''BrandName' ProductName 2,5  kg' (length=31)
  1 => string ''BrandName' ProductName 10,0 kg' (length=31)
  2 => string ''BrandName' ProductName 15,0 kg' (length=31)
  4 => string 'Brand 1 ‘BProduct Name 2’' (length=29)
  8 => string 'Brand 1 ‘CProduct Name 3 1,25 kg’ (It contains 1,25 the name)' (length=65)
  7 => string 'Brand 1 ‘CProduct Name 3 2,5 kg’' (length=36)
  6 => string 'Brand 1 ‘CProduct Name 3 5,0 kg’' (length=36)
  5 => string 'Brand 2 ‘AProduct Name 1’' (length=29)

If you want to sort the result set from the database, take a look on the answers in this stackoverflow post:

Yeah, what @droopsnoot said.

Until we know how the data is stored it is pointless to offer “solutions”.

OP, Please provide an sql dump of your DB schema with a few sample records.