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?

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)
        ->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));
  • Can you please show us the exact error? When I copy your code into my custom element type it is working fine May 8, 2018 at 12:02
  • Updated with SQL error ;) May 8, 2018 at 12:17
  • 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 May 8, 2018 at 12:22
  • 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. May 8, 2018 at 12:35
  • Did you change the modifyElementsQuery function somehow? May 8, 2018 at 12:42

1 Answer 1


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.

  • 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. May 8, 2018 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.