Hi. in jsonapi website write for multivalue field i can call this url
GET /comments?filter[post]=1,2 HTTP/1.1
i expect bellow query return only articles that have term 508 AND 511 but seems this query all article that have 508 OR 511 term id:
http://backend/api/node/article?filter[and-group][group][conjunction]=AND
&filter[taxonomy_term--group][condition][path]=field_group.drupal_internal__tid&filter[taxonomy_term--group][condition][operator]=IN&filter[taxonomy_term--group][condition][value][]=508
&filter[taxonomy_term--group][condition][memberOf]=and-group&filter[group-b][condition][path]=field_group.drupal_internal__tid&filter[group-b][condition][operator]=IN&filter[group-b][condition][value][]=511
&filter[group-b][condition][memberOf]=and-group&include=field_group
how can i write a json query for that?
Comments
Comment #2
wim leersThis seems to follow https://www.drupal.org/docs/8/modules/jsonapi/filtering#condition-groups.
It's kinda weird though that it's using
INfor each subgroup instead of=. But that should work.I adapted it to work with
node--articleandfield_tags:http://d8.test/jsonapi/node/article?filter[and-group][group][conjunction]=AND&filter[taxonomy_term--group][condition][path]=field_tags.drupal_internal__tid&filter[taxonomy_term--group][condition][operator]=IN&filter[taxonomy_term--group][condition][value][]=1&filter[taxonomy_term--group][condition][memberOf]=and-group&filter[group-b][condition][path]=field_tags.drupal_internal__tid&filter[group-b][condition][operator]=IN&filter[group-b][condition][value][]=2&filter[group-b][condition][memberOf]=and-groupthat generated this SQL query:
and with placeholders filled in:
That indeed returns the empty result set.
I wonder if this is a bug in the Entity Query API…
Note that
http://d8.test/jsonapi/node/article?filter[and-group][group][conjunction]=AND&filter[taxonomy_term--group][condition][path]=field_tags.drupal_internal__tid&filter[taxonomy_term--group][condition][value]=1&filter[taxonomy_term--group][condition][memberOf]=and-group&filter[group-b][condition][path]=field_tags.drupal_internal__tid&filter[group-b][condition][value]=2&filter[group-b][condition][memberOf]=and-groupalso fails, and generates a similar query:@gabesullice is the expert in this area, assigning to him. This definitely seems like a major bug, although I'm not yet sure whether it's in JSON:API or the Entity Query API.
Comment #3
wim leersSimilar bug report at #3067449: Multivalue reference field AND conditions.
Comment #4
gabesulliceI had some trouble understanding the problem in an abstract way from the supplied queries. I looked at the issue @Wim Leers linked and marked as a duplicate, that was really helpful in understanding the problem here. I'm cross-posting @ebeyrent's issue summary here (with some extra formatting) so anyone else can use that for context-building too:
Comment #5
wim leersI think #3058910: [PP-1] JSON:API - How to retrieve multiple values in a CONTAINS condition? may be a duplicate too, and would mean this is not limited to just reference fields.
Comment #6
gabesulliceRegarding the queries in the original summary and #2, I think they're overly complicated (although that's not the root problem). There's already an implicit root
ANDgroup, so we can remove the groups in all the queries to illustrate the same problem.Coincidentally, I deployed a public JSON:API server to serve as an example implementation. It lives at
https://example.jsonpapi.dev. Let's use that for sharing queries.It has three articles. One article is tagged with a term named "Blue". One article is tagged with a term named "Red". And one article is tagged with both a term named "Blue" and a term named "Red".
Here, we can see both articles tagged with "Blue":
https://example.jsonapi.dev/jsonapi/node/article?filter[blue][condition]...
Here, we can see both articles tagged with "Red":
https://example.jsonapi.dev/jsonapi/node/article?filter[red][condition][...
Because of the implicit root
ANDgroup, this should return only the articled withdrupal_internal__nid: 3:https://example.jsonapi.dev/jsonapi/node/article?filter[blue][condition]...
Instead, the previous result is empty. This is simplest way to reproduce the bug. You might be tempted to think that is because the query is not using the
INoperator. For thoroughness, that is empty too:https://example.jsonapi.dev/jsonapi/node/article?filter[blue][condition]...
Comment #7
gabesulliceI think as a next step, we need to isolate whether this is a JSON:API problem or an entity query problem. Let's write a kernel test that either proves (or disproves) that a plain, PHP-written entity query can get the desired results.
Comment #8
gabesulliceOkay, I've written the test I suggested in #7.
Unfortunately, it looks like this is a problem with the entity query API itself. Not JSON:API.
I've uploaded three files. The
should-passfile changes an existing entity query test to use an unlimited cardinality field and sets up the test scenario described by this issue. Theshould-failfile also adds the failing assertion that proves this bug report. Theinterdiffshows the difference between the passing and the failing tests files.Comment #9
gabesullice@Wim Leers pointed out in Slack that #3058910: [PP-1] JSON:API - How to retrieve multiple values in a CONTAINS condition? is very similar to this issue. The primary difference is that it does not have anything to do with entity references. I suspect that the bug here is really about multi-value fields, not references, so I've marked that issue as "Postponed" on this issue. I'm pointing this out so that in whatever fix we come up with here, we can be sure to not get over-focused on the entity reference part of this issue.
Comment #11
gabesulliceComment #12
gabesulliceI was shocked that this didn't work and so I went digging through the issue queues to find similar bugs. To my surprise I couldn't find any duplicates! However, armed with the knowledge that this could be replicated with a vanilla entity query, I started on a Google hunt. Lo and behold:
https://drupal.stackexchange.com/questions/226396/perform-a-query-with-a...
The OP is describing exactly the same problem as this! The answerers had a solution: use multiple andConditionGroups. I tried it via JSON:API and it works as expected. Here's the working query in an easier-to-read format:
I'm degrading this to a "Normal" priority because it's now an "Admin- or developer-facing [bug] with a workaround."
The workaround is to add two distinct filter groups. I think this is still a bug because the intuitive/logical approach still does not work.
Comment #13
gabesulliceComment #14
ebeyrent commented@gabesullice I can confirm this works!
I too was shocked that the basic query was producing an empty result set, but this (somewhat complex) workaround works as expected. You're just in time too - I am doing a JSON:API presentation at a DrupalCamp tomorrow and this was in my demo!
I agree with your assessment that this is a bug, and with the workaround, I also agree with your assessment of priority.
Thank you so much for putting time into this, and thank you @Wim Leers for moving my issue here!
Comment #15
wim leersQuoting from Drupal Slack:
@gabesullice++
Comment #16
berdirPer Slack, there might be an existing issue for this already, not sure. We've added support for delta-specific queries a while ago, so you can say delta 0 needs to be this value. That would maybe allow for some very limited and ugly workarounds.
Entity query magically builds joins and reuses them if it thinks that this is the same thing. @chx might have some thoughts about this too.
One thing that I'm wondering is whether it would make sense to explicitly support a "CONTAINS ALL" operation, like we already support BETWEEN and STARTS_WITH, that might or might not be easier to implement and possibly also easier to port to non-SQL backends? And also easier to use.
Comment #17
wim leersDelta-specific queries indeed are supported, but that wouldn't solve this problem, since it'd require the user to try all combinations of possible deltas. I think that's what you mean when you say 😀
Could you clarify this? I don't follow 😊
That could work!
Comment #18
berdir> Could you clarify this? I don't follow 😊
Basically it's based on the prefix. It knows that some_field.entity.name needs a different join than other_field.entity.name, but because these cnditions share the prefix, it assumes the condition needs to be on the same join. There isn't really a syntax right now that would allow you to prevent that. See \Drupal\Core\Entity\Query\Sql\Tables::ensureFieldTable, how it has a property where it keys joins by prefix + field name.
Based on that, you might get the desired result in the test if you mix using the property and not using it (the two cases in the test), not quite sure and you'd need more variations if you have more values ;)
It's also worth pointing out that such a condition is likely very inefficient in SQL and that's not really fixable. Especially if there is a lot of data with those values.
Comment #19
arefen commentedThanks @gabesullice i try with your solution on #12 and this work as expected on your senario.
Let check your solution with this senario:
It has four articles. One article is tagged with a term named "Blue" with nid= 10 . One article is tagged with a term named "Red" with nid= 11. One article is tagged with both a term named "Blue" and a term named "Red" with nid=12 . And one article with a three term "Red" , "Blue" And "yellow" with nid=13
with this query
I expected i get only article with nid 13. but i get two article 12 and 13. with this query i say fetch all articles that have term name "blue" AND "Red" Not more But this query fetch all articles that IN(Red & Blue).
Comment #20
gabesullice@Berdir, thanks for chiming in!
I'm glad you're thinking about expanding the API via a new operator. I'm not sure an operator is the right choice though.
If we use an operator, don't we limit the API to only an exact matches?
Consider that I have a pair of entities each with a multi-value string field with the following values:
and I wanted to find only the entity with opinions about blue AND red. I'd need a STARTS_WITH operator.
Instead of an operator, maybe we need a method that works like
orConditionGroup. For example:Alternatively, a new field syntax.
Comment #21
wim leersOn who/what is this blocked?
Comment #22
hchonovPlease remember to give credit to @ebeyrent according to #4.
I like #20 very much. We just need to make that work :).
Comment #24
jwjoshuawalker commentedI just want to chime in with a reminder that Views is still without this behavior as well.
The progress on a "compare 2 fields" filter has hit the same roadblock with matching multiple values on an "ANY vs ALL" basis:
#699252: Create a method of filtering one field by its relation to another field
I'm in that Filter's code now about to make something work for myself, but it seems without a core API change to the effect of a "CONTAINS ALL" or @gabesullice's suggestion in #20, I will have to do something silly like:
- Get all values from left table.some_field
- Iterate through them, adding "and" conditions for each that compares to right table.some_field
I would like to submit back what I come up with to progress that ticket, but 1) This approach just feels crufty, 2) I don't think within the context of a Views Filter I even have access to values yet, query is still being constructed, which would lend support to the argument for a new operator type instead ("CONTAINS ALL").
Comment #26
jwjoshuawalker commentedSorry for the noise, I was initially looking at this from bird's eye and after trying all manner of subquery approaches (as a JOIN, a WHERE, trying to match 2 subqueries results with
= ALL(, I just don't see a current solution from a purely-query perspective.There is a 3rd situation to consider, and maybe that is why no query approach thus far solves this, where entity has ALL of (values) and no more, no less.
e.g. entity has all: a, b, c, but NOT d.
Making the full list of situations as I understand it:
HAVING COUNT(table1.field) = COUNT(table2.field)Comment #29
leovw commentedHi - Just wondering whether there has been any progress on this as this thread seems to have gone quiet for the last 2 years but I still seem to be experiencing the same issue and the workaround no longer seems to be working either??
Thanks, Leo
Comment #30
leovw commentedComment #31
cpatrone commentedThe workaround in #12 seems to be still working @leovw. I just implemented a custom views query plugin and it worked as expected. This is the code I used to generate the filter part:
Hope this helps.
Comment #33
dorficus commentedBringing this issue back to light and wondering if there is an issue for the Views behavior that should be related to this. I've done some searching and haven't come up with any yet, but someone on this thread may have an idea of where to look for it.