How to get randomly sorted recordsets in Strapi

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 for the frontend and Strapi 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:

{
  "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:

"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.

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

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 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 (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:

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:

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:

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 Edge2Edge Media on Unsplash