I know that autocomplete widget with profile2 searching problem already solved in 7.x-1.x-dev
but I found other problem in user views with profile relationships.
when I made profile views with user relationships there is no problem.
but when I made user views with profile relationships I coudn't search any related value with profile in autocomplete widget.
only worked in users original values.

there are steps this bug report.
1.make user views with profile relation.
set entity reference display with profile search fields.
2.make field that is referenced entity. and set user , views mode. and set autocomplete widget.

in these settings, you can't use autocomplete widget. searching doesn't work.

#8 selection.patch1.26 KBparanojik
PASSED: [[SimpleTest]]: [MySQL] 124 pass(es). View
Members fund testing for the Drupal project. Drupal Association Learn more


Jody Lynn’s picture

I found that if you make profile2 fields searchable that the autocomplete returns nothing (not even matches from other fields).

My view lists og memberships, related to users, related to profiles.

As long as I don't make the profile2 fields searchable, the autocomplete works.

darrylri’s picture

I'd like to add to this. My case is for a club website, with thousands of users. There's a profile2 profile that includes a membership number, first and last names. I'd like to have the autocomplete be able to search on a result that looks like "memno last, first - username", to allow my admins the greatest flexibility in choosing a user reference.

As Jody Lynn states, if you check any profile2 field as searchable, then the autocomplete doesn't find anything.

I've also tried taking the user name field and rewriting it to include the fields from my profile2. The profile2 fields are otherwise excluded from display, and are not marked searchable. In this case, autocomplete works for specifically the user name, but not for any of the other fields that were added via field rewrite.

Jody Lynn’s picture

darrylri I tried the same thing.

Searching the profile fields breaks the query somehow. It will take some expert views query debugging to solve it.

Ovation1357’s picture

I'm hitting this problem too and it's causing a bit of a headache in an urgent project I'm working on - I could probably use the deprecated 'reference' module but I thought I'd stick with entity reference in the name of progress ( and to embrace the amazing flexibility of what 'entities' should be ).

I've spent rather more time than I can afford poking through the code and adding dsm()s of various arrays and the like and I've made a little progress but sadly not enough - maybe this will mean more to someone who understands this, particularly someone who understands OO because my brain exploded looking at the Views stuff!

So I've got a user view with an entityreference display which shows User:Uid, User:name, profile2:first_name and profile2:last_name - I've got relationships set up as user:profile and "Profile (User) profile:user Uid" - The entityreference search is configured to search all of the available fields including the Uid/Name from the Users table and my profile2 fields for First and Last name.
NB:I've also tried a Profile based View which also fails to autocomplete but I've done no diagnostics on that

The View works fine in its own preview, but in preview mode you can't add an autocomplete search term so I always see all matching results (and using this view as an entity reference select list works fine too). When I try it via an Autocomplete field in a node, I can only get results which match the Uid or the Name of the user. Once I uncovered the SQL being used it became obvious why the query doesn't match:

SELECT users.uid AS uid, users.name AS users_name, profile_users.pid AS profile_users_pid, 'profile2' AS field_data_field_first_name_profile2_entity_type, 'profile2' AS field_data_field_first_name_preferred__profile2_entity_type, 'profile2' AS field_data_field_last_name_profile2_entity_type, 'list_service_users:entityreference_2' AS view_name
users users
LEFT JOIN profile profile_users ON users.uid = profile_users.uid AND profile_users.type = 'personal_assistant'
INNER JOIN users users_profile ON profile_users.uid = users_profile.uid
INNER JOIN users_roles users_roles ON users.uid = users_roles.uid
WHERE ((( (users.uid LIKE 'Jonny%' ESCAPE '\\') OR (users.name LIKE 'Jonny%' ESCAPE '\\') OR (profile_users.pid LIKE 'Jonny%' ESCAPE '\\') OR (profile_users.pid LIKE 'Jonny%' ESCAPE '\\') OR (profile_users.pid LIKE 'Jonny%' ESCAPE '\\') ))AND(( (users.status <> '0') AND (users_roles.rid = '4') )))

Look at the WHERE clause - The first two are fine, checking 'users.uid LIKE...' and 'users.name LIKE...' but then it all falls down: "profile_users.pid LIKE 'Jonny%'" - Well that's never going to match in a million years! The pid is the Profile ID and the field we care about is in a table such as 'field_data_field_first_name' which isn't even JOINed in this query.

Okay: So I thought to myself let's compare a working query with a broken one... The result is weird to me: Even the SQL query dumped out by Views in preview mode does not include a JOIN to the tables containing the field data and yet it is present in the View output!!!!!
The only difference between the Views preview SQL and the entityreference_autocomplete SQL is the addition of the WHERE clause by the latter.

I enabled the general_log on my local MySQL database to watch the queries in real-time. Despite all caching being disabled on my site I see pages and pages of cache table activity when I run this view and I think there may be a separate query running in the View which retrieves the values from the field tables. In my log I see this before the main query:
139 Query SELECT cid, data, created, expire, serialized FROM cache_views WHERE cid IN ('views_data:field_data_field_first_name:en')
139 Query SELECT cid, data, created, expire, serialized FROM cache_views WHERE cid IN ('views_data:field_data_field_last_name:en')

So I think that Views is getting the field data before it executes the main query and therefore doesn't bother JOINing the relevant tables, but then must pluck out the required data from the results of those queries above based on the results of the main query.

There's a situation in entityreference/views/entityreference_plugin_display.inc which is confusing me:

 75       // Build the condition using the selected search fields
 76       foreach ($style_options['search_fields'] as $field_alias) {
 77         if (!empty($field_alias)) {
 78           // Get the table and field names for the checked field
 79           dsm($field_alias);
 80           dsm($this->view->query->fields);
 81           $field = $this->view->query->fields[$this->view->field[$field_alias]->field_alias];
 82           dsm($field);
 83           // Add an OR condition for the field
 84           $conditions->condition($field['table'] . '.' . $field['field'], $value, 'LIKE');
 85         }
 86       }

NB:The dsm() entries are my additions
When field_alias is set to 'uid' or 'name' then the respective Array in $field seems sensible - e.g.

field (String, 3 characters ) uid
table (String, 5 characters ) users
alias (String, 3 characters ) uid

But for the profile2 fields such as first_name the $fields entry seems wrong:

field (String, 3 characters ) pid
table (String, 13 characters ) profile_users
alias (String, 17 characters ) profile_users_pid

This is where the incorrect WHERE clause is being determined.

When I look at $this->view->query->fields the source array there are arrays containing field/table/alias for uid, users_name, profile_users_pid, field_data_field_first_name_profile2_entity_type and so on...
The field_data_field_first_name_profile2_entity_type contains:

field (String, 10 characters ) 'profile2'
table (NULL)
alias (String, 48 characters ) field_data_field_first_name_profile2_entity_type

Huh? So the table is NULL???

Actually I can see that for all my profile2 fields that at line 81 above $field is actually getting populated (as block qouted above) with the contents of the profile_users_pid entry.

My expectation of a working View is that all the necessary fields will be call into play by JOINing their tables in the main Views SQL query - clearly this is not the case here and I don't understand Views well enough to understand what its doing.
I can't even point a finger right now at any specific module for this problem - I've failed to determine whether it's a bug in profile2, entityreference or Views itself. From the tutorials I've read about creating custom entities, part of the process is describing your entity to Views - therefore I believe that the profile2 module has some responsibility for telling Views how interpret its entities (although there's no mention of views anywhere in that module). So perhaps entityreference is just a victim here?

I don't know.... Can anyone shed further light on this??

paulmartin84’s picture

Ran into this aswell, Ovation1357 your analysis really does help to get to where the problem lies but I've yet to solve it still.

I've changed things around a little and now the search fields do show for me in a dsm of $this->view->query but still not returning any results for me.

      // Multiple search fields are OR'd together
      $db_or = $this->view->query->set_where_group('OR');
      // Build the condition using the selected search fields
      foreach ($style_options['search_fields'] as $field_alias) {
        if (!empty($field_alias)) {

          // Get the table and field names for the checked field.
          if (empty($this->view->field[$field_alias]->field_info)) {
            $field = $this->view->query->fields[$this->view->field[$field_alias]->field_alias];
          else {
            $this->view->query->add_field($this->view->field[$field_alias]->options['table'], $this->view->field[$field_alias]->real_field, $this->view->field[$field_alias]->options['field'], array());
            $field = $this->view->query->fields[$this->view->field[$field_alias]->options['field']];

          $this->view->query->add_where( $db_or , $field['table'] . '.' . $field['field'] , $value , 'LIKE' );


brogdawg’s picture

Issue summary: View changes

Has anyone found a solution for this? Applying the patch in https://www.drupal.org/node/2293437 does fix the autocomplete from breaking when adding a search on profile fields, but the autocomplete still doesn't find any results searching by profile fields, only user entity objects(name, email, etc.)

nithinkolekar’s picture

paranojik’s picture

1.26 KB
PASSED: [[SimpleTest]]: [MySQL] 124 pass(es). View

The attached patch tries to solve this issue by ensuring that the table is joined properly.

paranojik’s picture

Status: Active » Needs review
cvharris’s picture

#8 That sucker works! My site is using the latest dev version of entity reference and a patch to use tokens as arguments to views for entity selection. In addition, the view I was using was pulling in First and Last Name which I configured on a profile in Profile2. This fixed everything I had wrong with autocomplete and entity reference!

supriyarajgopal’s picture

Thanks a ton!

Patch in #8 worked for me too...After a continuous head banging of 3 days, found the solution here.
Kindly commit this patch to the latest releases as soon as possible!

Supriya Rajgopal

vasike’s picture

Title: autocomplete widget with profile2 doesn't seem to work. » Autocompletes for Views that have Fields with Relationship broken
Priority: Normal » Major
Status: Needs review » Reviewed & tested by the community
Parent issue: » #2532152: Plan for EntityReference 7.x-1.2 release

this seems pretty serious bug for Autocompletes using Views that have Fields with Relationship

I was trying to use for references of product display (nodes) using product fields.

The good news is that #8 patch seems to work.

  • spotzero committed 0f63b96 on 7.x-1.x authored by paranojik
    Issue #1898290 by paranojik: Autocompletes for Views that have Fields...
spotzero’s picture

Status: Reviewed & tested by the community » Fixed

Tested, looks good. Committed.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

Sebastien @Actualys’s picture

Committed but not released

malmeida287’s picture

Thanks @paranojik, it works!