Answers.Body: <p>The issue was in the <code>modifyElementsQuery</code> function. When you select all columns <code>addSelect('*')</code> to the query your <code>SQL</code> will select the <code>dateCreated</code> and <code>dateUpdated</code> columns of your custom table as well. 
  As soon as you want to order by one of those columns the database does not know which <code>dateCreated</code> should be sorted.</p>
  
  <p>You need to select only the unique attribute columns in your custom element table. </p>
  
  <p>To debug your <code>score</code> sorting issue the best thing is to go into <code>craft/app/elementtypes/BaseElementType.php</code> and check the function <code>getIndexHtml</code>. You can <code>var_dump</code> the results of the following variables</p>
  
  <pre><code>// Combine them, accounting for the possibility that $order could contain multiple values,
  // and be defensive about the possibility that the first value actually has "asc" or "desc"
  
  // typeId             =&gt; typeId [sort]
  // typeId, title      =&gt; typeId [sort], title
  // typeId, title desc =&gt; typeId [sort], title desc
  // typeId desc        =&gt; typeId [sort]
  
  $criteria-&gt;order = preg_replace('/^(.*?)(?:\s+(?:asc|desc))?(,.*)?$/i', "$1 {$sort}$2", $order);
  </code></pre>
  
  <p>and compare the output with default Entry fields.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-05-08T14:18:45.373Z
Answers.Text: Thanks Robin, this was the answer/issues.... As you referenced I was getting dateCreated in element vs my own table confused, and using the ts.* made a conflict of selecting both. As far as SCORE, it would appear it must have been conflicting with a Craft score. When I renamed to testScore it started sorting properly. Robin's response regarding getIndexHtml led me to noticing Craft had a native score and I think therein lied the conflict. It is now working exactly as expected.
Answers.UserDisplayName: Shift2Design
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-05-08T13:37:15.653Z
Answers.LastActivityDate: 2018-05-08T13:37:15.653Z
Answers.UserDisplayName: Robin Schambach
Body: <p>I have a plugin with custom elements that can be sorted on a handful of fields in my CP element index page. I can get the DATECREATED displaying correctly that Craft generates automatically but I cannot sort by it. I am getting an ambiguous column error in SQL and not sure how I am supposed to include these native columns so they can be sorted like in the CP entries section. The other field, SCORE, is an example of one of my fields that I adjust the html display a bit (add a '%' sign) when it's rendered and sorting doesn't seem to work. Anybody with some tips? I tried referencing the craft app EntryElementType file but still could not quite figure it out. Thanks</p>
  
  <pre><code>public function defineSortableAttributes()
  {
      $attributes = array(
          'id'            =&gt; Craft::t('ID'), 
          'userId'        =&gt; Craft::t('User'), 
          'testId'        =&gt; Craft::t('Test ID'),  
          'score'         =&gt; Craft::t('Score'), // no errors but not sorting properly
          'dateCreated'   =&gt; Craft::t('Date Created'), 
      );
  
      return $attributes;
  }
  
  /**
   * Returns the attributes that can be shown/sorted by in table views.
   *
   * @param string|null $source
   * @return array
   */
  public function defineAvailableTableAttributes()
  {
      $attributes = array(
          'id'                =&gt; Craft::t('ID'), 
          'userId'            =&gt; Craft::t('User'), 
          'testId'            =&gt; Craft::t('Test'), 
          'results'           =&gt; Craft::t('Results'), 
          'score'             =&gt; Craft::t('Score'), 
          'ipAddress'         =&gt; Craft::t('IP Address'), 
          'dateCreated'       =&gt; Craft::t('Date Created'), 
      );
  
      return $attributes;
  }
  </code></pre>
  
  <p>The error for dateCreated (when trying to sort by the field) is below. When trying to sort by SCORE I don't notice an error but the order doesn't seem to change, so I'm not terribly sure what's not quite right there. Maybe something to do with sorting on fields where I customize the html output?</p>
  
  <pre><code>CDbException
  CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'dateCreated' in order clause is ambiguous. The SQL statement executed was: SELECT `elements`.`id`, `elements`.`type`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_i18n`.`slug`, `elements_i18n`.`uri`, `elements_i18n`.`enabled` AS `localeEnabled`, `ts`.* FROM `craft_elements` `elements` JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id JOIN `craft_testcenter_submissions` `ts` ON ts.id = elements.id WHERE (elements_i18n.locale = :locale) AND (elements.archived = 0) GROUP BY `elements`.`id` ORDER BY `dateCreated` ASC LIMIT 50. Bound with :locale='en_us' (/volume1/storage/___/HTML/01/craft/app/framework/db/CDbCommand.php:543)
  </code></pre>
  
  <p>Here is my modifyElementsQuery</p>
  
  <pre><code>public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
  {
      $query
          -&gt;addSelect('ts.*')
          -&gt;join('testcenter_submissions ts', 'ts.id = elements.id');
  
      if ($criteria-&gt;userId)
      {
          $query-&gt;andWhere(DbHelper::parseParam('ts.userId', $criteria-&gt;userId, $query-&gt;params));
      }
  
      if ($criteria-&gt;testId)
      {
          $query-&gt;andWhere(DbHelper::parseParam('ts.testId', $criteria-&gt;testId, $query-&gt;params));
      }
  }
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-08T12:02:59.873Z
Comments.Text: Can you please show us the exact error? When I copy your code into my custom element type it is working fine
Comments.UserDisplayName: Robin Schambach
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-08T12:17:35.323Z
Comments.Text: Updated with SQL error ;)
Comments.UserDisplayName: Shift2Design
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-08T12:22:52.257Z
Comments.Text: Do you have a custom `dateCreated` column/attribute in record for `craft_testcenter_submissions`? Craft usually takes care for all `dateCreated` attributes and the SQL states it does not know which column to take
Comments.UserDisplayName: Robin Schambach
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-08T12:35:25.630Z
Comments.Text: I have my own table where these custom fields (like score etc) are saved and Craft automatically created a few of the usual columns like dateCreated, dateUpdated and uid. The dateCreated field in my craft_testcenter_submissions table is the one I am trying to use. It gets displayed properly but when sorting Craft doesn't know which dateCreated to use and I'm not quite sure how to remedy.
Comments.UserDisplayName: Shift2Design
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-08T12:42:12.623Z
Comments.Text: Did you change the `modifyElementsQuery` function somehow?
Comments.UserDisplayName: Robin Schambach
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-08T12:44:23.900Z
Comments.Text: Yes a small bit. I'll add it to original post.
Comments.UserDisplayName: Shift2Design
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-08T12:46:49.400Z
Comments.Text: Hmmmm...I think I see where you are going with this, I am grabbing all ts.* columns which includes dateCreated. How can I specify thats the one I want to use vs the elements' dateCreated?
Comments.UserDisplayName: Shift2Design
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-08T12:54:46.097Z
Comments.Text: simply by not selecting all columns `ts.*` but only those you need `ts.score`
Comments.UserDisplayName: Robin Schambach
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-08T13:00:13.820Z
Comments.Text: Okay, that makes sense but I am actually using the dateCreated from this table not from the element. I don't believe changing the elements dateCreated etc cascades to these records which I why I wanted to display ts.dateCreated for example instead of the elements' dateCreated. I updated to only select specific ts.xxxxxx fields vs * but unless I can alias that field as a new name like myDateCreated I may have to rethink how I am using dateCreated. If I include ts.dateCreated I will of course still have the same ambiguos column issue.
Comments.UserDisplayName: Shift2Design
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-08T13:02:50.217Z
Comments.Text: Then you might want to change your logic while storing your records. Craft usually takes care of everything. You `testcenter_submissions``dateCreated` and `dateUpdated` fields should always be the same as those from the `craft_elements` table. Maybe you don't use `craft()->element->saveElement()`?
Comments.UserDisplayName: Robin Schambach
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-08T13:08:36.740Z
Comments.Text: Okay, I can do that. I know I was using some logic to see how old a custom element was (based on dateCreated) and was manually changing that field for testing. I can refactor, that makes sense. Any thoughs on the scoring sorting? The values are stored like 100, 66.7, 33.3, 50 etc and I am just adding a percent sign in the tablehtml. I'm not getting an error but it also doesn't seem to change the order.
Comments.UserDisplayName: Shift2Design
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-08T13:11:55.117Z
Comments.Text: As I already said.. Craft/SQL handles everything. As long as you have an `numeric` column type you don't have to do anything else than adding the correct index in your `defineSortableAttributes` in your case it could be `ts.score` edit: of course that's only the case if you don't changed the `getIndexHtml` as well
Comments.UserDisplayName: Robin Schambach
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-08T13:24:53.737Z
Comments.Text: Thanks Robin. If you respond the select '*' was a conflict with the elements dateCreated I'll mark as the answer. I'm definitely fighting something odd/unrelated with the score column. Others work as expected but that one doesn't change the sorting whether asc/desc and it isn't actually asc or desc. The arrow will change from asc/desc but even the options if you choose the sorting dropdown on the right ascending/descending are grayed out.
Comments.UserDisplayName: Shift2Design
Tags: <plugin-development><element>
Title: Custom element index page (cp) sortable on dateCreated?
ThreadText: # Custom element index page (cp) sortable on dateCreated?
  
  (Asked by: username_0 on 2018-05-08)
  
  I have a plugin with custom elements that can be sorted on a handful of fields in my CP element index page. I can get the DATECREATED displaying correctly that Craft generates automatically but I cannot sort by it. I am getting an ambiguous column error in SQL and not sure how I am supposed to include these native columns so they can be sorted like in the CP entries section. The other field, SCORE, is an example of one of my fields that I adjust the html display a bit (add a '%' sign) when it's rendered and sorting doesn't seem to work. Anybody with some tips? I tried referencing the craft app EntryElementType file but still could not quite figure it out. Thanks
  
  ```
  public function defineSortableAttributes()
  {
      $attributes = array(
          'id'            => Craft::t('ID'), 
          'userId'        => Craft::t('User'), 
          'testId'        => Craft::t('Test ID'),  
          'score'         => Craft::t('Score'), // no errors but not sorting properly
          'dateCreated'   => Craft::t('Date Created'), 
      );
  
      return $attributes;
  }
  
  /**
   * Returns the attributes that can be shown/sorted by in table views.
   *
   * @param string|null $source
   * @return array
   */
  public function defineAvailableTableAttributes()
  {
      $attributes = array(
          'id'                => Craft::t('ID'), 
          'userId'            => Craft::t('User'), 
          'testId'            => Craft::t('Test'), 
          'results'           => Craft::t('Results'), 
          'score'             => Craft::t('Score'), 
          'ipAddress'         => Craft::t('IP Address'), 
          'dateCreated'       => Craft::t('Date Created'), 
      );
  
      return $attributes;
  }
  
  ```
  
  The error for dateCreated (when trying to sort by the field) is below. When trying to sort by SCORE I don't notice an error but the order doesn't seem to change, so I'm not terribly sure what's not quite right there. Maybe something to do with sorting on fields where I customize the html output?
  
  ```
  CDbException
  CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'dateCreated' in order clause is ambiguous. The SQL statement executed was: SELECT `elements`.`id`, `elements`.`type`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_i18n`.`slug`, `elements_i18n`.`uri`, `elements_i18n`.`enabled` AS `localeEnabled`, `ts`.* FROM `craft_elements` `elements` JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id JOIN `craft_testcenter_submissions` `ts` ON ts.id = elements.id WHERE (elements_i18n.locale = :locale) AND (elements.archived = 0) GROUP BY `elements`.`id` ORDER BY `dateCreated` ASC LIMIT 50. Bound with :locale='en_us' (/volume1/storage/___/HTML/01/craft/app/framework/db/CDbCommand.php:543)
  
  ```
  
  Here is my modifyElementsQuery
  
  ```
  public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
  {
      $query
          ->addSelect('ts.*')
          ->join('testcenter_submissions ts', 'ts.id = elements.id');
  
      if ($criteria->userId)
      {
          $query->andWhere(DbHelper::parseParam('ts.userId', $criteria->userId, $query->params));
      }
  
      if ($criteria->testId)
      {
          $query->andWhere(DbHelper::parseParam('ts.testId', $criteria->testId, $query->params));
      }
  }
  
  ```
  
  **Tags:** plugin-development, element
  
  > **Comments:**
  > **username_1**: Can you please show us the exact error? When I copy your code into my custom element type it is working fine  
  > **username_0**: Updated with SQL error ;)  
  > **username_1**: Do you have a custom `dateCreated` column/attribute in record for `craft_testcenter_submissions`? Craft usually takes care for all `dateCreated` attributes and the SQL states it does not know which column to take  
  > **username_0**: I have my own table where these custom fields (like score etc) are saved and Craft automatically created a few of the usual columns like dateCreated, dateUpdated and uid. The dateCreated field in my craft_testcenter_submissions table is the one I am trying to use. It gets displayed properly but when sorting Craft doesn't know which dateCreated to use and I'm not quite sure how to remedy.  
  > **username_1**: Did you change the `modifyElementsQuery` function somehow?  
  > **username_0**: Yes a small bit. I'll add it to original post.  
  > **username_0**: Hmmmm...I think I see where you are going with this, I am grabbing all ts.* columns which includes dateCreated. How can I specify thats the one I want to use vs the elements' dateCreated?  
  > **username_1**: simply by not selecting all columns `ts.*` but only those you need `ts.score`  
  > **username_0**: Okay, that makes sense but I am actually using the dateCreated from this table not from the element. I don't believe changing the elements dateCreated etc cascades to these records which I why I wanted to display ts.dateCreated for example instead of the elements' dateCreated. I updated to only select specific ts.xxxxxx fields vs * but unless I can alias that field as a new name like myDateCreated I may have to rethink how I am using dateCreated. If I include ts.dateCreated I will of course still have the same ambiguos column issue.  
  > **username_1**: Then you might want to change your logic while storing your records. Craft usually takes care of everything. You `testcenter_submissions``dateCreated` and `dateUpdated` fields should always be the same as those from the `craft_elements` table. Maybe you don't use `craft()->element->saveElement()`?  
  > **username_0**: Okay, I can do that. I know I was using some logic to see how old a custom element was (based on dateCreated) and was manually changing that field for testing. I can refactor, that makes sense. Any thoughs on the scoring sorting? The values are stored like 100, 66.7, 33.3, 50 etc and I am just adding a percent sign in the tablehtml. I'm not getting an error but it also doesn't seem to change the order.  
  > **username_1**: As I already said.. Craft/SQL handles everything. As long as you have an `numeric` column type you don't have to do anything else than adding the correct index in your `defineSortableAttributes` in your case it could be `ts.score` edit: of course that's only the case if you don't changed the `getIndexHtml` as well  
  > **username_0**: Thanks username_1. If you respond the select '*' was a conflict with the elements dateCreated I'll mark as the answer. I'm definitely fighting something odd/unrelated with the score column. Others work as expected but that one doesn't change the sorting whether asc/desc and it isn't actually asc or desc. The arrow will change from asc/desc but even the options if you choose the sorting dropdown on the right ascending/descending are grayed out.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2018-05-08. Score: 1 | Accepted answer
  
  The issue was in the `modifyElementsQuery` function. When you select all columns `addSelect('*')` to the query your `SQL` will select the `dateCreated` and `dateUpdated` columns of your custom table as well.
  As soon as you want to order by one of those columns the database does not know which `dateCreated` should be sorted.
  
  You need to select only the unique attribute columns in your custom element table.
  
  To debug your `score` sorting issue the best thing is to go into `craft/app/elementtypes/BaseElementType.php` and check the function `getIndexHtml`. You can `var_dump` the results of the following variables
  
  ```
  // Combine them, accounting for the possibility that $order could contain multiple values,
  // and be defensive about the possibility that the first value actually has "asc" or "desc"
  
  // typeId             => typeId [sort]
  // typeId, title      => typeId [sort], title
  // typeId, title desc => typeId [sort], title desc
  // typeId desc        => typeId [sort]
  
  $criteria->order = preg_replace('/^(.*?)(?:\s+(?:asc|desc))?(,.*)?$/i', "$1 {$sort}$2", $order);
  
  ```
  
  and compare the output with default Entry fields.
  
  > **Comments:**
  > **username_0**: Thanks username_1, this was the answer/issues.... As you referenced I was getting dateCreated in element vs my own table confused, and using the ts.* made a conflict of selecting both. As far as SCORE, it would appear it must have been conflicting with a Craft score. When I renamed to testScore it started sorting properly. username_1's response regarding getIndexHtml led me to noticing Craft had a native score and I think therein lied the conflict. It is now working exactly as expected.  
  
  ---
  
  