0

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: field_data_field_first_name, field_data_field_othername, field_data_field_nickname, field_data_field_fullname.

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[21000]: 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();
  • Why are you adding the same query object to the union? Is there a reason you're trying to do a union? – mradcliffe Apr 12 '16 at 21:50
  • Well... Now you are forcing me to second guess myself. My sql isn't up to par, and maybe im going about it the wrong way. I want to check against like 5 tables for the users name. Which ever of the tables' columns is closer to what the user wants. So they start typing in lar, for larry, it checks 5 tables and compares '%lar%' with a unique column in each table. It seemed like a union would work. The join is so if i got a match in the first_name table i want the last_name table to display last name with the first name. I can send some ss tomorrow if what im saying makes no sense. – Daedalus Apr 13 '16 at 3:05
  • Sorry, I misread the code and did not see the $result = statement for some reason, and thought secondQuery was running itself. :| – mradcliffe Apr 13 '16 at 10:11
  • Ahh, yeah :-). Not a problem. – Daedalus Apr 13 '16 at 12:48
1

So, I figured out how to convert MySQL to Drupal SQL, of a join and union sql transaction, I am not done - but this is the start and the rest is just copy / paste and massage.

$matches = array();
  $debug_stop = "";

  // Firstname and Lastname - Larry Adams
  $firstname_lastname_query = db_select('field_data_field_first_name', 'f') 
     ->fields('f')
    ->condition('field_first_name_value', '%'. db_like($string) .'%', 'LIKE');
  $firstname_lastname_query->leftJoin('field_data_field_last_name', 'l', 'f.entity_id = l.entity_id');
  $firstname_lastname_query->fields('l');

  // Username checks - lAdams
  $username_query = db_select('field_data_field_user_name', 'u')
      ->fields('u')
      ->condition('field_user_name_value', '%' . db_like($string) . '%', 'LIKE');
  $username_query->leftJoin('field_data_field_last_name', 'l', 'u.entity_id = l.entity_id');
  $username_query->fields('l');

  // Unionize the query to previous query
  $firstname_lastname_query->union($username_query);

  // Nickname checks - lar-bear Adams
  $nickname_lastname_query = db_select('field_data_field_nickname', 'ni')
      ->fields('ni')
      ->condition('field_nickname_value', '%' . db_like($string) . '%', 'LIKE');
  $nickname_lastname_query->leftJoin('field_data_field_last_name', 'l', 'ni.entity_id = l.entity_id');
  $nickname_lastname_query->fields('l');

  // Unionize the query to previous query
  $firstname_lastname_query->union($nickname_lastname_query);

  // Execute the query
  $result = $firstname_lastname_query->execute();


  $debug_stop = "";
  // save the query to matches
  foreach ($result as $row) {
    // If we returned back a user_name accessible field
    if (isset($row->field_user_name_value)) {
      $matches[$row->field_user_name_value] = check_plain($row->field_user_name_value);
    }
    // If we returned back a first_name_value accessible field
    if (isset($row->field_first_name_value)) {
      // with a last name
      if ($row->field_last_name_value) {
       $first_last_name = $row->field_first_name_value ." ". $row->field_last_name_value;
       $matches[$first_last_name] = check_plain($first_last_name);
      }
    }

  }  

  // Return the result to the form in json
  drupal_json_output($matches);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.