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

arefen created an issue. See original summary.

wim leers’s picture

Title: AND in filter » Filtering multivalue reference fields to contain multiple specified values does not work
Category: Support request » Bug report
Priority: Normal » Major
Issue tags: +Entity query

This seems to follow https://www.drupal.org/docs/8/modules/jsonapi/filtering#condition-groups.

It's kinda weird though that it's using IN for each subgroup instead of =. But that should work.

I adapted it to work with node--article and field_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-group

that generated this SQL query:

SELECT base_table.vid AS vid, base_table.nid AS nid
FROM
{node} base_table
INNER JOIN {node__field_tags} node__field_tags ON node__field_tags.entity_id = base_table.nid
LEFT OUTER JOIN {taxonomy_term_data} taxonomy_term_data ON taxonomy_term_data.tid = node__field_tags.field_tags_target_id
INNER JOIN {taxonomy_term_field_data} taxonomy_term_field_data ON taxonomy_term_field_data.tid = taxonomy_term_data.tid
LEFT OUTER JOIN {taxonomy_term_data} taxonomy_term_data_2 ON taxonomy_term_data_2.tid = node__field_tags.field_tags_target_id
INNER JOIN {taxonomy_term_field_data} taxonomy_term_field_data_2 ON taxonomy_term_field_data_2.tid = taxonomy_term_data_2.tid
INNER JOIN {node_field_data} node_field_data ON node_field_data.nid = base_table.nid
WHERE ((taxonomy_term_field_data.tid IN (:db_condition_placeholder_0)) and (taxonomy_term_field_data_2.tid IN (:db_condition_placeholder_1))) AND (node_field_data.type = :db_condition_placeholder_2)
GROUP BY base_table.vid, base_table.nid
LIMIT 51 OFFSET 0

and with placeholders filled in:

SELECT base_table.vid AS vid, base_table.nid AS nid
FROM
node base_table
INNER JOIN node__field_tags node__field_tags ON node__field_tags.entity_id = base_table.nid
LEFT OUTER JOIN taxonomy_term_data taxonomy_term_data ON taxonomy_term_data.tid = node__field_tags.field_tags_target_id
INNER JOIN taxonomy_term_field_data taxonomy_term_field_data ON taxonomy_term_field_data.tid = taxonomy_term_data.tid
LEFT OUTER JOIN taxonomy_term_data taxonomy_term_data_2 ON taxonomy_term_data_2.tid = node__field_tags.field_tags_target_id
INNER JOIN taxonomy_term_field_data taxonomy_term_field_data_2 ON taxonomy_term_field_data_2.tid = taxonomy_term_data_2.tid
INNER JOIN node_field_data node_field_data ON node_field_data.nid = base_table.nid
WHERE ((taxonomy_term_field_data.tid IN (1)) and (taxonomy_term_field_data_2.tid IN (2))) AND (node_field_data.type = 'article')
GROUP BY base_table.vid, base_table.nid
LIMIT 51 OFFSET 0

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-group also fails, and generates a similar query:

SELECT base_table.vid AS vid, base_table.nid AS nid
FROM
{node} base_table
INNER JOIN {node__field_tags} node__field_tags ON node__field_tags.entity_id = base_table.nid
LEFT OUTER JOIN {taxonomy_term_data} taxonomy_term_data ON taxonomy_term_data.tid = node__field_tags.field_tags_target_id
INNER JOIN {taxonomy_term_field_data} taxonomy_term_field_data ON taxonomy_term_field_data.tid = taxonomy_term_data.tid
LEFT OUTER JOIN {taxonomy_term_data} taxonomy_term_data_2 ON taxonomy_term_data_2.tid = node__field_tags.field_tags_target_id
INNER JOIN {taxonomy_term_field_data} taxonomy_term_field_data_2 ON taxonomy_term_field_data_2.tid = taxonomy_term_data_2.tid
INNER JOIN {node_field_data} node_field_data ON node_field_data.nid = base_table.nid
WHERE ((taxonomy_term_field_data.tid = :db_condition_placeholder_0) and (taxonomy_term_field_data_2.tid = :db_condition_placeholder_1)) AND (node_field_data.type = :db_condition_placeholder_2)
GROUP BY base_table.vid, base_table.nid
LIMIT 51 OFFSET 0

@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.

wim leers’s picture

gabesullice’s picture

I 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:

Imagine you have a "movie" content type that has a multi-value entity reference field for the "actor" content type.

Let's say you have movies with Keanu Reeves. Let's say you have movies with Sandra Bullock. Let's also say you have a movie that has both Keanu and Sandra.

Using JSON:API, I can get a list of all movies that have Keanu:

/jsonapi/node/movie
?include=field_actors,field_genre,field_poster
&filter[actor][condition][path]=field_actors.title
&filter[actor][condition][value]=Keanu Reeves

I can get a list of all movies that have Sandra:

/jsonapi/node/movie
?include=field_actors,field_genre,field_poster
&filter[actor][condition][path]=field_actors.title
&filter[actor][condition][value]=Sandra Bullock

I can get a list of all movies that have either Keanu or Sandra:

/jsonapi/node/movie
?include=field_actors,field_genre,field_poster
&filter[actor][condition][path]=field_actors.title
&filter[actor][condition][value][]=Keanu Reeves
&filter[actor][condition][value][]=Sandra Bullock
&filter[actor][condition][operator]=IN

But what I really want is to get a list of movies where both Keanu AND Sandra are in the movie. I can't seem to get the filter right - any ideas?

wim leers’s picture

I 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.

gabesullice’s picture

Regarding 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 AND group, 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 AND group, this should return only the articled with drupal_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 IN operator. For thoroughness, that is empty too:

https://example.jsonapi.dev/jsonapi/node/article?filter[blue][condition]...

gabesullice’s picture

I 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.

gabesullice’s picture

Status: Active » Needs review
StatusFileSize
new1.34 KB
new9.02 KB
new8.16 KB

Okay, 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-pass file changes an existing entity query test to use an unlimited cardinality field and sets up the test scenario described by this issue. The should-fail file also adds the failing assertion that proves this bug report. The interdiff shows the difference between the passing and the failing tests files.

gabesullice’s picture

@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.

The last submitted patch, 8: 3066202-8-should-fail.patch, failed testing. View results

gabesullice’s picture

Component: jsonapi.module » entity system
Issue tags: +API-First Initiative
gabesullice’s picture

I 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:

https://example.jsonapi.dev/jsonapi/node/article
?filter[blue-and][group][conjunction]=AND
&filter[red-and][group][conjunction]=AND
&filter[blue][condition][value]=Blue
&filter[blue][condition][path]=field_tags.name
&filter[blue][condition][memberOf]=blue-and
&filter[red][condition][value]=Red
&filter[red][condition][path]=field_tags.name
&filter[red][condition][memberOf]=red-and

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.

gabesullice’s picture

Priority: Major » Normal
ebeyrent’s picture

@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!

wim leers’s picture

Quoting from Drupal Slack:

gabesullice  [3 days ago]
Looks like this is an entity query API problem, not a JSON:API problem: https://www.drupal.org/project/drupal/issues/3066202#comment-13181159

e0ipso  [2 days ago]
@gabesullice @wimleers (he/him) I've had reports like this for years. Even before JSON:API.

e0ipso  [2 days ago]
Filtering with multi-value fields is *very* confusing to reason about.

wimleers (he/him)  [< 1 minute ago]
Excellent digging, @gabesullice! And @e0ipso, I agree it’s confusing DX at the very least, but I didn’t realize it had been known for so long! Do you remember where you first encountered this? Because you say you even had reports like this from _before_ JSON:API 😨

@gabesullice++

berdir’s picture

Per 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.

wim leers’s picture

Delta-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 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.

Could you clarify this? I don't follow 😊

One thing that I'm wondering […]

That could work!

berdir’s picture

> 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.

arefen’s picture

Thanks @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

https://example.jsonapi.dev/jsonapi/node/article
?filter[blue-and][group][conjunction]=AND
&filter[red-and][group][conjunction]=AND
&filter[blue][condition][value]=Blue
&filter[blue][condition][path]=field_tags.name
&filter[blue][condition][memberOf]=blue-and
&filter[red][condition][value]=Red
&filter[red][condition][path]=field_tags.name
&filter[red][condition][memberOf]=red-and

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).

gabesullice’s picture

@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:

Entity 1:
Blue is my favorite color.
Red is my second favorite color.

Entity 2: 
Blue is my second favorite color.
Yellow is my favorite color.

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:

$has_group = $query->hasConditionGroup();
$has_group->condition('field_color_preferences.value', 'Blue', 'STARTS_WITH');
$has_group->condition('field_color_preferences.value', 'Red', 'STARTS_WITH');
$query->condition($has_group);

Alternatively, a new field syntax.

$group = $query->andConditionGroup();
$group->condition('field_color_preferences.@.value', 'Blue', 'STARTS_WITH');
$group->condition('field_color_preferences.@.value', 'Red', 'STARTS_WITH');
$query->condition($group);
wim leers’s picture

On who/what is this blocked?

hchonov’s picture

Please remember to give credit to @ebeyrent according to #4.

I like #20 very much. We just need to make that work :).

Version: 8.7.x-dev » 8.8.x-dev

Drupal 8.7.9 was released on November 6 and is the final full bugfix release for the Drupal 8.7.x series. Drupal 8.7.x will not receive any further development aside from security fixes. Sites should prepare to update to 8.8.0 on December 4, 2019. (Drupal 8.8.0-beta1 is available for testing.)

Bug reports should be targeted against the 8.8.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.9.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

jwjoshuawalker’s picture

I 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").

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.7 was released on June 3, 2020 and is the final full bugfix release for the Drupal 8.8.x series. Drupal 8.8.x will not receive any further development aside from security fixes. Sites should prepare to update to Drupal 8.9.0 or Drupal 9.0.0 for ongoing support.

Bug reports should be targeted against the 8.9.x-dev branch from now on, and new development or disruptive changes should be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

jwjoshuawalker’s picture

Sorry 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:

  • - has ANY of - which is widely covered
  • - has ALL of known values - which is being discussed in this ticket
  • - has ALL of table values - where all of left side is found in right side
  • - BOTH sides have ALL of table values - where all of left is found right, and all of right is found left HAVING COUNT(table1.field) = COUNT(table2.field)

Version: 8.9.x-dev » 9.2.x-dev

Drupal 8 is end-of-life as of November 17, 2021. There will not be further changes made to Drupal 8. Bugfixes are now made to the 9.3.x and higher branches only. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.2.x-dev » 9.3.x-dev
leovw’s picture

Hi - 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

leovw’s picture

Status: Needs review » Needs work
cpatrone’s picture

The 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:

foreach ($array_with_tids_values_to_search_for as $tid) {
  $query_string .= "&filter[tag-" . $tid . "-and][group][conjunction]=AND";
  $query_string .= "&filter[tag-" . $tid . "][condition][value]=" . $tid;
  $query_string .= "&filter[tag-" . $tid . "][condition][path]=field_tags.drupal_internal__tid";
  $query_string .= "&filter[tag-" . $tid . "][condition][memberOf]=tag-" . $tid . "-and";
 }

Hope this helps.

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.15 was released on June 1st, 2022 and is the final full bugfix release for the Drupal 9.3.x series. Drupal 9.3.x will not receive any further development aside from security fixes. Drupal 9 bug reports should be targeted for the 9.4.x-dev branch from now on, and new development or disruptive changes should be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

dorficus’s picture

Bringing 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.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.9 was released on December 7, 2022 and is the final full bugfix release for the Drupal 9.4.x series. Drupal 9.4.x will not receive any further development aside from security fixes. Drupal 9 bug reports should be targeted for the 9.5.x-dev branch from now on, and new development or disruptive changes should be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 11.x-dev » main

Drupal core is now using the main branch as the primary development branch. New developments and disruptive changes should now be targeted to the main branch.

Read more in the announcement.