It might just be my inexperience with the EntityReference Autocomplete and Views but what I have is the following scenario. I have a large pool of users on my site to who have a 'Full Name' field (to note, this is using the core user module, not profile2 or anything else). And I have a content type where I would like for my content editors to reference these users. But instead of having them autocomplete via their username (like btmash), I would prefer them to be able to select users by autocomplete on their name (so they can instead search for Ashok). I created an Entity Reference Source view called 'reference_user_selection':

$view = new view();
$view->name = 'reference_user_selection';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'users';
$view->human_name = 'reference_user_selection';
$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';
/* Field: User: Full Name */
$handler->display->display_options['fields']['field_user_full_name']['id'] = 'field_user_full_name';
$handler->display->display_options['fields']['field_user_full_name']['table'] = 'field_data_field_user_full_name';
$handler->display->display_options['fields']['field_user_full_name']['field'] = 'field_user_full_name';
$handler->display->display_options['fields']['field_user_full_name']['label'] = '';
$handler->display->display_options['fields']['field_user_full_name']['element_label_colon'] = FALSE;
/* Sort criterion: User: Full Name (field_user_full_name) */
$handler->display->display_options['sorts']['field_user_full_name_value']['id'] = 'field_user_full_name_value';
$handler->display->display_options['sorts']['field_user_full_name_value']['table'] = 'field_data_field_user_full_name';
$handler->display->display_options['sorts']['field_user_full_name_value']['field'] = 'field_user_full_name_value';
/* Filter criterion: User: Active */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'users';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = '1';
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: User: Full Name (field_user_full_name) */
$handler->display->display_options['filters']['field_user_full_name_value']['id'] = 'field_user_full_name_value';
$handler->display->display_options['filters']['field_user_full_name_value']['table'] = 'field_data_field_user_full_name';
$handler->display->display_options['filters']['field_user_full_name_value']['field'] = 'field_user_full_name_value';
$handler->display->display_options['filters']['field_user_full_name_value']['operator'] = 'contains';
$handler->display->display_options['filters']['field_user_full_name_value']['exposed'] = TRUE;
$handler->display->display_options['filters']['field_user_full_name_value']['expose']['operator_id'] = 'field_user_full_name_value_op';
$handler->display->display_options['filters']['field_user_full_name_value']['expose']['label'] = 'Full Name (field_user_full_name)';
$handler->display->display_options['filters']['field_user_full_name_value']['expose']['operator'] = 'field_user_full_name_value_op';
$handler->display->display_options['filters']['field_user_full_name_value']['expose']['identifier'] = 'field_user_full_name_value';
$handler->display->display_options['filters']['field_user_full_name_value']['expose']['remember_roles'] = array(
  2 => '2',
  1 => 0,
  6 => 0,
  4 => 0,
  5 => 0,
  3 => 0,
);

/* Display: Entity Reference */
$handler = $view->new_display('entityreference', 'Entity Reference', 'entityreference_1');
$handler->display->display_options['defaults']['title'] = FALSE;
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['pager']['type'] = 'some';
$handler->display->display_options['pager']['options']['items_per_page'] = '20';
$handler->display->display_options['pager']['options']['offset'] = '0';
$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_user_full_name' => 'field_user_full_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['row_options']['inline'] = array(
  'field_user_full_name' => 'field_user_full_name',
  'uid' => 'uid',
);
$handler->display->display_options['row_options']['default_field_elements'] = FALSE;
$handler->display->display_options['defaults']['row_options'] = FALSE;

And in the field configuration, I configure field_work_entry_user to filter by the above entity reference view. When I get to the page, I see that the callback gets made and it does call on the view, but I never get back any results if I try to enter part of the full name. But if I started to enter an Entity ID (lets say 4), I would start getting back results of users with the full name in it for the user (so it would return entities with uid 4, 14, 24, 34, 40, and so on).

I took a look at the query being generated and this is what I got:


SELECT users.uid AS uid, field_data_field_user_full_name.field_user_full_name_value AS field_data_field_user_full_name_field_user_full_name_value, 'user' AS field_data_field_user_full_name_user_entity_type FROM users users LEFT JOIN field_data_field_user_full_name field_data_field_user_full_name ON users.uid = field_data_field_user_full_name.entity_id AND (field_data_field_user_full_name.entity_type = 'user' AND field_data_field_user_full_name.deleted = '0') WHERE ((( (users.uid LIKE '%s%' ESCAPE '\\') ))AND(( (users.status <> '0') AND (field_data_field_user_full_name.field_user_full_name_value LIKE '%%' ESCAPE '\\') ))) ORDER BY field_data_field_user_full_name_field_user_full_name_value ASC LIMIT 10 OFFSET 0

The key parts are:

(users.uid LIKE '%s%' ESCAPE '\\')

and

(field_data_field_user_full_name.field_user_full_name_value LIKE '%%' ESCAPE '\\')

Basically, the autocomplete search is being performed on the user id column instead of the full name value column. Any help is debugging this behavior (or perhaps telling me my view is wrong and it should be set up some other way) would be very helpful.

Comments

btmash’s picture

Looking at what dpm provides, me, there is a databasecondition object that gets added and this is where the (users.uid LIKE '%s%' ESCAPE '\\') condition is coming in from. I cannot modify this from a views query alter and right now, the only way around it is something like the following:

function my_module_views_query_alter(&$view, &$query) {
  if ($view->name == 'reference_user_selection') {
    $pattern = arg(7);
    if (!empty($pattern) && is_object($query->where[0]['conditions'][0]['field'])) {
      $query->where[0]['conditions'][0]['field'] = $query->fields['field_data_field_user_full_name_field_user_full_name_value']['table'] .'.'. $query->fields['field_data_field_user_full_name_field_user_full_name_value']['field'];
      $query->where[0]['conditions'][0]['operator'] = 'LIKE';
      $query->where[0]['conditions'][0]['value'] = '%'. check_plain($pattern) .'%';
    }
  }
}

It is somewhat ugly given the kind of things this small snippet is doing (getting the args since they are not available to the view, essentially removing the databaseconnection object, etc).

itaces’s picture

I'm also having this problem.

I was thinking about trying the patch listed at:
http://drupal.org/node/1691612

DrMiaow’s picture

I'm wondering if this might be related to our issue

http://drupal.org/node/1806078

Which looks like a malformation of the LIKE query on the users table.

krlucas’s picture

Status: Active » Needs review
StatusFileSize
new988 bytes

I ran into this as well. The entityreference Views autocomplete works with entity "properties" like title but fails (maybe only in some cases) with field api fields. Has something to do with the way Views is forced to deal with field api. See merlin's comment here: http://drupal.org/node/1160706#comment-4916402

Anyways, I've attached a mildly tested patch.

DrMiaow’s picture

Some more information - if we change this from autocomplete to optionselect, we don't get the error.

btmash’s picture

@krlucas, thanks - I'll try it out sometime this week.

@DrMiaow - I know about the option select and while it works, its also not an ideal solution as I have a site with hundreds of users (and since a user_load operation is performed, it ends up being a fairly expensive page load).

DrMiaow’s picture

I agree completely - same issue. I'm just wondering if that information helps the developers work out what the actual issue might be.

Randeep.Singh’s picture

help please i have strainge problem with "entity reference"...

i have two content types say: A and B.

content type "A" has field "ID number" (field type = serial)

and content type "B" has field "id number" (field type = "entity reference")(autocomplete).

i have ENTITY SELECTION mode set to view which contains "id number" field of content type "A".

now whenever i type "id number" in content type"B" (entity reference) it displays ONE LESS OF THAT NUMBER . i.e if i type "id number" = 4 it returns entity reference result "3". any clue why this is happning????

note i have 3 nodes under content type "A" with ID number (serial) number 1,2 and 3

also it seems EntityReference Autocomplete for Views on fields performs LIKE search on uid instead of field.

atlea’s picture

Status: Needs review » Reviewed & tested by the community

Hi, I have reviewd and tested patch in #4 and it works great. Thanks!

But note that this only works if the field can be used by views as a filter criteria. This might be obvious, but the settings lets you select from all fields added - and not all of them can be used as filters. I ran into this as I tried to use a "computed fields"-field.

Atle

krlucas’s picture

@atlea thanks for reviewing the patch! I will test your issue though I'm not sure if it is directly related to this patch. Regardless, next step is to write a test.

mkhamash’s picture

I have also reviewed and tested patch in #4 and it works great.

Thanks.

skylord’s picture

Patch from #4 works OK - thank you (the same use case - search by users fields)!

acb’s picture

CONFIRM THIS WORKS!

Thank you.

loopduplicate’s picture

Patch from #4 worked for me too. Autocomplete now works on fields and properties.

dave reid’s picture

Status: Reviewed & tested by the community » Closed (duplicate)

I believe this is a duplciate of #1691612: Autocomplete is searching only on entity properties (not fields) which is also RTBC.

marcus178’s picture

Patch #4 works for me too

wolf_22’s picture

That patch essentially works but what about making the field display whatever the view keys from? I.e. - the patch worked for me in making the view auto-complete from field_last_name but in the display of the final node, it shows username.

Is there a way to fix that or is this an issue of being designed this way?

Renee S’s picture

Wolf_22: You need to use a view on the display mode as well, using something like this: http://drupal.org/project/entityreference_views_formatter (or, more comprehensive solution, http://drupal.org/project/custom_formatters).

rogerrogers’s picture

#4 works for me too.

trumanru’s picture

Current dev version (2013-02-01) works fine with autocomplete LIKE search on fields from view.

andrew_lokko’s picture

Thanks for the patch, solved my problem!

Johann Wagner’s picture

When will this be released in stable ?

jontyler’s picture

The patch from #4 does not seem to be included in the current -dev version. I'm still seeing this issue where autocomplete only searches on the title of a node.

jontyler’s picture

Please ignore my previous comment. My install was still using the stable version (moved to *.bak), not the dev version I had replaced it with.

achton’s picture

I'm pretty sure this is fixed in dev, but not via the patch in #4. Maybe someone else can spot which issue might be responsible so we can shut this down: http://drupalcode.org/project/entityreference.git/shortlog/refs/heads/7....

loopduplicate’s picture

Issue summary: View changes

This issue was fixed in #1691612: Autocomplete is searching only on entity properties (not fields).
It works for me as of 7.x-1.1

supriyarajgopal’s picture

Patch in #8 in https://www.drupal.org/node/1898290 worked for me with the latest dev version of this module.

Regards,
Supriya Rajgopal