I am a bit out of my depth here.
I have successfully implemented my own autocomplete field of an entityform, the following link was my roadmap Textfield that uses AutoComplete.
But I am trying to do something much more fancier. I need to search for a users name, and autocomplete it for the user. The issue I am having is that I must check multiple tables for the user to select from, tables like:
Because some people's names have changed, or have a nickname.
So I have begun checking multiple tables and think I got a good start, but I keep getting the following error:
"SQLSTATE: Cardinality violation: 1222 The used SELECT statements have a different number of columns"
I want to be able to incorporate this:
SELECT fn.*, ln.* FROM field_data_field_first_name as fn left join field_data_field_last_name as ln ON fn.entity_id = ln.entity_id WHERE fn.field_first_name_value LIKE '%bun%' UNION SELECT fn.*, fo.* FROM field_data_field_first_name as fn left join field_data_field_other_name as fo ON fn.entity_id = fo.entity_id WHERE fn.field_first_name_value LIKE '%bun%' UNION SELECT ni.*, ln.* FROM field_data_field_nickname as ni left join field_data_field_last_name as ln on ni.entity_id = ln.entity_id WHERE ni.field_nickname_value LIKE '%bun%' UNION SELECT ni.*, fo.* FROM field_data_field_nickname as ni LEFT JOIN field_data_field_other_name as fo ON ni.entity_id = fo.entity_id WHERE ni.field_nickname_value LIKE '%bun%'
into Drupal 7
db_select. I have some of this query in my Drupal query (not all of it, for the moment) but I am getting the column discrepency error (1222), my column counts aren't matching. Can't I just grab every field, like I do in my MySQL string and call it a day? I thought
fields('alias') would of solved this, since that is grabbing every column, but it doesn't seem to be doing the trick!
This is what I got now:
$secondQuery = db_select('field_data_field_first_name', 'f') ->fields('f') // check the first_name_value, but we want to do a leftJoin to show the left_name_value as well! like Larry, Adams, not just Larry ->condition('field_first_name_value', '%'. db_like($string) .'%', 'LIKE'); $secondQuery->leftJoin('field_data_field_last_name', 'l', 'f.entity_id = l.entity_id'); $secondQuery->fields('l'); // Make the query $result = db_select('field_data_field_user_name', 'u') ->fields('u') ->condition('field_user_name_value', '%' . db_like($string) . '%', 'LIKE') ->union($secondQuery) ->execute();