Entity reference field search by custom field

Question

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?

Edit:

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.

Answer 1

Ok, I found the issue:

SELECT 
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
FROM
    users_field_data as users_field_data
    LEFT JOIN paragraph__field_member as paragraph__field_member 
        ON 
        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 
    ON 
    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 
    ON 
    /** 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 
    ON 
    /** AND HERE **/
    field_member_users_field_data__node__field_number_ownership.entity_id = field_number_ownership_paragraphs_item_field_data.vid

INNER JOIN 
    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
LIMIT 11 OFFSET 0

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';
}