SQL query in widget preventing users from accessing CMS

[paulgoodfield]

Hi there,

We recently installed the plugin for a client and all seemed to be working fine, but in the last few days the client was getting an Internal Server Error when trying to login to the CMS control panel.

I checked through the error logs and found that there's an ambiguous 'id' being selected in the query in the TotalCartsRecovered widget.

Full related error:

2020-08-10 13:06:04 [-][14885][gim8sjuh0tfas647m12ro1r8um][error][yii\db\IntegrityException] PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous in craft/vendor/yiisoft/yii2/db/Command.php:1293
Stack trace:
#0 craft/vendor/yiisoft/yii2/db/Command.php(1293): PDOStatement->execute()
#1 craft/vendor/yiisoft/yii2/db/Command.php(1159): yii\db\Command->internalExecute('SELECT `id`\nFRO...')
#2 craft/vendor/yiisoft/yii2/db/Command.php(444): yii\db\Command->queryInternal('fetchAll', 7)
#3 craft/vendor/yiisoft/yii2/db/Query.php(318): yii\db\Command->queryColumn()
#4 craft/vendor/craftcms/cms/src/db/Query.php(202): yii\db\Query->column(NULL)
#5 craft/vendor/mediabeastnz/craft-commerce-abandoned-cart/src/stats/TotalCartsRecovered.php(33): craft\db\Query->column()
#6 craft/vendor/craftcms/commerce/src/base/Stat.php(70): mediabeastnz\abandonedcart\stats\TotalCartsRecovered->getData()
#7 craft/vendor/mediabeastnz/craft-commerce-abandoned-cart/src/widgets/TotalCartsRecovered.php(91): craft\commerce\base\Stat->get()
#8 craft/vendor/craftcms/cms/src/controllers/DashboardController.php(535): mediabeastnz\abandonedcart\widgets\TotalCartsRecovered->getBodyHtml()
#9 craft/vendor/craftcms/cms/src/controllers/DashboardController.php(108): craft\controllers\DashboardController->_getWidgetInfo(Object(mediabeastnz\abandonedcart\widgets\TotalCartsRecovered))
#10 [internal function]: craft\controllers\DashboardController->actionIndex()
#11 craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#12 craft/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#13 craft/vendor/craftcms/cms/src/web/Controller.php(178): yii\base\Controller->runAction('index', Array)
#14 craft/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('index', Array)
#15 craft/vendor/craftcms/cms/src/web/Application.php(291): yii\base\Module->runAction('dashboard/index', Array)
#16 craft/vendor/yiisoft/yii2/web/Application.php(103): craft\web\Application->runAction('dashboard/index', Array)
#17 craft/vendor/craftcms/cms/src/web/Application.php(276): yii\web\Application->handleRequest(Object(craft\web\Request))
#18 craft/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#19 craft/web/index.php(21): yii\base\Application->run()
#20 {main}

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous
The SQL being executed was: SELECT `id`
FROM `commerce_orders` `orders`
INNER JOIN `elements` `elements` ON `elements`.`id` = `orders`.`id`
WHERE (`dateOrdered` >= '2020-08-09 23:00:00') AND (`dateOrdered` <= '2020-08-10 22:59:59') AND (`isCompleted`=1) AND (`elements`.`dateDeleted` IS NULL) in craft/vendor/yiisoft/yii2/db/Schema.p$
Stack trace:
#0 craft/vendor/yiisoft/yii2/db/Command.php(1298): yii\db\Schema->convertException(Object(PDOException), 'SELECT `id`\nFRO...')
#1 craft/vendor/yiisoft/yii2/db/Command.php(1159): yii\db\Command->internalExecute('SELECT `id`\nFRO...')
#2 craft/vendor/yiisoft/yii2/db/Command.php(444): yii\db\Command->queryInternal('fetchAll', 7)
#3 craft/vendor/yiisoft/yii2/db/Query.php(318): yii\db\Command->queryColumn()
#4 craft/vendor/craftcms/cms/src/db/Query.php(202): yii\db\Query->column(NULL)
#5 craft/vendor/mediabeastnz/craft-commerce-abandoned-cart/src/stats/TotalCartsRecovered.php(33): craft\db\Query->column()
#6 craft/vendor/craftcms/commerce/src/base/Stat.php(70): mediabeastnz\abandonedcart\stats\TotalCartsRecovered->getData()
#7 craft/vendor/mediabeastnz/craft-commerce-abandoned-cart/src/widgets/TotalCartsRecovered.php(91): craft\commerce\base\Stat->get()
#8 craft/vendor/craftcms/cms/src/controllers/DashboardController.php(535): mediabeastnz\abandonedcart\widgets\TotalCartsRecovered->getBodyHtml()
#9 craft/vendor/craftcms/cms/src/controllers/DashboardController.php(108): craft\controllers\DashboardController->_getWidgetInfo(Object(mediabeastnz\abandonedcart\widgets\TotalCartsRecovered))
#10 [internal function]: craft\controllers\DashboardController->actionIndex()
#11 craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#12 craft/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#13 craft/vendor/craftcms/cms/src/web/Controller.php(178): yii\base\Controller->runAction('index', Array)
#14 craft/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('index', Array)
#15 craft/vendor/craftcms/cms/src/web/Application.php(291): yii\base\Module->runAction('dashboard/index', Array)
#16 craft/vendor/yiisoft/yii2/web/Application.php(103): craft\web\Application->runAction('dashboard/index', Array)
#17 craft/vendor/craftcms/cms/src/web/Application.php(276): yii\web\Application->handleRequest(Object(craft\web\Request))
#18 craft/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#19 craft/web/index.php(21): yii\base\Application->run()
#20 {main}
Additional Information:
Array
(
    [0] => 23000
    [1] => 1052
    [2] => Column 'id' in field list is ambiguous
)

Versions:

Craft: 3.4.28.1 Commerce: 3.1.12 Craft Commerce Abandoned Cart: 1.5.3

[BrandonJamesBorders]

@mediabeastnz Had this issue as well. Please review my PR. Thanks!

[paulgoodfield]

@BrandonJamesBorders any idea if your fix has been merged yet?

[BrandonJamesBorders]

@paulgoodfield It has not. If you need to pull in my fix, you can replace the appropriate line in your composer file with this one:

"mediabeastnz/craft-commerce-abandoned-cart": "dev-patch-2",