[QA] Order string as numeric value – how to?

[trogwarz]

What is best way to sort by decimal value?

For example, i need to do this request GET api.example.com/orders.jsonld?order[price]=desc and it returns prices in this order: 1, 2, 21, 22, 23, 3, 4, 47, 5, ...

My prices stored as strings (because i use many operations with them as money). But i want to sort them as numbers.

Any suggestions?

[soyuka]

imo this would need a custom query change, what's your dbms?

[trogwarz]

@soyuka, i use MariaDB 10

[soyuka]

why don't you use the DECIMAL type it's almost built to store prices ^^. Anyway you could alter the query with a CAST: ORDER BY CAST(price as DECIMAL) but I'd not advise to do so. If you do so you should also add a function index on that but the easiest would be to just use the DECIMAL type.

[trogwarz]

why don't you use the DECIMAL type it's almost built to store prices

Because i need FLOATing point values – in project exists many different currencies (regular ones and crypto too) that have different precision.
String and bcmath for money operations are working and because of that all moneys are represented as DTO (simple wrapper around string) in code and as strings in DB.

When i switch to DECIMAL, there are many problems appearing: need conversion every time between float/string in code before using bcmath; some currencies with scale as 10 numbers are too low (like btc) for PHP and i need to patch apiplatform to cast it into string without scientific form; my unit tests are failing because of "1/3 floating problem"; and so on.

Anyway you could alter the query with a CAST: ORDER BY CAST(price as DECIMAL)

@soyuka, casting in mysql order query seems enough. At least, i can run load tests and profile what happens (-: Can you show me how/where to configure/patch apiplatform to do so only for one fiend of one resource?

[soyuka]

You may create your own Filter (see https://api-platform.com/docs/core/filters/#creating-custom-filters) by taking example on https://github.com/api-platform/core/blob/master/src/Bridge/Doctrine/Orm/Filter/OrderFilter.php

[ghost]

@trogwarz Did you find a solution ? Can you share it with us ?

[trogwarz]

@OlivierFromLille, well.. workarouding this with string/cast/customfilter-solution took too much time and raised complexity too high (or i don't have simple idea how to do it).

So.. i gave up and made two decisions:

1. String masking – so strings like "00042.3700" and "01142.9532" ordered correctly anyways and i can use bcmath in my codebase. Just done in Entity setter Order::setPrice.
2. Optional. Event subscriber attached to POST_SERIALIZE event to convert strings into floats for non-BC changes in frontend. floatval was enough for me.

In general, it fixes problem and solution is simple in code support. But.. feels ugly (-:

[ghost]

Thanks @trogwarz
Finally, I did a solution like this to sort by version (here I'll always have only one dot, and values 1.1 and 1.10 are considered different). It may be ugly too but it works :)

In a custom filter :

• Take the part after the dot as a whole integer
• Take the part before the dot as an integer and multiply it by 10 ** length of the max characters in the "version" column.
• Add the 2 results and I sort on that

So, here an example of "extreme" sorted versions (in parenthesis, the calcul to show how it looks) :

1. 0.1 (equals 1)
2. 0.2 (equals 2)
3. 0.10 (equals 10)
4. 1.0 (equals 100.000.000)
5. 1.9 (equals 100.000.009)
6. 1.900 (equals 100.000.900)
7. 1.9000 (equals 100.009.000)
8. 1.200000(equals 100.200.000)
9. 20.0 (equals 2.000.000.000)

`<?php
namespace App\Filter\File;

use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\AbstractContextAwareFilter;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use Doctrine\Common\Persistence\ManagerRegistry;
use Doctrine\ORM\QueryBuilder;
use Psr\Log\LoggerInterface;

/**

• Class SortFilter.
  */
  final class SortFilter extends AbstractContextAwareFilter
  {
  const ORDER = 'order';
  const VERSION = 'version';

  /**

  • TypeFilter constructor.
  • @param ManagerRegistry $managerRegistry
  • @param LoggerInterface|null $logger
  • @param array|null $properties
    */
    public function __construct(ManagerRegistry $managerRegistry, LoggerInterface $logger = null, array $properties = null)
    {
    parent::__construct($managerRegistry, null, $logger, $properties);
    }

  /**

  • @param string $property

  • @param $value

  • @param QueryBuilder $queryBuilder

  • @param QueryNameGeneratorInterface $queryNameGenerator

  • @param string $resourceClass

  • @param string|null $operationName

  • @throws \Doctrine\ORM\NonUniqueResultException
    */
    protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null)
    {
    // otherwise filter is applied to order and page as well
    if ((!$this->isPropertyEnabled($property, $resourceClass) || !$this->isPropertyMapped($property, $resourceClass))
    && self::ORDER !== $property) {
    return;
    }

    foreach ($value as $index => $val) {
    if (self::VERSION === $index) {
    $em = $queryBuilder->getEntityManager();
    $maxLength = 10 ** $em->createQuery("SELECT max(length(rf.version)) FROM App\Entity\File\AbstractRevisableFile rf")->getSingleScalarResult();
    $queryBuilder->addSelect(sprintf("SUBSTRING_INDEX(%s.version, '.', 1) * $maxLength + SUBSTRING_INDEX(%s.version, '.', -1) AS HIDDEN sort_version", $queryBuilder->getRootAliases()[0], $queryBuilder->getRootAliases()[0]))
    ->orderBy("sort_version", $val);
    }
    }
    }
    }
    `

Note that SUBSTRING_INDEX is not in doctrine by default so I used https://github.com/beberlei/DoctrineExtensions

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.