Help Needed with Complex Query SORTING by nested repeatable Fields in Strapi

Questions and Answers
1
System Information
  • Strapi Version: 14.15.5
  • Operating System: macOS sonoma 14.5
  • Database: postgresql
  • Node Version: 18.20.2
  • NPM Version: 10

Hi everyone,

I’m facing a challenge with constructing a query in Strapi that sorts, filters and populates nested fields for my content types. Below are the details of my content types and the query requirements.

Content Types:

  1. University
  • id
  • title
  • slug
  • noOfStudentsStudying
  • createdAt
  • updatedAt
  • publishedAt
  • authorName
  • rankingStreams (Relation with RankingStream)
  1. RankingStream
  • id
  • rankingYear
  • description
  • rankingNumber
  • stream (Relation with Stream)
  • rankingPublisher (Relation with RankingPublisher)
  1. Stream
  • id
  • slug
  1. RankingPublisher
  • id
  • slug

Query Requirements:

I need to fetch universities and their associated ranking streams, filtering the ranking streams based on specific conditions. The query needs to:

  1. Populate rankingStreams with rankingNumber and rankingYear.
  2. Further populate rankingStreams.stream with slug and id.
  3. Further populate rankingStreams.rankingPublisher with slug.
  4. Filter rankingStreams based on rankingPublisher.slug and stream.slug.
  5. Sort the results by rankingStreams.rankingNumber in ascending order.
  6. Ensure only non-null rankingNumber and rankingStreams.id are considered.

Here is the query I attempted:

&populate[rankingStreams][fields][0]=rankingNumber
&populate[rankingStreams][fields][1]=rankingYear
&populate[rankingStreams][populate][stream][fields][0]=slug
&populate[rankingStreams][populate][stream][fields][1]=id
&populate[rankingStreams][populate][rankingPublisher][fields][1]=slug
&populate[rankingStreams][filters][rankingPublisher][slug][$eq]=${rankingParam}
&populate[rankingStreams][filters][stream][slug][$eq]=${params.stream}
&populate[rankingStreams][sort][0]=rankingNumber:asc
&filters[rankingStreams][rankingNumber][$notNull]=true

However, this query isn’t yielding the expected results. Either the data is empty or the filtering isn’t working as intended.

Could someone guide me on how to correctly construct this query using Strapi’s query builder or qs package or LHS ? Any help or pointers would be greatly appreciated!

Thank you!