Advanced API filter combining AND and OR

Questions and Answers
1

Hi!

I have a problem with my API querying. The thing I want to achieve doesn’t seem too complex, but the query gets pretty long, so bear with me.

I have one content type ContentType1 which has a relation Author (which has a field Name). It also has a relation to ContentType2, which also has a relation to Author. Like this:

ContentType1 {
contentType2: ContentType2
author: Author
}

ContentType2 {
author: Author
}

Author {
Name: string
}

I want to create a filter function where I enter names of authors. For each name, i want to check if they’re an author of ContentType1 OR an author of ContentType1’s contentType2. In that case I want to return the matching ContentType1. I can achieve this by:

/api/contentType1s?filters[$or][0][Author][Name][$containsi]=AUTHOR1&contentType1s?filters[$or][1][ContentType2][Author][Name][$containsi]=AUTHOR1

So far so good - this works. But how would I now query if I want to look for the above, but in combination with another author? That is, get all contentType1s which has AUTHOR1 as author/sub-author AND AUTHOR2 as author/sub-author.

(contentType1’s author = AUTHOR1 OR contentTypes1’s contentType2’s author = AUTHOR1) AND (contentType1’s author = AUTHOR2 OR contentTypes1’s contentType2’s author = AUTHOR2)

I tested the following, but that seems to do an OR check between the different sets of authors:
/api/contentType1s?filters[$or][0][Author][Name][$containsi]=AUTHOR1&filters[$or][1][ContentType2][Author][Name][$containsi]=AUTHOR1&filters[$or][0][Author][Name][$containsi]=AUTHOR2&filters[$or][1][ContentType2][Author][Name][$containsi]=AUTHOR2

1 Like
2

I would really appreciate some help on this…

1 Like
3

I am coping with the same problem and would like some help too

1 Like
4

I would say start with an and statment and put an or statement inside of it

5

this seems to solve it for me:

6

in a situation where you have an

((or or or) AND (or or or))

you can do something like this:

{{host}}/api/blog?sort=publishedAt%3Adesc
&populate=Tags.tags
&populate=Image
&pagination[page]=1
&pagination[pageSize]=25
&locale=en
&filters[$and][0][$or][0][title][$containsi]=important
&filters[$and][0][$or][1][introduction][$containsi]=important
&filters[$and][0][$or][2][body][$containsi]=important
&filters[$and][1][$or][0][tags][$containsi]=JAVA
&filters[$and][1][$or][1][tags][$containsi]=BE
&filters[$and][1][$or][2][tags][$containsi]=FE
&filters[$and][1][$or][3][tags][$containsi]=PYTHON
&filters[$and][1][$or][4][Tags][$containsi]=JAKARTAEE
&filters[$and][1][$or][5][Tags][$containsi]=SPRING

where [$and][0] or [$and][1] tells in what group the AND belongs and [$or][0] the OR in the AND group

It actually does make sense if you know how it works but OMG it took me forever to find this out. The documentation is not great in explaining this. It actually does not explain it at all :slight_smile:

4 Likes
7

Ah, excellent job figuring that out! I tried a bunch of “logical” guesses on how those indices were supposed to be used, but never succeeded. As you’re saying, it does make sense now when you see it, but it should definitely be specified in the documentation.

1 Like
8

Just a side note here though. We are finding out that performance goes down really fast if you have some relation in there and there are more than about 6 filters. We have many more and wow it’s bad

9

Yeah, I can imagine… What kind of response times are we talking about?

10

we jump from about 11 seconds when filtering on 6 items to 97 seconds when on 7. Lets me be clear that 11 seconds is already way too long. When searching on this performance issue it seems to be related to Relation(s). Still researching this though

11

Hi IvoNet, I’m new to querying with the Strapi api and had seen this note in the docs

:raised_hand: CAUTION

The population and pagination operators cannot be combined.

but since then I’ve tried and it does work, I noticed you’ve also done that here and wondered if you had any info that suggests it is now supported and allowed?
Thx.

12

Sry for the late reaction, but no I do not know

13

It is allowed on the first layer only aka you can’t do it inside of a populate so
{ populate: {}, pagination: {}} would work but { populate: { pagination: {}}} would not but with for example fields or some of the others it would work

14

You saved my day. I had to sign up to this forum just to tell you “Thank you!”.

15

Hi IvoNet, I wonder if you found a solution for those performance issues? I have a relatively small database but filtering with 10 filters takes ages and CPU usage jumps up to 100%, with more I don’t even get the results back :frowning: