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 GET
ting 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 bycreatedAt
ascendinghttps://mysite.com/widget?orderBy=-createdAt
- sorts bycreatedAt
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 bycreatedAt
ascendinghttps://mysite.com/widget?orderBy=-createdAt
- sorts bycreatedAt
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 use
mb_string][5] as it is a less error prone version of
strpos` 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.