Custom element index page (cp) sortable on dateCreated?

Question

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));
    }
}

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.