Custom Search Unknown column 'contact_a.id' in 'field list'

Question

I'm trying to code a custom search (CiviCRM 4.7.11 - WordPress) and I keep getting

 Unknown column 'contact_a.id' in 'field list'

I built the search by civix as per docs and that created a contact search by default

I stripped out the form code and have even simplified it all the way down to one column

The meat being as follows and should just list hello thousands of times

function &columns() {
// return by reference
   $columns = array(
  ts('Hello') => 'hool',
);
 return $columns;
}
/**
* Construct a full SQL query which returns one page worth of results
*
* @param int $offset
* @param int $rowcount
* @param null $sort
* @param bool $includeContactIDs
* @param bool $justIDs
* @return string, sql
*/
function all($offset = 0, $rowcount = 0, $sort = NULL, $includeContactIDs = FALSE, $justIDs = FALSE) {
// delegate to $this->sql(), $this->select(), $this->from(), $this->where(), etc.
return $this->sql($this->select(), $offset, $rowcount, $sort, $includeContactIDs, NULL);
}

/**
 * Construct a SQL SELECT clause
 *
 * @return string, sql fragment with SELECT arguments
 */
 function select() {
   return ' "hello" hool';
 }


 /**
  * Construct a SQL FROM clause
 *
 * @return string, sql fragment with FROM and JOIN clauses
 */
function from() {
   return "FROM civicrm_contact";
}

/**
 * Construct a SQL WHERE clause
 *
 * @param bool $includeContactIDs
 * @return string, sql fragment with conditional expressions
 */
function where($includeContactIDs = FALSE) {
 return "1";
}

But I get

Database Error Code: Unknown column 'contact_a.id' in 'field list', 1054
Additional Details:
Array
(
[callback] => Array
    (
        [0] => CRM_Core_Error
        [1] => handle
    )

[code] => -19
[message] => DB Error: no such field
[mode] => 16
[debug_info] => SELECT count(distinct contact_a.id) as total 
  FROM civicrm_contact
  WHERE 
1
 [nativecode=1054 ** Unknown column 'contact_a.id' in 'field list']
[type] => DB_Error
[user_info] => SELECT count(distinct contact_a.id) as total 
  FROM civicrm_contact
 WHERE 
1
  [nativecode=1054 ** Unknown column 'contact_a.id' in 'field list']
 [to_string] => [db_error: message="DB Error: no such field" code=-19   mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT count(distinct contact_a.id) as total 
  FROM civicrm_contact
 WHERE 
 1
 [nativecode=1054 ** Unknown column 'contact_a.id' in 'field list']"]
o )

I don't understand what is trying to create the SELECT count(distinct contact_a.id) as total with an invalid column and how to stop it?

Ideas?

Answer 1

Does your class extend a base class? A parent method in there might be expecting you to alias your main contact table "AS contact_a".

You can either

• do what the base class is expecting, in exchange for it doing some of the work for you,
• override the methods as needed,
• not extend the base class and implement the required interface methods yourself.

Answer 2

Contact_a isn't a table there as far as I can tell (on smartphone atm). Change it to civicrm_contact.id :)