changing a MySQL query to a drupal query any ideas

Question

I'm trying to change the following query to a drupal db_query:

SELECT first_name, SUBSTR(first_name,1,3) AS firstname_substr FROM civicrm_contact WHERE id != 'NULL' ;

I have tried this, but its not working:

$firstname_substr = $query->addExpression('SUBSTRING(cc.first_name,1,3)','cc.firstname_substr');

Below is the function I am trying to add it to:

function search_pcp($name, $from = 0, $count = 5){

  $query = db_select('civicrm_pcp', 'pcp');
  $query -> join('civicrm_contribution_page','cp', 'pcp.page_id = cp.id');
  $query -> join('civicrm_contact','cc','pcp.contact_id = cc.id');
  $query -> addJoin('LEFT OUTER','civicrm_entity_file','cef', 'cef.entity_id = pcp.id');
  $query -> addJoin('LEFT OUTER','civicrm_file','cf', 'cef.file_id = cf.id');
  $query -> condition('pcp.is_active','1','=');
  $query -> condition('cp.is_active','1','=');  

  $db_or = db_or();
  $db_or->condition('cc.first_name',  $name, 'like');
  $db_or->condition('cc.last_name',  $name, 'like');

  **//$db_or->condition($firstname_substr, $name);**

  $query -> condition($db_or);

  return $query->execute();

}

Answer 1

I believe you need to change

$firstname_substr = $query->addExpression('SUBSTRING(cc.first_name,1,3)','cc.firstname_substr');

to

$query->addExpression('SUBSTRING(cc.first_name,1,3)','firstname_substr');

and

$db_or->condition($firstname_substr, $name);

to

$db_or->condition('firstname_substr', '%'. db_like($name) . '%', 'like');

So it will look like this:

function search_pcp($name, $from = 0, $count = 5){

  $query = db_select('civicrm_pcp', 'pcp');
  $query->join('civicrm_contribution_page','cp', 'pcp.page_id = cp.id');
  $query->join('civicrm_contact','cc','pcp.contact_id = cc.id');
  $query->addJoin('LEFT OUTER','civicrm_entity_file','cef', 'cef.entity_id = pcp.id');
  $query->addJoin('LEFT OUTER','civicrm_file','cf', 'cef.file_id = cf.id');
  $query->condition('pcp.is_active','1','=');
  $query->condition('cp.is_active','1','=');

  // changed cc.firstname_substr to just firstname_substr
  $query->addExpression('SUBSTRING(cc.first_name,1,3)','firstname_substr');

  $db_or = db_or();
  $db_or->condition('cc.first_name',  $name, 'like');
  $db_or->condition('cc.last_name',  $name, 'like');

  // corrected the condition statement
  $db_or->condition('firstname_substr', '%' . db_like($name) . '%', 'like');

  $query -> condition($db_or);

  return $query->execute();

}