Answers.Body: <p>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".</p>
  
  <p>You can either</p>
  
  <ul>
  <li>do what the base class is expecting, in exchange for it doing some of the work for you,</li>
  <li>override the methods as needed,</li>
  <li>not extend the base class and implement the required interface methods yourself.</li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-23T07:56:20.967Z
Answers.Text: 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 :)
Answers.UserDisplayName: Alan Fuller
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-23T08:52:08.477Z
Answers.Text: 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.
Answers.UserDisplayName: Alan Fuller
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-23T00:05:05.767Z
Answers.LastActivityDate: 2016-09-23T00:05:05.767Z
Answers.UserDisplayName: Demerit
Answers.Body: <p>Contact_a isn't a table there as far as I can tell (on smartphone atm). Change it to civicrm_contact.id :)</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-23T07:53:29.303Z
Answers.Text: 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
Answers.UserDisplayName: Alan Fuller
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-22T19:39:43.413Z
Answers.LastActivityDate: 2016-09-22T19:39:43.413Z
Answers.UserDisplayName: JohnFF
Body: <p>I'm trying to code a custom search (CiviCRM 4.7.11 - WordPress)  and I keep getting</p>
  
  <pre><code> Unknown column 'contact_a.id' in 'field list'
  </code></pre>
  
  <p>I built the search by civix as per docs and that created a contact search by default</p>
  
  <p>I stripped out the form code and have even simplified it all the way down to one column</p>
  
  <p>The meat being as follows and should just list hello thousands of times</p>
  
  <pre><code>function &amp;columns() {
  // return by reference
     $columns = array(
    ts('Hello') =&gt; '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-&gt;sql(), $this-&gt;select(), $this-&gt;from(), $this-&gt;where(), etc.
  return $this-&gt;sql($this-&gt;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";
  }
  </code></pre>
  
  <p>But I get </p>
  
  <pre><code>Database Error Code: Unknown column 'contact_a.id' in 'field list', 1054
  Additional Details:
  Array
  (
  [callback] =&gt; Array
      (
          [0] =&gt; CRM_Core_Error
          [1] =&gt; handle
      )
  
  [code] =&gt; -19
  [message] =&gt; DB Error: no such field
  [mode] =&gt; 16
  [debug_info] =&gt; SELECT count(distinct contact_a.id) as total 
    FROM civicrm_contact
    WHERE 
  1
   [nativecode=1054 ** Unknown column 'contact_a.id' in 'field list']
  [type] =&gt; DB_Error
  [user_info] =&gt; 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] =&gt; [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 )
  </code></pre>
  
  <p>I don't understand what is trying to create the <code>SELECT count(distinct contact_a.id) as total</code>  with an invalid column and how to stop it?</p>
  
  <p>Ideas?</p>
  
Tags: <custom-search>
Title: Custom Search Unknown column 'contact_a.id' in 'field list'
ThreadText: # Custom Search Unknown column 'contact_a.id' in 'field list'
  
  (Asked by: username_0 on 2016-09-22)
  
  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?
  
  **Tags:** custom-search
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2016-09-23. Score: 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.
  
  > **Comments:**
  > **username_0**: 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 :)  
  > **username_0**: 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.  
  
  ---
  
  ### Answer by username_2 on 2016-09-22. Score: 0
  
  Contact\_a isn't a table there as far as I can tell (on smartphone atm). Change it to civicrm\_contact.id :)
  
  > **Comments:**
  > **username_0**: 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  
  
  ---
  
  