Views hides nodes with a blank entity reference field

[kswan]

Description of the bug

Views incorrectly filters out content where an entity reference field is blank in some specific situations. The conditions are:

• The view has a relationship configured to the Referenced Entity.
• A module is enabled that implements "node_grants".

This was originally reported in the content_access issue queue (backdrop-contrib/content_access#30), but I believe it is actually a core issue.

Steps To Reproduce

To reproduce the behavior:

1. Setup a Content Type with an entity reference field that targets a node.
2. Make sure you have a non-admin (not user 1) account with Bypass content access control permission is disabled.
3. Add some content to the content type where some content includes the entity reference and some does not.
4. Create a view filtered by the content type setup earlier, displayed as fields in a table. Including the entity reference field.
5. Add a Relationship to the view for the Entity Reference - Referenced Entity. Leave the "Require this relationship" unchecked.
6. Load the view from the non-admin account. At this point, you should see all content including the nodes with and without the entity reference field populated.
7. Enable any module that implements "node_grants", for example, content_access.
8. Rebuild Node access permissions.
9. Load the view again from the non-admin account.
10. This time you will only see the content where the entity reference field is populated. The view should include the nodes where the entity reference field is blank.

Actual behavior

Only the content where the entity reference field is populated is displayed.

Expected behavior

The same content shown in step 6 should be displayed.

Additional information

Add any other information that could help, such as:

• Backdrop CMS version: 1.30.0
• Web server and its version: Apache/2.4.58 (Ubuntu)
• PHP version: 8.3
• Database sever (MySQL or MariaDB?) and its version: 8.0.41-0ubuntu0.24.04.1
• Operating System and its version: Ubuntu 24.04

The views relationship configured to the Referenced Entity causes the following to be appended to the WHERE clause of the Views query:

AND ( EXISTS  (SELECT `na`.`nid` AS `nid`
FROM 
{node_access} `na`
WHERE (( (`na`.`gid` IN  ('0')) AND (`na`.`realm` = 'all') )OR( (`na`.`gid` IN  ('2')) AND (`na`.`realm` = 'content_access_author_unpublished') )OR( (`na`.`gid` IN  ('2')) AND (`na`.`realm` = 'content_access_author') )OR( (`na`.`gid` IN  ('222', '333', '337')) AND (`na`.`realm` = 'content_access_rid') ))AND (`na`.`grant_view` >= '1') AND (node_field_data_field_reference.nid = na.nid) )) 

This is only valid when the node_field_data_field_reference.nid is not null.

[olafgrabienski]

I've reproduced the steps from the issue description and can confirm the issue: Visitors see only the content of a view, where the content item's entity reference field has a value. After applying the PR to my test site, they see all the content. So, the fix works for me.

@kswan – One of the steps is to enable a module that implements "node_grants". Does it make sense to test the issue with other modules than Content Access, and which? (Or have you tested this already?)

[kswan]

@olafgrabienski Thank you for testing the PR.
After searching backdrop-contrib for modules that implement node_grants, these are affected.
Content Access - Confirmed using STR
Simple Access - Confirmed using STR
Content view access - Confirmed using STR
ACL - Confirmed using STR
Node access node reference - Confirmed using STR
View Unpublished - Confirmed using STR
Simple Access Grants - Confirmed using STR
Organic groups subgroups - Confirmed using STR
Node access user reference - Confirmed using STR
Organic Groups/og_access - Confirmed using STR
Taxonomy Access Control - Confirmed using STR
Taxonomy Access Control Lite - Confirmed using STR
Domain Access - Confirmed using STR
Access by Term (ABT) - Confirmed using STR
Forum Access - Confirmed using STR

[argiepiano]

I'm not able to reproduced. I have followed the steps very carefully, and double checked everything. I did leave the "require this relationship" unchecked. Is there a missing step?

Snapshots:

[olafgrabienski]

Is there a missing step?

Interesting! I was able to reproduce it with exactly the steps from the issue description. (The first time I wasn't, because I forgot to look at the view without the "Bypass content access control" permission.)

[kswan]

If you are logged in as User 1 or an account with "Bypass content access control", the issue doesn't' occur. I'll add the user 1 factor to the STR.

[argiepiano]

Still not able to reproduced. I'm logging in with another user (with editor role). Still able to see all nodes. What am I missing?

[argiepiano]

I'm pasting my test view here, in case one of you wants to import it and test it on your end. The host content type is called node_with_ref and the reference field is called field_ref, referencing content type page.

{
    "_config_name": "views.view.node_with_ref",
    "name": "node_with_ref",
    "description": "",
    "tag": "",
    "disabled": false,
    "base_table": "node",
    "human_name": "node with ref",
    "core": "1.30.x-dev",
    "display": {
        "default": {
            "display_title": "Default",
            "display_plugin": "default",
            "display_options": {
                "query": {
                    "type": "views_query",
                    "options": []
                },
                "access": {
                    "type": "perm",
                    "perm": "access content"
                },
                "cache": {
                    "type": "none"
                },
                "exposed_form": {
                    "type": "basic"
                },
                "pager": {
                    "type": "none",
                    "options": {
                        "items_per_page": ""
                    }
                },
                "style_plugin": "table",
                "row_plugin": "fields",
                "fields": {
                    "title": {
                        "id": "title",
                        "table": "node",
                        "field": "title",
                        "alter": {
                            "alter_text": 0,
                            "make_link": 0,
                            "absolute": 0,
                            "trim": 0,
                            "word_boundary": 0,
                            "ellipsis": 0,
                            "strip_tags": 0,
                            "html": 0
                        },
                        "hide_empty": 0,
                        "empty_zero": 0,
                        "link_to_node": 1
                    },
                    "field_ref": {
                        "id": "field_ref",
                        "table": "field_data_field_ref",
                        "field": "field_ref",
                        "relationship": "none",
                        "group_type": "group",
                        "ui_name": "",
                        "label": "Ref",
                        "exclude": 0,
                        "alter": {
                            "alter_text": 0,
                            "text": "",
                            "make_link": 0,
                            "path": "",
                            "absolute": 0,
                            "external": 0,
                            "replace_spaces": 0,
                            "path_case": "none",
                            "trim_whitespace": 0,
                            "alt": "",
                            "rel": "",
                            "link_class": "",
                            "prefix": "",
                            "suffix": "",
                            "target": "",
                            "nl2br": 0,
                            "max_length": "",
                            "word_boundary": 1,
                            "ellipsis": 1,
                            "more_link": 0,
                            "more_link_text": "",
                            "more_link_path": "",
                            "strip_tags": 0,
                            "trim": 0,
                            "preserve_tags": "",
                            "html": 0
                        },
                        "element_type": "",
                        "element_class": "",
                        "element_label_type": "",
                        "element_label_class": "",
                        "element_label_colon": 1,
                        "element_wrapper_type": "",
                        "element_wrapper_class": "",
                        "element_default_classes": 1,
                        "empty": "",
                        "hide_empty": 0,
                        "empty_zero": 0,
                        "hide_alter_empty": 1,
                        "click_sort_column": "target_id",
                        "type": "entityreference_label",
                        "settings": {
                            "bypass_access": 0,
                            "link": 0
                        },
                        "group_column": "target_id",
                        "group_columns": [],
                        "group_rows": true,
                        "delta_limit": "all",
                        "delta_offset": 0,
                        "delta_reversed": false,
                        "delta_first_last": false,
                        "multi_type": "separator",
                        "separator": ", ",
                        "field_api_classes": 0
                    },
                    "title_1": {
                        "id": "title_1",
                        "table": "node",
                        "field": "title",
                        "relationship": "field_ref_target_id",
                        "group_type": "group",
                        "ui_name": "",
                        "label": "Title",
                        "exclude": 0,
                        "alter": {
                            "alter_text": 0,
                            "text": "",
                            "make_link": 0,
                            "path": "",
                            "absolute": 0,
                            "external": 0,
                            "replace_spaces": 0,
                            "path_case": "none",
                            "trim_whitespace": 0,
                            "alt": "",
                            "rel": "",
                            "link_class": "",
                            "prefix": "",
                            "suffix": "",
                            "target": "",
                            "nl2br": 0,
                            "max_length": "",
                            "word_boundary": 1,
                            "ellipsis": 1,
                            "more_link": 0,
                            "more_link_text": "",
                            "more_link_path": "",
                            "strip_tags": 0,
                            "trim": 0,
                            "preserve_tags": "",
                            "html": 0
                        },
                        "element_type": "",
                        "element_class": "",
                        "element_label_type": "",
                        "element_label_class": "",
                        "element_label_colon": 1,
                        "element_wrapper_type": "",
                        "element_wrapper_class": "",
                        "element_default_classes": 1,
                        "empty": "",
                        "hide_empty": 0,
                        "empty_zero": 0,
                        "hide_alter_empty": 1,
                        "link_to_node": 1
                    }
                },
                "filters": {
                    "status": {
                        "value": 1,
                        "table": "node",
                        "field": "status",
                        "id": "status",
                        "expose": {
                            "operator": false
                        },
                        "group": 1
                    },
                    "type": {
                        "id": "type",
                        "table": "node",
                        "field": "type",
                        "value": {
                            "node_with_ref": "node_with_ref"
                        }
                    }
                },
                "sorts": {
                    "created": {
                        "id": "created",
                        "table": "node",
                        "field": "created",
                        "order": "DESC"
                    }
                },
                "title": "node with ref",
                "relationships": {
                    "field_ref_target_id": {
                        "id": "field_ref_target_id",
                        "table": "field_data_field_ref",
                        "field": "field_ref_target_id",
                        "relationship": "none",
                        "group_type": "group",
                        "ui_name": "",
                        "label": "Content referenced from field_ref",
                        "required": 0
                    }
                }
            }
        },
        "page": {
            "display_title": "Page",
            "display_plugin": "page",
            "display_options": {
                "query": {
                    "type": "views_query",
                    "options": []
                },
                "path": "node-with-ref"
            }
        }
    }
}

[argiepiano]

OK, now I'm able to reproduce. I'll check the fix later.

[argiepiano]

The PR fixes this issue, but I'm wondering if this is the ideal way to solve this.

The problem here is created because Views uses LEFT JOIN to join the node table when you have an entity reference field that references nodes as a relationship. And since the joined table is node, content access adds the EXISTS clause to check that the user has access to view the referenced node. Of course, the issue is that the LEFT JOIN has the potential to return null if the entity reference field is empty. My concern is that we are adding that OR clause to the query in _node_query_node_access_alter() only to accommodate the View issue created by using LEFT JOIN. That OR will be added to ALL queries involving nodes, regardless of whether they are part of a View or not, and I'm not sure if this may have other unintended effects.

That said, I really can't think of another way to solve this at the moment.

[laryn]

This issue may be relevant. I would love someone else to look over what I've started, based on the Drupal work, but I ran aground trying to get things working (including tests):

#6421

[argiepiano]

This issue may be relevant.

Brilliant! I think that seems relevant, and a quick glance at the D10 PR checks for left joins, which is what this problem is about. So, that issue seems like a duplicate of this one. @kswan any chance you can check the PR in the other issue to see if we can make it work?

[kswan]

Yes, I agree this issue is a duplicate of #6421. Our PRs both apply to the same section of code. At a quick scan I don't understand Laryn's PR, but I'll dig into it and see what I can determine.

[laryn]

@kswan Sounds good -- I'll tag you in the other issue as working on it. Feel free to start fresh based on the Drupal work. My IDE always tries to apply code fixes which can sometimes muddy up a PR like that one.