REST API: retrieve id only ids of relations

Questions and Answers Strapi Backend
1
System Information
  • Strapi Version: 3.5.x
  • Operating System: Linux
  • Database: Postgres
  • Node Version: 14.x
  • NPM Version:
  • Yarn Version:

Hello, Is there a way to retrieve id of relations (many-to-many and one-to-many) without populating them without using graphql?

My problem is: I have a lot of products that are related to many categories.
I have only 10 categories, so they are all fetched once and stored in my app.
When I fetch product, I don’t want Strapi to make a join query to populate the categories (for performances issues), but I need those IDs.

If I query directly my /products endpoints, categories are populated by default
But, If I make a relation on a single type with those products, categories are not populated (neither ids …)

Edit: I saw that there is another table that is called products_categories__categories_products but how we can query it ?

1 Like
2

Take a look here:

3

Hum, unfortunately this does not solve my problem: doing so will make a join query to retrieve only IDs which is overkill because we already have them (IDs are needed for making the join query)

4

you can add "autoPopulate": false, to categories attribute in the

model.settings.json

file and it will give you just ids of categories. I use two way relationship for this and strapi version is 3.6.0

5

"autoPopulate": false removes the attribute entirely, but what is needed is the list of id’s.
For example, the correct output should be - "someAttributeName": [1,2,3,4,5]

I am on Strapi version 3.6.5.

update: this is a known bug - Setting autoPopulate to false on relations does not return ids. · Issue #4630 · strapi/strapi · GitHub fix and date tbc…

6

Any update on this?

Just to be clear: the goal is to have only the ids of the relationship while avoiding an extra unnecessary read to the DB for performance reasons.

I believe it can’t be done in only one read because the relationship is not stored in one column, it is stored in another table.

For example products and categories would be split in 3 tables:

  • categories
  • products
  • products_categories__categories_products (where the relationship is stored)

But still when calling categories and extra call to products could be avoided by just calling products_categories__categories_products and returning the ids without populating it. And this is where we are stuck.

It may sound like not much, but is causing me and extra 1000+ unnecessary DB reads in a very requested endpoint.

Any solution to alleviate the problem would be welcome, no matter how hacky it feels :joy:

Thanks!

7

Would you recommend writing a custom Query (Backend customization - Strapi Developer Documentation) with Bookshelf and stuff?

8

In strapi v3 I’ve used this:

const {
  convertRestQueryParams,
  buildQuery,
} = require('strapi-utils');

const exportQuery = buildQuery({
  model: strapi.query('products').model,
  filters: convertRestQueryParams({
    _where: {},
    _limit: -1,
  }),
  rest: undefined,
})

const response = await strapi
  .query('products')
  .model
  .query((qb) => {
    exportQuery(qb);
    qb.select('category')
      .distinct()
      .catch((error) => {
        strapi.log.error(error);

        return null;
      });
  })
  .fetchAll({ withRelated: ['category'] });

const categories = response.toJSON().map(result => result.category);
9

I have the same problem in v4. I don’t want to populate the whole related entry, I only want the id of it, for performance reasons. Isn’t it possible?

10

You can. Check this link from the docs:

/api/articles?populate[category][fields][0]=name&populate[category][fields][1]=url

Tha way you will populate only name and url of the category relation

2 Likes
11

@sunnyson I don’t think it’s a good idea as it is one more query or join table. I want only the ids, really, but i think that if I query populate=myrelation.id, it will do one my query (or join) anyway. I would love to avoid that extra query. but, from what I saw until here, it’s not possible in v4.

12

Any updates on this? This seems to be a huge performance issue

13

@jakubm @matepaiva I think here is the related feature request: Add option to populate only an array of IDs for relations (and maybe others) | Developer Experience | Strapi.
Please vote for it and add your suggestions.

14

CC @sunnyson @d_w

15

Also maybe you guys already have some viable solution? Other than mapping for each required operation

16

i have the same issue. any solution ?