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));
}
}
dateCreated
column/attribute in record forcraft_testcenter_submissions
? Craft usually takes care for alldateCreated
attributes and the SQL states it does not know which column to takemodifyElementsQuery
function somehow?