Custom Entry sort option SQL error

[ghost]

Description

I'm trying to make a few custom sort options for my Entries using a plugin but I'm getting a SQL error. The error says its unable to find my column which makes sense because I think its looping through my commerce products? It shows fields I am using for my commerce products in the SQL query that fails but it should only be fetching entries? This might be a commerce bug, but I don't know why it would query my products when looking for entries. So this may also be an issue within the Craft core.

This is my sort option code (don't mind my EventListener setup. It has been tested and works perfectly fine. Its just a wrapper to help me organize my code.):

class EntrySortOptionListener extends EventListener {

    public function getEventClass() {
        return Element::class;
    }

    public function getEventName() {
        return Element::EVENT_REGISTER_SORT_OPTIONS;
    }

    public function onEvent(Event $e) {
        $e->sortOptions[] = [
            'label' => 'Sector',
            'orderBy' => 'field_categorySectors',
            'attribute' => 'field:80'
        ]; 
    }

}

SQL error:

SQLSTATE[42S22]: COLUMN NOTFOUND: 1054 unknown COLUMN 'field_categorySectors' IN 'order clause'\nthe sql being executedWAS:SELECT `elements`.`id`,
       `elements`.`fieldlayoutid`, 
       `elements`.`uid`, 
       `elements`.`enabled`, 
       `elements`.`archived`, 
       `elements`.`datecreated`, 
       `elements`.`dateupdated`, 
       `elements_sites`.`slug`, 
       `elements_sites`.`uri`, 
       `elements_sites`.`enabled` AS `enabledforsite`, 
       `entries`.`sectionid`, 
       `entries`.`typeid`, 
       `entries`.`authorid`, 
       `entries`.`postdate`, 
       `entries`.`expirydate`, 
       `content`.`id` AS `contentid`, 
       `content`.`title`, 
       `content`.`field_facebook`, 
       `content`.`field_instagram`, 
       `content`.`field_intro`, 
       `content`.`field_linkedin`, 
       `content`.`field_subtitle`, 
       `content`.`field_productextra`, 
       `content`.`field_productdownloads`, 
       `content`.`field_productdatetimeend`, 
       `content`.`field_productwarranty`, 
       `content`.`field_productintro`, 
       `content`.`field_productspecs`, 
       `content`.`field_productdatetimestart`, 
       `content`.`field_seo`, 
       `content`.`field_body`, 
       `content`.`field_twitter`, 
       `structureelements`.`root`, 
       `structureelements`.`lft`, 
       `structureelements`.`rgt`, 
       `structureelements`.`level`\nfrom 
       ( 
              SELECT `elements`.`id`       AS `elementsid`, 
                     `elements_sites`.`id` AS `elementssitesid`, 
                     `content`.`id`        AS `contentid`\nfrom `elements` `elements`\ninner 
              JOIN   `entries` `entries` 
              ON     `entries`.`id` = `elements`.`id`\ninner 
              JOIN   `elements_sites` `elements_sites` 
              ON     `elements_sites`.`elementid` = `elements`.`id`\ninner 
              JOIN   `content` `content` 
              ON     `content`.`elementid` = `elements`.`id`\ninner 
              JOIN   `structureelements` `structureelements` 
              ON     ( 
                            `structureelements`.`elementid` = `elements`.`id`) 
              AND    ( 
                            `structureelements`.`structureid`=7)\nwhere (`entries`.`sectionid` IN ('13',
                                                                                                   '5',
                                                                                                   '10',
                                                                                                   '11',
                                                                                                   '1',
                                                                                                   '4',
                                                                                                   '3',
                                                                                                   '7',
                                                                                                   '12'))
              AND    ( 
                            `entries`.`sectionid`='13') 
              AND    ( 
                            `elements_sites`.`siteid`='1') 
              AND    ( 
                            `content`.`siteid`='1') 
              AND    ( 
                            `elements`.`archived`=false) 
              AND    ( 
                            `elements`.`datedeleted` IS NULL)\norder BY `field_categorysectors`\nlimit 50) `subquery`\ninner
JOIN   `entries` `entries` 
ON     `entries`.`id` = `subquery`.`elementsid`\ninner 
JOIN   `elements` `elements` 
ON     `elements`.`id` = `subquery`.`elementsid`\ninner 
JOIN   `elements_sites` `elements_sites` 
ON     `elements_sites`.`id` = `subquery`.`elementssitesid`\ninner 
JOIN   `content` `content` 
ON     `content`.`id` = `subquery`.`contentid`\ninner 
JOIN   `structureelements` `structureelements` 
ON     ( 
              `structureelements`.`elementid` = `subquery`.`elementsid`) 
AND    ( 
              `structureelements`.`structureid`=7)\norder BY `field_categorysectors`

Steps to reproduce

1. Have Craft Commerce installed
2. Create a custom sort option using a plugin
3. Try to load the Entry overview page

Additional info

• Craft version: 3.1.8
• PHP version: 7.1.6
• Database driver & version: MySQL 5.5.59
• Plugins & versions: Craft Commerce 2.0.4

[brandonkelly]

EVENT_REGISTER_SORT_OPTIONS is fired for all element classes, including Commerce products.

Hard to know exactly what you should do, since I can’t see how you’re registering these event handlers, but I’m guessing to fix you need to change Element::class ('craft\base\Element') to Entry::class ('craft\elements\Entry'). That way it only gets registered for entries, not other element types.

[ghost]

EVENT_REGISTER_SORT_OPTIONS is fired for all element classes, including Commerce products.

Hard to know exactly what you should do, since I can’t see how you’re registering these event handlers, but I’m guessing to fix you need to change Element::class ('craft\base\Element') to Entry::class ('craft\elements\Entry'). That way it only gets registered for entries, not other element types.

I tried that before but it didn't work either. Thats why I tried registering it for all elements. Registering it for all Elements could actually work if I were able to check the instance of the sender. However, $event->sender returns NULL.

Custom even class:

<?php
namespace privatename\siteutils\events;

use yii\base\Event;

abstract class EventListener {

	public static $instance = array();

	public abstract function getEventClass();
	public abstract function getEventName();

	public abstract function onEvent(Event $e);

	public static function init() {
	    $className = get_called_class();
	    EventListener::$instance[$className] = new $className();

            Event::on(EventListener::$instance[$className]->getEventClass(), EventListener::$instance[$className]->getEventName(), function(Event $e) {
                EventListener::$instance[get_called_class()]->onEvent($e);
            });
	}

}

EDIT: I've got at least 10 other classes using this EventListener and they all work perfectly fine. They are called whenever I expect them to be and I haven't had any issue's with this custom setup :)

[narration-sd]

@tim-pixeldeluxe this looks like a situation that could benefit from @brandonkelly 's explanation of the grounding for what happened here: #3761

That the event doesn't get fired sounds very familiar...as well as the attempt to get around it by using strings vs namespaces proper ;)

You can track it down with deep debugger watching, but the reasoning is there probably in Brandon's commentary, if I don't miss something. Good fortune.

[ghost]

Just tested this code and got the same error (same SQL error).

Event::on(Entry::class, Entry::EVENT_REGISTER_SORT_OPTIONS, function(RegisterElementSortOptionsEvent $e) {
    $e->sortOptions[] = [
        'label' => 'Sector',
        'orderBy' => 'field_categorySectors',
        'attribute' => 'field:80'
    ]; 
});

[narration-sd]

Well, I suspect you may need to trace at the point the query is built, then. You can get the sql itself by patching in ->sql() from where the command is completed, or intermediate points. Then you can run the query in a db tool, and see what's going on, if it's not clear from the text.

What I would do, anyway, and if in your timezone again especially ;)

[narration-sd]

Also, you might try conversation with @carlcs who is in Germany and is on Discord at moment. I don't know about queries, but he is quite astute about much else.

[ghost]

Thanks for for quick responses, I very much appreciate it! I tried some different field names but unfortunately those didn't work either. I always get an SQL error saying these columns do not exist.

I tried these variants:
field_categorySectors
content.field_categorySectors
content.categorySectors
categorySectors

This is a part of my web.log that seemed relevant to me:

2019-02-13 10:06:15 [-][33][-][info][yii\db\Command::query] SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`slug`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_facebook`, `content`.`field_instagram`, `content`.`field_intro`, `content`.`field_linkedin`, `content`.`field_subTitle`, `content`.`field_productExtra`, `content`.`field_productDownloads`, `content`.`field_productDateTimeEnd`, `content`.`field_productWarranty`, `content`.`field_productIntro`, `content`.`field_productSpecs`, `content`.`field_productDateTimeStart`, `content`.`field_seo`, `content`.`field_body`, `content`.`field_twitter`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON `content`.`elementId` = `elements`.`id`
INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `elements`.`id`) AND (`structureelements`.`structureId`=7)
WHERE (`entries`.`sectionId` IN ('13', '5', '10', '11', '1', '4', '3', '7', '12')) AND (`entries`.`sectionId`='13') AND (`elements_sites`.`siteId`='1') AND (`content`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL)
ORDER BY `field_categorySectors`
LIMIT 50) `subquery`
INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `subquery`.`elementsId`) AND (`structureelements`.`structureId`=7)
ORDER BY `field_categorySectors`
2019-02-13 10:06:15 [-][33][-][profile begin][yii\db\Command::query] SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`slug`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_facebook`, `content`.`field_instagram`, `content`.`field_intro`, `content`.`field_linkedin`, `content`.`field_subTitle`, `content`.`field_productExtra`, `content`.`field_productDownloads`, `content`.`field_productDateTimeEnd`, `content`.`field_productWarranty`, `content`.`field_productIntro`, `content`.`field_productSpecs`, `content`.`field_productDateTimeStart`, `content`.`field_seo`, `content`.`field_body`, `content`.`field_twitter`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON `content`.`elementId` = `elements`.`id`
INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `elements`.`id`) AND (`structureelements`.`structureId`=7)
WHERE (`entries`.`sectionId` IN ('13', '5', '10', '11', '1', '4', '3', '7', '12')) AND (`entries`.`sectionId`='13') AND (`elements_sites`.`siteId`='1') AND (`content`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL)
ORDER BY `field_categorySectors`
LIMIT 50) `subquery`
INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `subquery`.`elementsId`) AND (`structureelements`.`structureId`=7)
ORDER BY `field_categorySectors`
2019-02-13 10:06:16 [-][33][-][profile end][yii\db\Command::query] SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`slug`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_facebook`, `content`.`field_instagram`, `content`.`field_intro`, `content`.`field_linkedin`, `content`.`field_subTitle`, `content`.`field_productExtra`, `content`.`field_productDownloads`, `content`.`field_productDateTimeEnd`, `content`.`field_productWarranty`, `content`.`field_productIntro`, `content`.`field_productSpecs`, `content`.`field_productDateTimeStart`, `content`.`field_seo`, `content`.`field_body`, `content`.`field_twitter`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON `content`.`elementId` = `elements`.`id`
INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `elements`.`id`) AND (`structureelements`.`structureId`=7)
WHERE (`entries`.`sectionId` IN ('13', '5', '10', '11', '1', '4', '3', '7', '12')) AND (`entries`.`sectionId`='13') AND (`elements_sites`.`siteId`='1') AND (`content`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL)
ORDER BY `field_categorySectors`
LIMIT 50) `subquery`
INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `subquery`.`elementsId`) AND (`structureelements`.`structureId`=7)
ORDER BY `field_categorySectors`
2019-02-13 10:06:16 [-][33][-][error][yii\db\Exception] PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_categorySectors' in 'order clause' in /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/db/Command.php:1258
Stack trace:
#0 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/db/Command.php(1258): PDOStatement->execute()
#1 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/db/Command.php(1148): yii\db\Command->internalExecute('SELECT `element...')
#2 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/db/Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)
#3 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/db/Query.php(237): yii\db\Command->queryAll()
#4 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/db/Query.php(161): yii\db\Query->all(NULL)
#5 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1200): craft\db\Query->all(NULL)
#6 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/base/Element.php(445): craft\elements\db\ElementQuery->all()
#7 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/controllers/ElementIndexesController.php(391): craft\base\Element::indexHtml(Object(craft\elements\db\EntryQuery), Array, Array, 'section:93a9922...', 'index', true, true)
#8 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/controllers/ElementIndexesController.php(121): craft\controllers\ElementIndexesController->_elementResponseData(true, true)
#9 [internal function]: craft\controllers\ElementIndexesController->actionGetElements()
#10 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#11 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#12 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/web/Controller.php(109): yii\base\Controller->runAction('get-elements', Array)
#13 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('get-elements', Array)
#14 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/web/Application.php(297): yii\base\Module->runAction('element-indexes...', Array)
#15 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/web/Application.php(561): craft\web\Application->runAction('element-indexes...', Array)
#16 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/web/Application.php(281): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#17 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#18 /Applications/MAMP/htdocs/privatename/public_html/index.php(21): yii\base\Application->run()
#19 {main}

Next yii\db\Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_categorySectors' in 'order clause'
The SQL being executed was: SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`slug`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_facebook`, `content`.`field_instagram`, `content`.`field_intro`, `content`.`field_linkedin`, `content`.`field_subTitle`, `content`.`field_productExtra`, `content`.`field_productDownloads`, `content`.`field_productDateTimeEnd`, `content`.`field_productWarranty`, `content`.`field_productIntro`, `content`.`field_productSpecs`, `content`.`field_productDateTimeStart`, `content`.`field_seo`, `content`.`field_body`, `content`.`field_twitter`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON `content`.`elementId` = `elements`.`id`
INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `elements`.`id`) AND (`structureelements`.`structureId`=7)
WHERE (`entries`.`sectionId` IN ('13', '5', '10', '11', '1', '4', '3', '7', '12')) AND (`entries`.`sectionId`='13') AND (`elements_sites`.`siteId`='1') AND (`content`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL)
ORDER BY `field_categorySectors`
LIMIT 50) `subquery`
INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `subquery`.`elementsId`) AND (`structureelements`.`structureId`=7)
ORDER BY `field_categorySectors` in /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/db/Schema.php:664
Stack trace:
#0 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/db/Command.php(1263): yii\db\Schema->convertException(Object(PDOException), 'SELECT `element...')
#1 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/db/Command.php(1148): yii\db\Command->internalExecute('SELECT `element...')
#2 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/db/Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)
#3 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/db/Query.php(237): yii\db\Command->queryAll()
#4 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/db/Query.php(161): yii\db\Query->all(NULL)
#5 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1200): craft\db\Query->all(NULL)
#6 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/base/Element.php(445): craft\elements\db\ElementQuery->all()
#7 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/controllers/ElementIndexesController.php(391): craft\base\Element::indexHtml(Object(craft\elements\db\EntryQuery), Array, Array, 'section:93a9922...', 'index', true, true)
#8 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/controllers/ElementIndexesController.php(121): craft\controllers\ElementIndexesController->_elementResponseData(true, true)
#9 [internal function]: craft\controllers\ElementIndexesController->actionGetElements()
#10 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#11 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#12 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/web/Controller.php(109): yii\base\Controller->runAction('get-elements', Array)
#13 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('get-elements', Array)
#14 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/web/Application.php(297): yii\base\Module->runAction('element-indexes...', Array)
#15 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/web/Application.php(561): craft\web\Application->runAction('element-indexes...', Array)
#16 /Applications/MAMP/htdocs/privatename/vendor/craftcms/cms/src/web/Application.php(281): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#17 /Applications/MAMP/htdocs/privatename/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#18 /Applications/MAMP/htdocs/privatename/public_html/index.php(21): yii\base\Application->run()
#19 {main}
Additional Information:
Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'field_categorySectors' in 'order clause'
)

2019-02-13 10:06:15 [-][33][-][info][application] $_GET = [
    'p' => 'admin/actions/element-indexes/get-elements'
]

$_POST = [
    'context' => 'index'
    'elementType' => 'craft\\elements\\Entry'
    'source' => 'section:93a9922c-a311-4906-b04a-90ebec0ec6c4'
    'criteria' => [
        'status' => ''
        'siteId' => '1'
        'search' => ''
        'limit' => '50'
        'trashed' => '0'
        'enabledForSite' => ''
    ]
    'viewState' => [
        'mode' => 'table'
        'order' => 'field:80'
        'sort' => 'asc'
    ]
]

I've never really looked into SQL JOINS that much so I'm not an expert when it comes to queries like this. However, the thing I immediately noticed is the fact its selecting some of my product fields like productDateTimeStart when I'm viewing the Entry overview page. This doesn't seem right to me and I'm pretty sure this is causing the error. I have no clue why this is happening though.

I tried copy pasting some parts of the query and running them using my SQL app but without the ordering part. It returned my two entries without any problems. I checked the id's in the database and they matched.

I hope this extra info is useful. If you need any more info please let me know 😄
Again, thanks a lot!

[carlcs]

Yeah @tim-pixeldeluxe, I deleted my comment because I realized it didn’t make sense, right after I posted. You probably don’t have to namespace the column and Craft also has some magic in place to prep fields column names, so all of your variants should work.

But that’s only true for simple field types that store into the content table directly. Looking at your field handle categorySectors, I can imagine it is a Categories field. Relationship field types don’t store to the content table at all, and it’s also not so easy to make your element table sortable by those.

A quick solution to work around the issue would be to install the Preparse plugin, which would allow you to store the selected Category into the content table on element save, and you would be able to use it for your custom sorting.

https://plugins.craftcms.com/preparse-field

[ghost]

Yeah @tim-pixeldeluxe, I deleted my comment because I realized it didn’t make sense, right after I posted. You probably don’t have to namespace the column and Craft also has some magic in place to prep fields column names, so all of your variants should work.

But that’s only true for simple field types that store into the content table directly. Looking at your field handle categorySectors, I can imagine it is a Categories field. Relationship field types don’t store to the content table at all, and it’s also not so easy to make your element table sortable by those.

A quick solution to work around the issue would be to install the Preparse plugin, which would allow you to store the selected Category into the content table on element save, and you would be able to use it for your custom sorting.

https://plugins.craftcms.com/preparse-field

Yes it is a category field. I already thought this might be cause but I didn't know why it would. It makes sense now. I'm assuming there is no way to write a custom sort method using PHP?

The plugin you suggested would probably work, but it sounds kinda hacky haha. This isn't my own website but one for a customer who is going to post a lot of content. So I need to make sure I find a solid solution without the possibillity of it breaking in a future update.

[brandonkelly]

@tim-pixeldeluxe if you send a database backup, composer.lock and composer.json and any custom module code over to [email protected], along with a list of steps to reproduce the error, we can try to look into it.

[carlcs]

@tim-pixeldeluxe you can also subscribe to this issue #2818

Not sure how it will be implemented for relationship fields, possible only add the attibute for fields that have limit set to one.