How to get randomly sorted recordsets in Strapi
Tags: strapi, headlesscms, javascript
Published: 2021-11-12 16:31:28

---
title: How to get randomly sorted recordsets in Strapi
published: true
description: 
tags: strapi,headlesscms,javascript
cover_image: https://images.unsplash.com/photo-1605870445919-838d190e8e1b?ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&ixlib=rb-1.2.1&auto=format&fit=crop&w=1172&q=80
---

Lately I had to build page that shows the details of a recordset, and at the bottom a section "Others" that shows two randomly picked recordsets that the user can click on to see their details. Of course in the "others" recordsets we shouldn't see the recordset the user is currently viewing.

This project stack is [Next.js](https://nextjs.org/) for the frontend and [Strapi](https://strapi.io/) for the backend. In this post, we will focus on the backend side and see how we can return random recordsets of a Strapi collection type.

You may think "wait, Strapi exposes an API with lots of parameters available, it should be possible to simply pass a param and this job is done". The thing is... there is no value that we can pass to the `_sort` parameter to sort randomly.

So, we will need to build a custom endpoint for our Partnerships collection type to get some randomly picked recordsets.

First, we need to add a route. Let's add it to `api/partnership/config/routes.json`:

```json
{
  "routes": [
    {
      "method": "GET",
      "path": "/partnerships/random",
      "handler": "partnership.random",
      "config": {
        "policies": []
      }
    }
  ]
}
```

Nice, now we can create the `random` method in the Partnership controller. Let's go in `api/partnership/controllers/partnership.js` and implement a dumb `random` method to see if we can reach it:

```js
"use strict";

module.exports = {
  async random() {
    return "Hello world"
  }
}
```

Then go to `http://localhost:1337/partnerships/random` in our browser... to see a HTTP 403 error. This is normal, by default Strapi endpoints are not reachable. We should go to Strapi's admin UI and check the `random` endpoint user the Partnership model in Settings > Role > Public.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/10lda88mztzpa7wvo6u7.png)

Save this settings and retry to reach the random endpoint. It now shows our Hello world :tada:.

We can now implement the endpoint.

First, we need to get all recordsets randomly sorted. To achieve this, we will need to build a query. Strapi is using [Bookshelf](https://bookshelfjs.org) as an ORM. So we can start by getting our Partnership model, so we can run a query on it. Inside the query, we get a [knex](https://knexjs.org/) (this is the query builder that Bookshelf uses under the hood) query builder instance. On this query builder instance, we can there ask to order recordsets randomly. Let's try this:

```js
async random() {
  const result = await strapi
    .query("partnership")
    .model.query((qb) => {
      qb.orderByRaw("RANDOM()")
    })
    .fetchAll()

  return result.toJSON()
}
```

Try to reach the `/partnerships/random` endpoint and see that we get all partnerships randomly sorted. This can do the trick if you just want to get all the recordsets. But in my case, I wanted to have to possibility to exclude some recordsets by their ID, and to limit the number of recordsets returned. Here is how I did it:

```js
async random({ query }) {
  const DEFAULT_LIMIT = 10
  const limit = query._limit || DEFAULT_LIMIT
  const excludedIds = query.id_nin || []

  const result = await strapi
    .query("partnership")
    .model.query((qb) => {
      qb
        .whereNotIn("id", excludedIds)
        .orderByRaw("RANDOM()")
        .limit(limit)
    })
    .fetchAll()

  return result.toJSON()
}
```

This way I can get 2 random partnerships and I will never have the partnership with the ID `1` in the returned recordsets by doing:

```js
const url = new URL("http://localhost:1337/partnerships/random")
url.search = new URLSearchParams({
  "id_nin[]": [1],
  _limit: 2
}).toString()

const response = await fetch(url)
const data = await response.json()
```

Hope it helps!

Cover photo by <a href="https://unsplash.com/@edge2edgemedia?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Edge2Edge Media</a> on <a href="https://unsplash.com/s/photos/random?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Unsplash</a>