Sorting ​

See prisma reference

Use orderBy to sort a list of records or a nested list of records by a particular field or set of fields. For example, the following query returns all User records sorted by role and name, and each user's posts sorted by title:

const usersWithPosts = await app.service('User').findMany({
  orderBy: [
    {
      role: 'desc',
    },
    {
      name: 'desc',
    },
  ],
  include: {
    posts: {
      orderBy: {
        title: 'desc',
      },
      select: {
        title: true,
      },
    },
  },
})

Note: You can also sort lists of nested records to retrieve a single record by ID.

Sort by relation ​

You can also sort by properties of a relation. For example, the following query sorts all posts by the author's email address:

const posts = await app.service('Post').findMany({
  orderBy: {
    author: {
      email: 'asc',
    },
  },
})

Sort by relation aggregate value ​

In 2.19.0 and later, you can sort by the count of related records.

For example, the following query sorts users by the number of related posts:

const getActiveUsers = await app.service('User').findMany({
  take: 10,
  orderBy: {
    posts: {
      _count: 'desc',
    },
  },
})

Note: It is not currently possible to return the count of a relation.

Sort by relevance (PostgreSQL) ​

When using PostgreSQL you can sort records by relevance to the query using the _relevance keyword. This uses the relevance ranking functions from PostgreSQL's full text search feature, which are explained further in the PostgreSQL documentation.

Enable order by relevance with the fullTextSearch preview feature:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch"]
}

Ordering by relevance can be used either separately from or together with the search filter: _relevance is used to order the list, while search filters the unordered list.

For example, the following query uses _relevance to order the list of users by relevance to the search term 'developer' in their bio, and search to filter the list:

const getUsersByRelevance = await app.service('User').findMany({
  take: 10,
  orderBy: {
    _relevance: {
      fields: ['bio'],
      search: 'developer',
      sort: 'asc',
    },
  },
})

Sort with null records first or last ​

Note: this feature is not supported for MongoDB.

You can sort the results so that records with null fields appear either first or last.

To use this feature, in the generator block of your schema.prisma file, enable the orderByNulls preview feature:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["orderByNulls"]
}

Note: You can only sort by nulls on optional scalar fields. If you try to sort by nulls on a required or relation field, Prisma throws a P2009 error.

Example: If updatedAt is an optional field, then the following query sorts posts by updatedAt, with null records at the end:

const posts = await app.service('Post').findMany({
  orderBy: {
    updatedAt: { sort: 'asc', nulls: 'last' },
  },
})