Entity Reference fails with Users view containing Profile2 search fields [#2478353]

We needed to create an autocomplete field to allow administrators to associate a User to an entity. To accomplish this, we built an Entity Reference view including three fields:

* username from User
* first name and last name from Profile2

All three were marked as searchable fields (set under Entity Reference list settings). The Entity Reference view was added to an entity with an Entity Reference field.

All of this was completed without issue. However, when attempting to use the field, we get no autocomplete results.

Examining the query built in /entityreference/views/entityreference_plugin_display.inc, it does not look like Profile2 fields are getting the correct table prefixes in the WHERE clause. We're seeing things like this:

[0] => Array
  (
    [field] => field_data_field_last_name.field_last_name_value
    [value] => %adam%
    [operator] => LIKE
  )
[1] => Array
  (
    [field] => field_data_field_first_name.field_first_name_value
    [value] => %adam%
    [operator] => LIKE
  )

[2] => Array
  (
    [field] => users.name
    [value] => %adam%
    [operator] => LIKE
  )

With references to field_data_field_first_name, which does not match what we see in the query built in Views:

SELECT profile_users.pid AS profile_users_pid, users.name AS users_name, users.uid AS uid, 'profile2' AS field_data_field_first_name_profile2_entity_type, 'profile2' AS field_data_field_last_name_profile2_entity_type
FROM 
{users} users
INNER JOIN {profile} profile_users ON users.uid = profile_users.uid AND profile_users.type = 'main'
LIMIT 10 OFFSET 0

We then ran an experiment where we used Profiles as our base table for building the view rather than Users. Using this approach, the Entity Reference autocomplete field worked perfectly. We were able to search on username, first name, and last name in the autocomplete, and every time it returned results.

While it is great that an alternative method works, it is not the right approach for us. We really need to capture uid as the field entry, not pid. This should work with Users as the base table, and I'm guessing this is one aspect of the Profile2 views relationships that is still not working correctly.

This issue is related to a number of other issues including:
* Reverse entity relationships for Views integration (https://www.drupal.org/node/1066398)
* Support views relationship "user -> profile" (https://www.drupal.org/node/1047904)
* Improve the views integration (https://www.drupal.org/node/986430)
* Autocomplete is searching only on entity properties (not fields) (https://www.drupal.org/node/1691612)

We are using the following modules:
Entity Reference 7.x-1.1
Entity API 7.x-1.6
Profile2 7.x-1.x-dev
Views 7.x-3.10

Comment	File	Size	Author
#6	entityreference-2478353-6-searching-on-field-with-relationship.patch	1.18 KB	james.williams
#6	7.x-1.x: PHP 7.4 & MySQL 8, D7 13 pass

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

ron_s CreditAttribution: ron_s commented 27 April 2015 at 06:03

Here's a very simple view that works in Views, but not the Entity Reference autocomplete. This includes field_first_name and field_last_name as Profile2 main profile fields, and Users is the base table. If field_first_name and field_last_name are removed as Search fields, the autocomplete functions correctly.

-------------------------------

$view = new view();
$view->name = 'entityreference_user_profile_view';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'users';
$view->human_name = 'entityreference_user_profile_view';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['access']['perm'] = 'access user profiles';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Relationship: User: Profile */
$handler->display->display_options['relationships']['profile']['id'] = 'profile';
$handler->display->display_options['relationships']['profile']['table'] = 'users';
$handler->display->display_options['relationships']['profile']['field'] = 'profile';
$handler->display->display_options['relationships']['profile']['required'] = TRUE;
$handler->display->display_options['relationships']['profile']['bundle_types'] = array(
  'main' => 'main',
);
/* Field: Profile: First Name */
$handler->display->display_options['fields']['field_first_name']['id'] = 'field_first_name';
$handler->display->display_options['fields']['field_first_name']['table'] = 'field_data_field_first_name';
$handler->display->display_options['fields']['field_first_name']['field'] = 'field_first_name';
$handler->display->display_options['fields']['field_first_name']['relationship'] = 'profile';
$handler->display->display_options['fields']['field_first_name']['label'] = '';
$handler->display->display_options['fields']['field_first_name']['element_type'] = '0';
$handler->display->display_options['fields']['field_first_name']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_first_name']['element_wrapper_type'] = '0';
$handler->display->display_options['fields']['field_first_name']['element_default_classes'] = FALSE;
$handler->display->display_options['fields']['field_first_name']['type'] = 'text_plain';
/* Field: Profile: Last Name */
$handler->display->display_options['fields']['field_last_name']['id'] = 'field_last_name';
$handler->display->display_options['fields']['field_last_name']['table'] = 'field_data_field_last_name';
$handler->display->display_options['fields']['field_last_name']['field'] = 'field_last_name';
$handler->display->display_options['fields']['field_last_name']['relationship'] = 'profile';
$handler->display->display_options['fields']['field_last_name']['label'] = '';
$handler->display->display_options['fields']['field_last_name']['element_type'] = '0';
$handler->display->display_options['fields']['field_last_name']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_last_name']['element_wrapper_type'] = '0';
$handler->display->display_options['fields']['field_last_name']['element_default_classes'] = FALSE;
$handler->display->display_options['fields']['field_last_name']['type'] = 'text_plain';
/* Field: User: Name */
$handler->display->display_options['fields']['name']['id'] = 'name';
$handler->display->display_options['fields']['name']['table'] = 'users';
$handler->display->display_options['fields']['name']['field'] = 'name';
$handler->display->display_options['fields']['name']['label'] = '';
$handler->display->display_options['fields']['name']['alter']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['name']['alter']['ellipsis'] = FALSE;
$handler->display->display_options['fields']['name']['element_type'] = '0';
$handler->display->display_options['fields']['name']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['name']['element_wrapper_type'] = '0';
$handler->display->display_options['fields']['name']['element_default_classes'] = FALSE;
$handler->display->display_options['fields']['name']['link_to_user'] = FALSE;

/* Display: Entity Reference */
$handler = $view->new_display('entityreference', 'Entity Reference', 'entityreference_1');
$handler->display->display_options['defaults']['title'] = FALSE;
$handler->display->display_options['pager']['type'] = 'some';
$handler->display->display_options['defaults']['style_plugin'] = FALSE;
$handler->display->display_options['style_plugin'] = 'entityreference_style';
$handler->display->display_options['style_options']['search_fields'] = array(
  'field_first_name' => 'field_first_name',
  'field_last_name' => 'field_last_name',
  'name' => 'name',
);
$handler->display->display_options['defaults']['style_options'] = FALSE;
$handler->display->display_options['defaults']['row_plugin'] = FALSE;
$handler->display->display_options['row_plugin'] = 'entityreference_fields';
$handler->display->display_options['defaults']['row_options'] = FALSE;

Comment #2

nithinkolekar CreditAttribution: nithinkolekar commented 28 April 2015 at 10:30

same here.. following.

Comment #3

niknak

Godarville, 🇧🇪

CreditAttribution: niknak commented 5 May 2015 at 13:46

same error for me

Comment #4

heinz.wegener CreditAttribution: heinz.wegener as a volunteer commented 21 May 2015 at 21:52

This solution worked for me. I'm using the Autocomplete widget on the entity reference field (not the "tagging" autocomplete widget). It requires a custom module (I'm calling it "custom"), and it does not require an Entity Reference view either. In this example, I am referring to a content type with a machine name of "content_type", and the user reference field is "field_user".

First register an autocomplete path using hook_menu:

function custom_menu() {
  $items = array();

  $items['custom/user-reference/autocomplete'] = array(
    'title' => 'Autocomplete for user reference field',
    'page callback' => 'custom_autocomplete_users',
    'access arguments' => array('access content'),
    'type' => MENU_CALLBACK
  );

  return $items;
}

Next we add the autocomplete callback function (assuming the profile type's machine name is the default, "main"):

function custom_autocomplete_users($string) {
  $matches = array();
  $query = db_select('users', 'u');
  $query->condition('u.status', 0, '<>');
  $query->addField('u', 'uid');
  
  $query->join('profile', 'p', 'p.uid = u.uid');
  $query->condition('p.type', 'main', 'LIKE');
  
  $query->join('field_data_field_first_name', 'f', 'f.entity_id = p.pid');
  $query->condition('f.entity_type', 'profile2', 'LIKE');
  $query->addField('f', 'field_first_name_value', 'first');
  
  $query->join('field_data_field_last_name', 'l', 'l.entity_id = p.pid');
  $query->condition('l.entity_type', 'profile2', 'LIKE');
  $query->addField('l', 'field_last_name_value', 'last');

  $or = db_or();
  $or->condition('f.field_first_name_value', '%'.$string.'%', 'LIKE');
  $or->condition('l.field_last_name_value', '%'.$string.'%', 'LIKE');
  $query->condition($or);

  $query->range(0,10);

  $result = $query->execute();

  foreach ($result as $data) {
    $matches[$data->first . ' ' . $data->last . ' ('.$data->uid.')'] = check_plain($data->first . ' ' . $data->last);
  }
  drupal_json_output($matches);
}

Finally, we change the autocomplete path using hook_form_alter (assuming the language is undetermined (LANGUAGE_NONE)):

function custom_form_alter(&$form, $form_state, $form_id) {
  switch ($form_id) {
    case 'content_type_node_form':
      $form['field_user'][LANGUAGE_NONE][0]['target_id']['#autocomplete_path'] = 'custom/user-reference/autocomplete';
      break;
  }
}

Let me know if that helps.

Comment #5

weri CreditAttribution: weri at Previon Plus AG commented 30 October 2015 at 09:53

I can confirm the problem. Now we use the profile as base entity for the view and not the user. This is a possible workaround for us, but nor for everyone.

I'm not sure it's an alias handling problem described in '#2293437: Autocomplete on user entity with related profile fields breaks on upgrade to 7.x-1.1 ' and a bug in the entityreference module or does the profile2 module not correct define the alias table names.

Comment #6

james.williams CreditAttribution: james.williams at ComputerMinds commented 9 May 2022 at 14:19

Project:	Profile 2	» Entity reference
Status:	Active	» Needs review

File	Size
entityreference-2478353-6-searching-on-field-with-relationship.patch	1.18 KB
7.x-1.x: PHP 7.4 & MySQL 8, D7 13 pass

The fix in #1898290: Autocompletes for Views that have Fields with Relationship broken, which was committed & released after the last comment here, probably fixes a majority of similar cases. But I did find a case where results would be empty: if searching on a field using a relationship, which also existed on the base entity/table. I believe this patch fixes that - and I believe it's an issue in Entity reference itself, so affects any entity type, not just those of Profile2. It might be more appropriate to handle this in its own newer issue, I'm not sure.