Postgres: Sort query results¶
Table of contents
The order_by argument¶
Results from your query can be sorted by using the order_by argument. The argument can be used to sort nested
objects too.
The sort order (ascending vs. descending) is set by specifying the asc or desc
enum value for the column name in the order_by input object, e.g. {name: desc}.
By default, for ascending ordering null values are returned at the end of the results and for descending
ordering null values are returned at the start of the results. null values can be fetched first on
ascending ordering by specifying asc_nulls_first and last on descending ordering by specifying
desc_nulls_last enum value e.g. {name: desc_nulls_last}.
The order_by argument takes an array of objects to allow sorting by multiple columns.
You can also use nested objects’ fields to sort the results. Only columns from object relationships and aggregates from array relationships can be used for sorting.
You can see the complete specification of the order_by argument in the API reference.
The following are example queries for different sorting use cases:
Sorting objects¶
Example: Fetch a list of authors sorted by their names in an ascending order:
query {
authors (
order_by: {name: asc}
) {
id
name
}
}
Sorting nested objects¶
Example: Fetch a list of authors sorted by their names with a list of their articles that is sorted by their rating:
query {
authors (order_by: {name: asc}) {
id
name
articles(order_by: {rating: desc}) {
id
title
rating
}
}
}
Sorting based on nested object’s fields¶
Only columns from object relationships and aggregates from array relationships can be used for sorting.
For object relationships¶
For object relationships only columns can be used for sorting.
Example: Fetch a list of articles that are sorted by their author’s ids in descending order:
query {
articles (
order_by: {author: {id: desc}}
) {
id
rating
published_on
author {
id
name
}
}
}
For array relationships¶
For array relationships only aggregates can be used for sorting.
Example: Fetch a list of authors sorted in descending order of their article count:
query {
authors (
order_by: {
articles_aggregate: {count: desc}
}
) {
id
name
articles_aggregate {
aggregate{
count
}
}
}
}
Example: Fetch a list of authors sorted in increasing order of their highest article rating:
query {
authors(
order_by: {
articles_aggregate: {
max: {rating: asc_nulls_last}
}
}
) {
id
name
articles_aggregate {
aggregate{
max {rating}
}
}
}
}
Sorting based on computed fields¶
Only scalar computed fields and aggregates of table computed fields can be used for sorting.
For scalar computed fields¶
Scalar computed fields can be used for sorting just like columns.
Example: Computed field total_marks is defined on student table which calculates
the sum of marks obtained in each subject. Fetch a list of students sorted by their total marks:
query {
student(order_by: {total_marks: desc}){
id
name
total_marks
physics
chemistry
maths
}
}
For table computed fields¶
Aggregates of table being returned by table computed fields can be used for sorting.
Example: Computed field get_articles is defined to author table returns list of articles.
Fetch a list of authors sorted by their articles count.
query{
author(order_by: {get_articles_aggregate: {count: desc}}){
id
name
get_articles{
id
title
content
}
}
}
Note
Only computed fields whose associated SQL function with no input arguments other than table row and hasura session arguments are supported in order by.
Supported from
Ordering by computed fields is supported from version v2.0.2 and above
Sorting by multiple fields¶
Example: Fetch a list of articles that is sorted by their rating (descending) and then on their published date (ascending with nulls first):
query {
articles (
order_by: [
{rating: desc},
{published_on: asc_nulls_first}
]
) {
id
rating
published_on
}
}
Thank you for subscribing to our newsletter!
