False order when Float Number

ahmedchouihi · April 22, 2020, 2:46pm

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,

m_hutley · April 22, 2020, 2:55pm

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.

ahmedchouihi · April 22, 2020, 3:01pm

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’

m_hutley · April 22, 2020, 3:19pm

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.

John_Betong · April 22, 2020, 4:05pm

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

m_hutley · April 22, 2020, 4:09pm

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…

John_Betong · April 22, 2020, 4:28pm

There’s always another kludge available

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.

m_hutley · April 22, 2020, 4:43pm

And always another ‘but’

(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.

John_Betong · April 22, 2020, 4:51pm

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

benanamen · April 22, 2020, 5:07pm

OP, How EXACTLY is the the source data stored?

ahmedchouihi · April 23, 2020, 9:17am

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

ahmedchouihi · April 23, 2020, 9:17am

Mysql

droopsnoot · April 23, 2020, 10:54am

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

TheRedDevil · April 23, 2020, 3:13pm

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:

benanamen · April 23, 2020, 3:21pm

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.

system · July 24, 2020, 12:19pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.