1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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