How to override default ordering with an order filter

[tezvi]

Hi everyone!

I've added default ordering to resource attributes (yaml). So far so good.
However, the default order is still applied when querying the collection with an explicit order filter (query param).

The query builder used by data provider orders by default and by order filter.

Is there a way to ditch default order when order filter has been activated?

Also is it possible to define default order at operation level?

[teohhanhui]

The default order is used to ensure a stable ordering of items, as otherwise the order is undefined as per the SQL standard. I don't see any case where you should have a need to remove the default order when using custom ordering.

[teohhanhui]

Also is it possible to define default order at operation level?

It does not seem to be supported at this time: https://github.com/api-platform/core/blob/v2.4.2/src/Bridge/Doctrine/Orm/Extension/OrderExtension.php#L54

But indeed we could. Actually we shouldn't, as it makes no sense.

[tezvi]

The default order is used to ensure a stable ordering of items, as otherwise the order is undefined as per the SQL standard. I don't see any case where you should have a need to remove the default order when using custom ordering.

Let me put it this way. The user should be able to order collection by using a specific order filter. If no filter is used the collection should be ordered by default order.
It seems I cannot recreate that with the current implementation.

[teohhanhui]

The user should be able to order collection by using a specific order filter. If no filter is used the collection should be ordered by default order.

Sorry, I don't understand. The default order is exactly what that's for? I've already explained above why the default order is always added to the end. You should choose something that is definitely able to order items to avoid running into undefined ordering.

To give you an example, if the user chooses to order by name, what happens when 2 items have the same name? That's the job of the default order (say, order by id), to make sure that the order is deterministic.

[teohhanhui]

In fact, the default order is not used by the OrderFilter (which is applied by the FilterExtension), but by the OrderExtension. Perhaps that's the source of your confusion. It should probably be renamed the DefaultOrderExtension (default_order in metadata attributes). 😄

[teohhanhui]

It should probably be renamed the DefaultOrderExtension (default_order in metadata attributes).

Keeping open for this.

[tezvi]

Hi teohhanhui,

thanks for your input and clarification. I understand the difference between OrderExtension and FilterExtension. However, I'm not really sure if we have an understanding. Let's assume we have a simple entity, just for the sake of example.

/**
 * @ORM\Table(name="bar")
 * @ORM\Entity
 */
class Bar
{
     /**
     * @var integer
     *
     * @ORM\Id
     * @ORM\Column(name="id", type="integer")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="code", type="string", length=10)
     */
    private $code;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=100)
     */
    private $name;

    /**
     * @var string
     *
     * @ORM\Column(name="description", type="string", length=100)
     */
    private $description;
}

ApiPlatform resource definition:

Bar:
    collectionOperations:
        get:
            path: '/api/bars'
            filters:
                - 'api_filter.bar.order'
    itemOperations:
        get:
            path: '/api/bars/{id}'
    attributes:
        order:
            code: asc
            id: desc

Filter service

api_filter.bar.order:
    parent: api_platform.doctrine.orm.order_filter
    arguments:
       properties: 
          id: ~
          code: ~
          name: ~
          description: ~

when querying /api/bars API endpoint the api_platform data provider would generate something like this (approximation, writing from top of my head)

SELECT * FROM bars ORDER BY code ASC, id DESC

When querying /api/bars?order[name]=asc&order[description]=desc the query would look like this (not really sure about order of ORDER clause identifiers):

SELECT * FROM bars ORDER BY code ASC, id DESC, name ASC, description DESC
or
SELECT * FROM bars ORDER BY name ASC, description DESC, code ASC, id DESC

Either way, default order is unnecessary applied. For huge tables this would present an overhead at database level and eventually wrong ordering.

Sorry if I make this unnecessary difficult or if I don't see a solution out-of-the-box.

A potential solution that does not touch any api-platform implementation is to decorate OrderExtension and detect if order filter is being activated, before or after the OrderFilter is processed.

[tezvi]

If anyone stumbles upon the same problem this gist has the solution mentioned in previous post.
https://gist.github.com/tezvi/d0974664a423a3052518bfb47bac672c

[wibimaster]

Same problem here, I write a filter that add a custom "distinct" capatibility, managable by HTTP GET queries ; but when we use the "distinct" method of queryBuilder, "orderBy" have to follow same list and same order as distinct properties.
The default order on identifier is a pain for this specific case, and we should assume to remove it when needed :)

EDIT
It works with @tezvi solution 👍

[shehi]

I have the same problem with Doctrine ODM. Adds ordering by _id = ASC into the same stage as my own ordering rule and messes up the aggregation result. I know what MongoDb docs say, but I am not getting the results I should be getting... In my case, I first have a $lookup stage where I alias the referenced document, then I $sort by a field within that alias:

// inside my custom data provider
$aggregationBuilder->lookup(Patient::class)->localField('participant.actor.reference')->foreignField('_id')->alias('patient');
// then inside my custom orderfilter (which is called in my data provider via extensions):
$aggregationBuilder->sort('patient.name.family', $value['patient']['name']);

[shehi]

Correction from my side: there is no need to disable default ordering. If you check OrderFilter class you will see guys here update context: https://github.com/api-platform/core/blob/main/src/Bridge/Doctrine/MongoDbOdm/Filter/OrderFilter.php#L104 . Doing so fixed my issues.