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  
  
  ---
  
  