How can I implement Sorting in a Symfony 3 JSON API?

I received a question from site visitor Rees regarding sorting a Symfony 3 JSON API result set. Here's the question:

Hi Chris, I'm using Symfony 3.2 with FOS REST Bundle to expose a JSON API. This is working really well. I'm wondering, how can I allow an API consumer the ability to sort my results? Any help appreciated. Thanks, Rees.

It's a good question. And as ever with Symfony (or just programming in general), there are many ways to address the problem.

There is an existing course here on CodeReviewVideos where we covered Pagination, Filtering, and Sorting with Twig, a Symfony 3 JSON API, and then consuming this API in both Angular 1, and React.

In that course I used the KNP Paginator Library to achieve the pagination, filtering, and sorting outcome.

However, you might not need a third party library.

We can achieve sorting by making use of Doctrine's Query Builder.

To add this functionality to our own projects, there are two immediate challenges that we need to address:

• How to allow sorting, both ASCending, and DESCending
• How to allow multiple fields to be sortable

Let's jump right in to it.

Symfony Sort Code Example

For this example I am going to use a combination of Symfony 3.2, and Symfony 3.3 conventions. This is because Rees (the question asker) uses Symfony 3.2. I will explain improvements for Symfony 3.3 onwards where practical:

Here's our example controller:

    /**
     * Gets a collection
     *
     * @Annotations\Get(path="/widget")
     */
    public function cgetAction()
    {
        return $this->get('app.widget_repo')->findAll();
    }

At the most basic, when GETting a collection we simply return all results.

This quickly becomes unfeasible as our application grows, but to keep things as focused as possible I am going to overlook the larger issue around pagination.

In Symfony 3.3 onwards we can, and should inject the repository rather than using the $this->get('...') approach:

use AppBundle\Repository\WidgetRepository;

// ...

    /**
     * Gets a collection, Symfony 3.3 approach
     *
     * @Annotations\Get(path="/widget")
     */
    public function cgetAction(WidgetRepository $widgetRepo)
    {
        return $widgetRepo->findAll();
    }

Let's imagine that our Widget entities have some interesting fields:

• createdAt
• name
• position

Created At will be a \DateTime.

Name is going to be a string.

And position is an int.

There's no real point to these other than to illustrate how to sort on different types of data.

Let's start by ignoring the fact that we potentially want to sort on multiple data points at any given time, and focus only on sorting one field.

The immediate question is how should our URL structure be defined?

I'm going to go with the following:

https://mysite.com/widget?orderBy=createdAt

Cool, seems to fit the bill, right?

Well, sort of.

Which way is this ordering? Ascending, or Descending?

My brain says: Ascending is the default.

How then, do we order by descending?

I'm going with a trick I learned from Django Rest Framework: -createdAt.

Therefore:

• https://mysite.com/widget?orderBy=createdAt - sorts by createdAt ascending
• https://mysite.com/widget?orderBy=-createdAt - sorts by createdAt descending

Simple enough.

We now need to tell the cgetAction controller action about our new query parameter:

use AppBundle\Repository\WidgetRepository;

// ...

    /**
     * Gets a collection, Symfony 3.3 approach
     *
     * @Annotations\Get(path="/widget")
     *
     * @Annotations\QueryParam(name="orderBy", nullable=true, description="Ordering")
     *
     */
    public function cgetAction(WidgetRepository $widgetRepo)
    {
        return $widgetRepo->findAll($queryParameters);
    }

There's no extra use statement required as QueryParam falls under the same namespace as Get.

We may not want any ordering applied, so nullable=true means this param can be omitted from the requested URL without issue.

What we do need to do is pass this query parameter through to the repository to be used as part of the built query.

In order to get access to query parameters we need to use the ParamFetcher:

use AppBundle\Repository\WidgetRepository;
use FOS\RestBundle\Request\ParamFetcher;

// ...

    /**
     * Gets a collection, Symfony 3.3 approach
     *
     * @Annotations\Get(path="/widget")
     *
     * @Annotations\QueryParam(name="orderBy", nullable=true, description="Ordering")
     *
     */
    public function cgetAction(WidgetRepository $widgetRepo, ParamFetcher $paramFetcher)
    {
        $queryParameters = [];

        if ($paramFetcher->get('orderBy', true)) {
            $queryParameters['orderBy'] = $paramFetcher->get('orderBy', true);
        }

        return $widgetRepo->findAll($queryParameters);
    }

We start by declaring an empty array of $queryParameters.

We will always pass an array of $queryParameters to our findAll repository method.

If orderBy is part of the URL then we will grab whatever is sent in, and set it under the orderBy key on the array of $queryParameters.

Let's look at the widget repository:

// src/AppBundle/Entity/Repository/DoctrineWidgetRepository.php

    public function findAll(array $queryParameters = [])
    {
        /**
         * @var \Doctrine\ORM\QueryBuilder $qb
         */
        $qb = $this->getManager()->createQueryBuilder('w');

        $qb
            ->select('w')
            ->from('AppBundle\Entity\Widget', 'w')
        ;

        return $qb->getQuery();
    }

Please look at the full code repository for a more real-world implementation, as this is a shortened example.

At this point our result set is unsorted, but working:

// GET https://mysite.com/widget

[
    {
        "id": 1,
        "name": "A",
        "position": 1,
        "created_at": "2017-08-26T12:30:58+0000",
        "updated_at": "2017-08-26T12:30:58+0000"
    },
    {
        "id": 2,
        "name": "B",
        "position": 2,
        "created_at": "2017-08-25T12:30:58+0000",
        "updated_at": "2017-08-25T12:30:58+0000"
    },
    {
        "id": 3,
        "name": "C",
        "position": 3,
        "created_at": "2017-08-19T12:30:58+0000",
        "updated_at": "2017-08-19T12:30:58+0000"
    }
]

Using the Query Builder approach seems pointless here. DQL would be better. It's only when we need to programatically add to our query that the Query Builder truly shines.

In this case, that's exactly what we need to do.

// src/AppBundle/Entity/Repository/DoctrineWidgetRepository.php

    public function findAll(array $queryParameters = [])
    {
        /**
         * @var \Doctrine\ORM\QueryBuilder $qb
         */
        $qb = $this->getManager()->createQueryBuilder('w');

        $qb
            ->select('w')
            ->from('AppBundle\Entity\Widget', 'w')
        ;

        if (isset($queryParameters['orderBy'])) {

            $orderBy = $queryParameters['orderBy'];

            $direction = 0 === mb_strpos($orderBy, '-') ? 'DESC' : 'ASC';

            $qb
                ->addOrderBy('w.createdAt', $direction)
            ;
        }

        return $qb->getQuery()->getResult();
    }

Now we check if the $queryParameters['orderBy'] is set.

If it isn't, we ignore the whole block and carry on as before.

If it is then we do two things:

We look at the first character of the field we want to order by. If it is a - then we want to be sorting DESC, and if not, ASC.

Next we add this orderBy logic to the query builder.

With that, we have a simple but useful solution to ordering by a single field:

// GET https://mysite.com/widget?orderBy=createdAt

[
    {
        "id": 3,
        "name": "C",
        "position": 3,
        "created_at": "2017-08-19T12:30:58+0000",
        "updated_at": "2017-08-19T12:30:58+0000"
    },
    {
        "id": 2,
        "name": "B",
        "position": 2,
        "created_at": "2017-08-25T12:30:58+0000",
        "updated_at": "2017-08-25T12:30:58+0000"
    },
    {
        "id": 1,
        "name": "A",
        "position": 1,
        "created_at": "2017-08-26T12:30:58+0000",
        "updated_at": "2017-08-26T12:30:58+0000"
    }
]

// GET https://mysite.com/widget?orderBy=-createdAt

[
    {
        "id": 1,
        "name": "A",
        "position": 1,
        "created_at": "2017-08-26T12:30:58+0000",
        "updated_at": "2017-08-26T12:30:58+0000"
    },
    {
        "id": 2,
        "name": "B",
        "position": 2,
        "created_at": "2017-08-25T12:30:58+0000",
        "updated_at": "2017-08-25T12:30:58+0000"
    },
    {
        "id": 3,
        "name": "C",
        "position": 3,
        "created_at": "2017-08-19T12:30:58+0000",
        "updated_at": "2017-08-19T12:30:58+0000"
    }
]

Symfony Sort By Multiple Fields Example

Useful though sorting by a single field may be, almost inevitably your business rules will become more complex.

Once management see what can be done, you can usually see the cogs slowly begin to turn and requests such as "can you just..." and "could it also..." start flying our way. Oh my, what have we done?

There are two parts to getting multiple order-by figured out:

• Allowing multiple orderBy on the URL
• Adding multiple orderBy clauses to our Query Builder

Let's tackle the URL first.

Previously we had this:

• https://mysite.com/widget?orderBy=createdAt - sorts by createdAt ascending
• https://mysite.com/widget?orderBy=-createdAt - sorts by createdAt descending

Now, we want this:

https://mysite.com/widget?orderBy=createdAt&orderBy=name

This won't quite work. We've overridden orderBy.

What we need is an array of orderBy entries. And it just so happens that this is already catered for:

https://mysite.com/widget?orderBy[]=createdAt&orderBy[]=name

Perfect. Now orderBy comes to our controller action as an array.

We need to update our controller to work with this info:

use AppBundle\Repository\WidgetRepository;

// ...

    /**
     * Gets a collection, Symfony 3.3 approach
     *
     * @Annotations\Get(path="/widget")
     *
     * @Annotations\QueryParam(name="orderBy", map=true, nullable=true, description="Ordering")
     *
     */
    public function cgetAction(WidgetRepository $widgetRepo)
    {

Note here the insertion of map=true into the QueryParam annotation.

The rest of the logic can stay the same. That's nice.

Things do need to change inside our Widget Repo though:

    public function findAll(array $queryParameters = [])
    {
        /**
         * @var \Doctrine\ORM\QueryBuilder $qb
         */
        $qb = $this->getManager()->createQueryBuilder('w');

        $qb
            ->select('w')
            ->from('AppBundle\Entity\Widget', 'w')
        ;

        if (isset($queryParameters['orderBy'])) {

            foreach($queryParameters['orderBy'] as $orderBy) {

                $hasLeadingDash = 0 === mb_strpos($orderBy, '-');

                $direction = $hasLeadingDash ? 'DESC' : 'ASC';
                $usableOrderBy = $hasLeadingDash ? substr($orderBy, 1) : $orderBy;

                $qb
                    ->addOrderBy('w.'.$usableOrderBy, $direction)
                ;
            }
        }

        return $qb->getQuery();
    }

Things are starting to feel a little messy.

As we now expect an array for orderBy, we can loop over the various options to build up our query.

$hasLeadingDash = 0 === mb_strpos($orderBy, '-');

This does what you might expect: it checks the first character of the given $orderBy variable to see if it contains a dash, or not. The outcome here will either be true or false. [I usemb_string][5] as it is a less error prone version ofstrpos` where foreign languages are concerned.

We can then use this outcome to determine if we should be sorting ASC or DESC.

And also, the field name we orderBy is the field name on our entity. If the given orderBy starts with a -, e.g. -createdAt, then our addOrderBy call would use the field name w.-createdAt, which is obviously not the property name.

As such we need to sanitise the input first, which is what:

$usableOrderBy = $hasLeadingDash ? substr($orderBy, 1) : $orderBy;

is doing. It removes the leading dash, if there is one.

And this now works as required:

GET http://127.0.0.1:8000/app_dev.php/widget?orderBy[]=createdAt&orderBy[]=-position

[
    {
        "id": 3,
        "name": "C",
        "position": 3,
        "created_at": "2017-08-19T12:45:25+0000",
        "updated_at": "2017-08-19T12:45:25+0000"
    },
    {
        "id": 2,
        "name": "B",
        "position": 2,
        "created_at": "2017-08-25T12:45:25+0000",
        "updated_at": "2017-08-25T12:45:25+0000"
    },
    {
        "id": 5,
        "name": "E",
        "position": 4,
        "created_at": "2017-08-26T12:45:25+0000",
        "updated_at": "2017-08-26T12:45:25+0000"
    },
    {
        "id": 4,
        "name": "D",
        "position": 2,
        "created_at": "2017-08-26T12:45:25+0000",
        "updated_at": "2017-08-26T12:45:25+0000"
    },
    {
        "id": 1,
        "name": "A",
        "position": 1,
        "created_at": "2017-08-26T12:45:25+0000",
        "updated_at": "2017-08-26T12:45:25+0000"
    }
]

It might be wise to tidy up this implementation somewhat. We could get rid of the foreach loop and replace it with array_map. We might also want to check if the passed in orderBy value is allowable.

if (isset($queryParameters['orderBy'])) {

    // make sure we are working with an array of `orderBy`
    if (false === is_array($queryParameters['orderBy'])) {
        $queryParameters['orderBy'] = [$queryParameters['orderBy']];
    }

    array_map(
        // don't forget to use the query builder
        // as otherwise it would be outside your function scope
        function ($orderBy) use ($qb) {

            $allowableTerms = [
                "createdAt",
                "-createdAt",
                // maybe you don't want reverse name sort
                "name",
                "position",
                "-position"
            ];

            // no funny business
            if (false === in_array($orderBy, $allowableTerms, true)) {
                return false;
            }

            $hasLeadingDash = 0 === mb_strpos($orderBy, '-');

            $direction = $hasLeadingDash ? 'DESC' : 'ASC';
            $usableOrderBy = $hasLeadingDash ? substr($orderBy, 1) : $orderBy;

            $qb
                ->addOrderBy('w.'.$usableOrderBy, $direction)
            ;
        },
        // our array of `orderBy` query params to map over
        $queryParameters['orderBy']
    );
}

There's likely more you could do. It's a thin line between doing this yourself and taking ownership of this whole new problem space, versus adding yet another dependency to your project which likely does this small thing you need and 99 other things you don't. The choice is, as ever, up to you.