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

CC BY-SA 4.0
2018-05-08T14:18:45.373Z
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.
Shift2Design
CC BY-SA 4.0
2018-05-08T13:37:15.653Z
2018-05-08T13:37:15.653Z
Robin Schambach
<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>

CC BY-SA 4.0
2018-05-08T12:02:59.873Z
Can you please show us the exact error? When I copy your code into my custom element type it is working fine
Robin Schambach
CC BY-SA 4.0
2018-05-08T12:17:35.323Z
Updated with SQL error ;)
Shift2Design
CC BY-SA 4.0
2018-05-08T12:22:52.257Z
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
Robin Schambach
CC BY-SA 4.0
2018-05-08T12:35:25.630Z
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.
Shift2Design
CC BY-SA 4.0
2018-05-08T12:42:12.623Z
Did you change the `modifyElementsQuery` function somehow?
Robin Schambach
CC BY-SA 4.0
2018-05-08T12:44:23.900Z
Yes a small bit. I'll add it to original post.
Shift2Design
CC BY-SA 4.0
2018-05-08T12:46:49.400Z
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?
Shift2Design
CC BY-SA 4.0
2018-05-08T12:54:46.097Z
simply by not selecting all columns `ts.*` but only those you need `ts.score`
Robin Schambach
CC BY-SA 4.0
2018-05-08T13:00:13.820Z
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.
Shift2Design
CC BY-SA 4.0
2018-05-08T13:02:50.217Z
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()`?
Robin Schambach
CC BY-SA 4.0
2018-05-08T13:08:36.740Z
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.
Shift2Design
CC BY-SA 4.0
2018-05-08T13:11:55.117Z
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
Robin Schambach
CC BY-SA 4.0
2018-05-08T13:24:53.737Z
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.
Shift2Design
<plugin-development><element>
Custom element index page (cp) sortable on dateCreated?
# 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 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.  

---

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

---

