In my setup, a user can own more entities of a specific content type but the ownership is stored in the entity. The user entity does not 'know' anything about this. I know without context it seems weird but in my case, it made more sense like this instead of having an entity reference field on the user.

Now I'm trying to build an Entity Reference Display for a view so that for certain autocomplete fields I can search for users by either their name or the title of the entities they own. I created a view before that showed all the users and all of their entities so I was hoping I can reuse that field here but it seems like if I set the entity reference field to search by this field as well it just adds this view field's id to the query as if it was a user's field and it gives (of course) column not found SQL exception.

I've also tried to add a join with a view query alter hook but I could not figure out how to get the text entered by the user as that seems to be neither in exposed_raw nor in args.

Any ideas?


Here is how my relationship is set up:

Entity -> Field referencing a paragraph that describes the ownership -> field referencing a user

And here is what I tried based on @stevekeiretsu's comment:

  • I set up a relation to paragraphs that have that field referencing a user
  • Then I added another relation to content that has that field referencing this paragraph.
  • Then I added the Title field of content to the view

The problem is that it still does not show anything. If I set the relations to required I have no results.

  • I think I know what you mean about the 'weird' entityref setup. I have a site where things have a 'place' entityref, rather than places having a 'things' entityref, which I think is the exact same concept, probably for the same reason (shame core doesnt support bidirectional entityrefs). I have created views with this. And yet, I am not quite sure how to answer your question, or even how to explain exactly what I need you to clarify. Does your view have any relationships configured under the 'advanced' section? Commented Apr 9, 2020 at 18:44
  • Well... I did not know about the relationships option... Now I did my research and I can see how this can help me but it is still not working. I edit the question whit what I tried here. Thanks Commented Apr 10, 2020 at 8:58
  • Ah, there is a paragraph entity 'in the middle'. That would complicate things. In my setup the entityref field is directly on the entity. Fundamentally, is this a view of users or a view of content type? Commented Apr 10, 2020 at 15:00
  • Of users as I want to use it for an entity reference field of type user Commented Apr 10, 2020 at 19:10

1 Answer 1


Ok, I found the issue:

users_field_data.created AS users_field_data_created, 
users_field_data.uid AS uid, 
field_member_users_field_data.id AS field_member_users_field_data_id, 
field_number_ownership_paragraphs_item_field_data.nid AS field_number_ownership_paragraphs_item_field_data_nid
    users_field_data as users_field_data
    LEFT JOIN paragraph__field_member as paragraph__field_member 
        users_field_data.uid = paragraph__field_member.field_member_target_id AND 
        paragraph__field_member.deleted = '0'

LEFT JOIN paragraphs_item_field_data as field_member_users_field_data 
    paragraph__field_member.entity_id = field_member_users_field_data.id

LEFT JOIN node__field_number_ownership as field_member_users_field_data__node__field_number_ownership 
    /** HERE **/
    field_member_users_field_data.id = field_member_users_field_data__node__field_number_ownership.field_number_ownership_target_revision_id AND
    field_member_users_field_data__node__field_number_ownership.deleted = '0'

LEFT JOIN node_field_data as field_number_ownership_paragraphs_item_field_data 
    /** AND HERE **/
    field_member_users_field_data__node__field_number_ownership.entity_id = field_number_ownership_paragraphs_item_field_data.vid

    user__roles as user__roles ON users_field_data.uid = user__roles.entity_id   AND user__roles.deleted = '0'
WHERE (user__roles.roles_target_id = 'member')
ORDER BY users_field_data_created DESC

This is the SQL generated by the View. As you can see it is using entity_id against vid and id against target_revision_id in the joins. This may be a bug or my lack of some fundametal drupal stuff. Please let me know if that is the case: Until then, I came up wiht this query alter to solve this problem:

function aci_ssp_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
    // Ifs view id is what i'm looking for
    $tables = $query->getTableQueue();

    $tbl1 = 'field_member_users_field_data__node__field_number_ownership';
    $tables[$tbl1]['join']->leftField = 'revision_id';
    $tables[$tbl1]['join']->configuration['left_field'] = 'revision_id';

    $tbl2 = "field_number_ownership_paragraphs_item_field_data";
    $tables[$tbl2]['join']->field = 'nid';
    $tables[$tbl2]['join']->configuration['field'] = 'nid';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.