1

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?

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.
  • I generated the code structure by - civix generate:search MySearch - in the thoughts that it should work. Looking at the generated code it extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface I guess I have now to look through that code / other code and decide what Class is appropriate! Not as easy as dropping SQL in :) – Alan Fuller Sep 23 '16 at 7:56
  • Thanks, the base class assumes a table alias and column name in function 'count' - either approach works - change the alias of the temp table and make sure it has a column 'id' - the SQL parse also demands a table called civicrm_contact to that is solved by joining it to the temp table. Still two issues 1. the query has hyperlinks to the contact - but these don't get populated with valid data 2. When I select query for print - all I get is ( altered ) Rather than solve these I might switch back to 'reports' as I have the crux of the temp table pre processing understood. – Alan Fuller Sep 23 '16 at 8:52
0

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

  • That is the issue, it isn't valid, but I didn't put it there, the SQL generator did, so i want to find out how to stop it generating broken SQL – Alan Fuller Sep 23 '16 at 7:53

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.