When filtering by entity relationships in an "OR" group, if one of the relationship fields is empty, the item is improperly excluded from the data

I don't have an answer but I've spit out the resulting queries that are close to the reporter's setup. Maybe this illuminates what might be happening. Clearly the query that doesn't work has some extra stuff going on.

Unauthenticated (doesn't return results)

SELECT base_table.vid AS vid, base_table.nid AS nid
FROM
node base_table
LEFT JOIN node__field_games node__field_games ON node__field_games.entity_id = base_table.nid
LEFT OUTER JOIN node node ON node.nid = node__field_games.field_games_target_id
LEFT JOIN node node_2 ON node_2.nid = node.nid
LEFT JOIN node__field_teams node__field_teams ON node__field_teams.entity_id = base_table.nid
LEFT OUTER JOIN node node_3 ON node_3.nid = node__field_teams.field_teams_target_id
LEFT JOIN node node_4 ON node_4.nid = node_3.nid
INNER JOIN node__field_games node__field_games_2 ON node__field_games_2.entity_id = base_table.nid
LEFT OUTER JOIN node node_5 ON node_5.nid = node__field_games_2.field_games_target_id
INNER JOIN node_field_data node_field_data ON node_field_data.nid = node_5.nid
INNER JOIN node_field_data node_field_data_2 ON node_field_data_2.nid = base_table.nid
INNER JOIN node__field_teams node__field_teams_2 ON node__field_teams_2.entity_id = base_table.nid
LEFT OUTER JOIN node node_6 ON node_6.nid = node__field_teams_2.field_teams_target_id
INNER JOIN node_field_data node_field_data_3 ON node_field_data_3.nid = node_6.nid
INNER JOIN node_field_data node_field_data_4 ON node_field_data_4.nid = base_table.nid
INNER JOIN node_field_data node_field_data_5 ON node_field_data_5.nid = base_table.nid
WHERE ((node_2.uuid IN ('e360d893-652e-4dd5-b823-866b80933469', 'f1f6aea5-e291-4d8f-aa00-46948554aa5e')) or (node_4.uuid IN ('15ce0927-cf7a-4aff-919a-385c65fe4996'))) AND (node_field_data.status = '1') AND (node_field_data_2.status = '1') AND (node_field_data_3.status = '1') AND (node_field_data_4.status = '1') AND (node_field_data_5.type = 'tournament')
GROUP BY base_table.vid, base_table.nid
LIMIT 51 OFFSET 0

Bypass content control (returns data)

SELECT base_table.vid AS vid, base_table.nid AS nid
FROM
node base_table
LEFT JOIN node__field_games node__field_games ON node__field_games.entity_id = base_table.nid
LEFT OUTER JOIN node node ON node.nid = node__field_games.field_games_target_id
LEFT JOIN node node_2 ON node_2.nid = node.nid
LEFT JOIN node__field_teams node__field_teams ON node__field_teams.entity_id = base_table.nid
LEFT OUTER JOIN node node_3 ON node_3.nid = node__field_teams.field_teams_target_id
LEFT JOIN node node_4 ON node_4.nid = node_3.nid
INNER JOIN node_field_data node_field_data ON node_field_data.nid = base_table.nid
WHERE ((node_2.uuid IN ('e360d893-652e-4dd5-b823-866b80933469', 'f1f6aea5-e291-4d8f-aa00-46948554aa5e')) or (node_4.uuid IN ('15ce0927-cf7a-4aff-919a-385c65fe4996'))) AND (node_field_data.type = 'tournament')
GROUP BY base_table.vid, base_table.nid
LIMIT 51 OFFSET 0

I'm also seeing this, my use case is I have a node (lesson) with two user entity reference fields: viewers (field_view) and editors (field_edit). In hook_entity_access I allow view access to the node if they current user is referenced in either field.

When I filter by only one field, I get the correct results. When I filter by both fields, using an OR group, I get no results. Here's my filters:

Query by both fields (doesn't work)

https://localhost/jsonapi/node/lesson
?filter[or][group][conjunction]=OR
&filter[field_edit][condition][path]=field_edit.id
&filter[field_edit][condition][value]=5afa2583-682b-4ddd-92d9-f52b4568365e
&filter[field_edit][condition][memberOf]=or
&filter[field_view][condition][path]=field_view.id
&filter[field_view][condition][value]=5afa2583-682b-4ddd-92d9-f52b4568365e
&filter[field_view][condition][memberOf]=or

Resulting mysql query:

SELECT base_table.vid AS vid, base_table.nid AS nid
FROM
node base_table
LEFT JOIN node__field_view node__field_view ON node__field_view.entity_id = base_table.nid
LEFT OUTER JOIN users users ON users.uid = node__field_view.field_view_target_id
LEFT JOIN users users_2 ON users_2.uid = users.uid
LEFT JOIN node__field_edit node__field_edit ON node__field_edit.entity_id = base_table.nid
LEFT OUTER JOIN users users_3 ON users_3.uid = node__field_edit.field_edit_target_id
LEFT JOIN users users_4 ON users_4.uid = users_3.uid
LEFT JOIN node__field_view node__field_view_2 ON node__field_view_2.entity_id = base_table.nid
LEFT OUTER JOIN users users_5 ON users_5.uid = node__field_view_2.field_view_target_id
LEFT JOIN users_field_data users_field_data ON users_field_data.uid = users_5.uid
LEFT OUTER JOIN users users_6 ON users_6.uid = node__field_view_2.field_view_target_id
LEFT JOIN users_field_data users_field_data_2 ON users_field_data_2.uid = users_6.uid
INNER JOIN node__field_view node__field_view_3 ON node__field_view_3.entity_id = base_table.nid
LEFT OUTER JOIN users users_7 ON users_7.uid = node__field_view_3.field_view_target_id
INNER JOIN users_field_data users_field_data_3 ON users_field_data_3.uid = users_7.uid
LEFT JOIN node_field_data node_field_data ON node_field_data.nid = base_table.nid
LEFT JOIN node__field_edit node__field_edit_2 ON node__field_edit_2.entity_id = base_table.nid
LEFT OUTER JOIN users users_8 ON users_8.uid = node__field_edit_2.field_edit_target_id
LEFT JOIN users_field_data users_field_data_4 ON users_field_data_4.uid = users_8.uid
LEFT OUTER JOIN users users_9 ON users_9.uid = node__field_edit_2.field_edit_target_id
LEFT JOIN users_field_data users_field_data_5 ON users_field_data_5.uid = users_9.uid
INNER JOIN node__field_edit node__field_edit_3 ON node__field_edit_3.entity_id = base_table.nid
LEFT OUTER JOIN users users_10 ON users_10.uid = node__field_edit_3.field_edit_target_id
INNER JOIN users_field_data users_field_data_6 ON users_field_data_6.uid = users_10.uid
LEFT JOIN node_field_data node_field_data_2 ON node_field_data_2.nid = base_table.nid
INNER JOIN node_field_data node_field_data_3 ON node_field_data_3.nid = base_table.nid
WHERE ((users_2.uuid LIKE '5afa2583-682b-4ddd-92d9-f52b4568365e' ESCAPE '\\') or (users_4.uuid LIKE '5afa2583-682b-4ddd-92d9-f52b4568365e' ESCAPE '\\')) AND (((users_field_data.status = '1') or (users_field_data_2.uid = '10')) and (users_field_data_3.uid != '0')) AND ((node_field_data.status = '1') or (node_field_data.uid = '10')) AND (((users_field_data_4.status = '1') or (users_field_data_5.uid = '10')) and (users_field_data_6.uid != '0')) AND ((node_field_data_2.status = '1') or (node_field_data_2.uid = '10')) AND (node_field_data_3.type = 'lesson')
GROUP BY base_table.vid, base_table.nid
LIMIT 51 OFFSET 0

Query by one field (works)

https://localhost/jsonapi/node/lesson
?filter[or][group][conjunction]=OR
&filter[editors][condition][path]=field_edit.id
&filter[editors][condition][value]=5afa2583-682b-4ddd-92d9-f52b4568365e
&filter[editors][condition][memberOf]=or

Above resulting mysql query:

SELECT base_table.vid AS vid, base_table.nid AS nid
FROM
node base_table
LEFT JOIN node__field_edit node__field_edit ON node__field_edit.entity_id = base_table.nid
LEFT OUTER JOIN users users ON users.uid = node__field_edit.field_edit_target_id
LEFT JOIN users users_2 ON users_2.uid = users.uid
LEFT JOIN node__field_edit node__field_edit_2 ON node__field_edit_2.entity_id = base_table.nid
LEFT OUTER JOIN users users_3 ON users_3.uid = node__field_edit_2.field_edit_target_id
LEFT JOIN users_field_data users_field_data ON users_field_data.uid = users_3.uid
LEFT OUTER JOIN users users_4 ON users_4.uid = node__field_edit_2.field_edit_target_id
LEFT JOIN users_field_data users_field_data_2 ON users_field_data_2.uid = users_4.uid
INNER JOIN node__field_edit node__field_edit_3 ON node__field_edit_3.entity_id = base_table.nid
LEFT OUTER JOIN users users_5 ON users_5.uid = node__field_edit_3.field_edit_target_id
INNER JOIN users_field_data users_field_data_3 ON users_field_data_3.uid = users_5.uid
LEFT JOIN node_field_data node_field_data ON node_field_data.nid = base_table.nid
INNER JOIN node_field_data node_field_data_2 ON node_field_data_2.nid = base_table.nid
WHERE (users_2.uuid LIKE '5afa2583-682b-4ddd-92d9-f52b4568365e' ESCAPE '\\') AND (((users_field_data.status = '1') or (users_field_data_2.uid = '10')) and (users_field_data_3.uid != '0')) AND ((node_field_data.status = '1') or (node_field_data.uid = '10')) AND (node_field_data_2.type = 'lesson')
GROUP BY base_table.vid, base_table.nid
LIMIT 51 OFFSET 0

I've tried directly executing different versions of the first query and haven't had any success in getting it to work. It's difficult to understand why all the different JOINS are used.

Also, perhaps because I'm specifying access in hook_entity_access, setting bypass content access doesn't fix this for me.

However, If I add the same user reference to both fields, then that specific node entity will show up in the results correctly when filtered by both fields.

If I can get some direction on where to tinker in the jsonapi module I'm willing to try to get a patch written with my setup.

I see the same behaviour. In my case I want to filter by address in either the `address_line1` or the `address_line2` field provided by the Address module. These are the parameters:

?filter[address][group][conjunction]=OR
&filter[address1][condition][path]=field_installation_address.address_line1
&filter[address1][condition][value]=Street 1
&filter[address1][condition][memberOf]=address
&filter[address2][condition][path]=field_installation_address.address_line2
&filter[address2][condition][value]=Street 1
&filter[address2][condition][memberOf]=address

For testing purposes I created one node having the value 'Street 1' in the `address_line1` field and another having the value 'Street 1' in the `address_line2` field. So logically, I should get two results, but none are found.

I'm also having this same issue. It occurs when filtering against two user entity reference fields for me. If one of those fields are empty they will be omitted from the results.

I think i've found the issue resides within TemporaryQueryGuard::getAccessCondition called from TemporaryQueryGuard::secureQuery.

case 'user':
  // Disallow querying values of the anonymous user.
  // @see \Drupal\user\UserAccessControlHandler::checkAccess()
  $specific_condition = new EntityCondition('uid', '0', '!=');
  break;

Currently the EntityCondition added from within Drupal\jsonapi\Access\TemporaryQueryGuard::secureQuery is simply appended onto the query without any context/care of the original filters conjunction.

WHERE 
(
  (users_field_data.uid = 19) or (users_field_data_2.uid = 19)
) 
AND (users_field_data_3.uid != 0) # Added for each user entity filter with 'new EntityCondition('uid', '0', '!=');'
AND (users_field_data_4.uid != 0)

Where i believe should be:

WHERE
(
  ( (users_field_data.uid = 19) AND (users_field_data_3.uid != 0) )
  or 
  ( (users_field_data_2.uid = 19) AND (users_field_data_4.uid != 0))
) 

Ok, i've put in some work to find out if i can proove this bug through a test. And it seems i can. Next is to work on a possible solution.

Oops, kept a var_export in there.

@ocastle you are right, the problem is the fact that in the whole QueryGuard it doesn't really consider the grouping of the filters. It should make sure the extra conditions are applied together then.

Updated patch to fix style issues.

Wow, epic work here, @bbrala!

Yeah, sorry appearantly commited my shortcut to run a specfic test :) Doesn't need review though, this is a test only commit.

I'm exploring how to fix this. My current thinking is this;

The problem is in (NotSo)TemporaryQueryGuard.

Currently the applyAccessControls method flattens the fields and remove any reference to any existing conditions. This seem counterproductive. Instead of collecting all filtered fields and just blindly add conditions it should probably do the following:

1. Collect the fields that are used.
2. Check for hardening (applyAccesConditions) in the context of the specifc conditions.
3. Apply the hardening explicitly on the condition in that nested context.

This would probably mean restructuring this code to walk through the conditions recursively and apply adustments to the conditions. This will be hard though. The question becomes, should this issue wait for the other issues that would make this class unneeded. At least #2809177: Introduce entity permission providers and maybe #777578: Add an entity query access API and deprecate hook_query_ENTITY_TYPE_access_alter().

Fixing this is hard and needs to be done carefully not to introduce security issues.

Just been battling with this one, and in my situation is was further complicated by the entity module, which would return
$result[JSONAPI_FILTER_AMONG_ALL] = $allowed; if no query access conditions were returned. This would make jsonapi avoid adding any additional filters to the query. Which actually is a fix for this issue, i.e. you get the correct results back. This means if you have the entity module enabled, you may not see this issue!

However in my case, as I am applying my own entity query access conditions, so I wasn't getting JSONAPI_FILTER_AMONG_ALL. Instead I got the same empty results as others have reported here (although this confused the hell out of me, as I thought the issue was something to do with my custom query access filter because it would work when disabling this part of my code).

Anyway, the workaround I am using for now is to implement my own hook_jsonapi_entity_filter_access() and return [JSONAPI_FILTER_AMONG_ALL => AccessResult::allowed()]; for situations where my query access subscriber is being applied. I just need to ensure that I apply the standard status = 1 filters to the query.

Sidenote: it seems as though these additional access filters only get added if there is a JSON:API filter in the url. See EntityResource.php#L893-898 . Again this initially confused me, I'm sure there's a reason why things are setup this way, but I would have thought we'd want to apply the same access filters to the query regardles of whether there was a JSON:API filter.

I'm having the same issue

Just to summarise my previous semi-rant, a workaround for this issue is to implement something like this:

function mymodule_jsonapi_entity_filter_access(\Drupal\Core\Entity\EntityTypeInterface $entity_type, \Drupal\Core\Session\AccountInterface $account) {
  if ($entity_type->type == 'node') {
    return [JSONAPI_FILTER_AMONG_ALL => AccessResult::allowed()];
  }
}

The caveat of this is that it will avoid applying entity access filters to the entity query. This won't mean that you'll get back results the user shouldn't have access to, but it will mean that you'll get results in the omitted part of the JSON:API response. I.e. if you request 40 items you may only get back 35 due to restricted content (whereas previously you would get back the full 40).
I believe this is correct, but anyone feel free to correct me.

This issue also affects other scenarios -- for me it was trying to get the top-level terms in a hierarchical vocabulary.

With filters defined as:

?filter[parent][condition][path]=parent.id
&filter[parent][condition][operator]=IS%20NULL

... the parent taxonomy term is pulled into the query with a LEFT JOIN, and checked for both "IS NULL" and status = 1. Which makes the query return no rows.

The fix is the same as @Leon Kessler identified -- implement the hook_jsonapi_entity_filter_access to set JSONAPI_FILTER_AMONG_ALL to AccessResult::allowed(), or enable the Entity API module which does this for you.

I've also confirmed that this loads unpublished terms in the underlying query, but these get stripped out of the response.