How to deep fetch In Strapi cms compnents?

Question

I have the following user API from Strapi cms to which I want to deep fetch address component(repeatable component).

So, what I want to fetch is the user whose address is 63 Southlands Road... I tried so many ways:

• tried the Strapi built-in query

http://localhost:1337/users?address.info=63%20Southlands%20Road

• tried the custom query:

   const result = await strapi
     .query("user")
     .model.query((qb) => {
       qb.where("address.info", "63 Southlands Road");
     })
     .fetch();
  

All these above messages didn't work out and I run out of options. Is this possible in Strapi... if not I am trying to switch to node server.

Answer 1

In Strapi there's no way of querying the models based on the nested component data directly. But you can use a raw query instead to query the component data and get the desired result. The table names are based on the model name in context, as Strapi follows this particular naming convention and automatically creates the tables when you create the content-types in admin.

Naming convention followed by Strapi

If you create a model called user and create two components in it for example, address and location then Strapi will create 4 tables as follows:

1. users - This is the table that actually stores the model data
2. users_components - This table stores a mapping of all the components that the user model has for each record
3. components_users_address - This table will store the data about all the address components linked to the user model
4. components_users_location - The table will store the data about all the location components linked to the user model

You can cross check the table names by opening up your project database via pg-admin or php-myadmin.

Solution

let result = await strapi.connections.default.raw(
"SELECT u.*
 FROM users u
 LEFT JOIN users_components uc
 ON (uc.user_id = u.id AND uc.component_type='components_users_address' AND uc.field='address')
 LEFT JOIN components_users_address cua ON (cua.id = uc.component_id)
 WHERE AND cua.ifno LIKE '63 Southlands Road'");
console.log(result);

P.S: I've only tried this in MySQL and PostGreSQL