Strapi API - Limit Results By Related Object Field Value

System Information
  • Strapi Version: 4.1.5
  • Operating System: Debian GNU/Linux 9
  • Database: PostgreSQL 13
  • Node Version: v14.16.0
  • NPM Version: 6.14.11
  • Yarn Version: v1.22.5

Hi Strapi Community,

I’ve been reading all that I can on this forum and in the documentation about filtering with the Strapi API, and I must be missing something. I need to query an option that has related objects, which in turn have related objects, which also have related objects, and I need to filter (i.e. restrict) the results in a way that doesn’t seem to be working. If I have 4 levels of related objects, for example, I want to only obtain objects at the third level if a field at the fourth level contains a certain value. Instead, what happens is that the query still shows all possible related objects at level three, and just makes the level 4 objects “null” that don’t meet the criteria.

To be a little more specific, here are my levels:

 Pillar 
   |__ (1:N) --> ActivityGroups
      |__ (1:N) --> Activities
         |__ (1:N) --> ActivityAttempts
            |__ (N:1) --> Organization

I’m trying to display this information, organized in this way, but for a single organization. When I try to add the organization’s name as a filter on the populate clause, it still shows all ActivityAttempts, even the ones that weren’t for that organization, and it just uses “null” for the organization. I’m trying to get it to not display those attempts at all if they don’t belong to that organization. Here’s the query I’m doing so far, I’d really appreciate some help with getting this to work correctly:

    return await strapi.entityService.findMany('api::pillar.pillar', {                                                                                                                                             
      populate: {
        ActivityGroups: {
          populate: {
            Activities: {
              populate: {
                ActivityAttempts: {
                  populate: {
                    Organization: {
                      filters: {
                        org_cst_key: {
                          $eq: '2df1f48b-8bac-40d2-b02a-6d22196b5f4b'
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      },
      filters: { 
        $and: [
          {
            createdAt: { $gte: `${start}` },
          },
          {
            createdAt: { $lt: `${end}` },
          },
          {
            ActivityGroups: {
              Activities: {
                ActivityAttempts: {
                  Organization: {
                    org_cst_key: {
                      $eq: '2df1f48b-8bac-40d2-b02a-6d22196b5f4b'
                    }
                  }
                }
              }
            }
          }
        ]
      } 
    });

1 Like

I’m not sure but I think you can not mix filters with populate options

update: Yes, you can Population for REST API - Strapi Developer Docs

I tried many variations (with populate and without filter, with filter and without populate, and both). Could not get the result I wanted. So I ended up just doing direct database queries instead. Can’t spend too long with an API that just won’t work. This was much cleaner:

    const rows = await strapi.db.connection.context.raw(`
      SELECT <query here>;
    `);