search

studioespresso / craft-scout

Craft Scout provides a simple solution for adding full-text search to your entries. Scout will automatically keep your search indexes in sync with your entries.
MIT License
81 stars 54 forks source link

Unknown column 'purchasables_stores.hasUnlimitedStock' #299

Closed sunscreem closed 1 month ago

sunscreem commented 1 month ago

I'm not sure if this is a scout or a commerce issue - but I'll start here incase I'm doing something dumb:

my criteria in scout.php:

->criteria(function (ProductQuery $query) {
        return $query->status(Product::STATUS_ENABLED)->hasVariant(['hasStock' => true])->with(['productImages', 'productImage1', 'relatedWatchBrands', 'relatedJewelleryBrands', 'jewelleryCategories', 'relatedGiftBrands']);
      })

When I go to Utilities > Scout Indexes:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'purchasables_stores.hasUnlimitedStock' in 'where clause'
The SQL being executed was: SELECT COUNT(*)
FROM `elements` `elements`
[rest of query]

Workaround

If I remove ->hasVariant(['hasStock' => true]) from my query that error goes away, but non-stock items are going to be indexed.

Stack Trace


PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'purchasables_stores.hasUnlimitedStock' in 'where clause' in /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/db/Command.php:1302
Stack trace:
#0 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/db/Command.php(1302): PDOStatement->execute()
#1 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/db/Command.php(1168): yii\db\Command->internalExecute()
#2 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/db/Command.php(436): yii\db\Command->queryInternal()
#3 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/db/Query.php(497): yii\db\Command->queryScalar()
#4 /home/forge/jc.sunscreem.xyz/vendor/craftcms/cms/src/db/Query.php(366): yii\db\Query->queryScalar()
#5 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/db/Query.php(368): craft\db\Query->queryScalar()
#6 /home/forge/jc.sunscreem.xyz/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1723): yii\db\Query->count()
#7 /home/forge/jc.sunscreem.xyz/vendor/studioespresso/craft-scout/src/utilities/ScoutUtility.php(56): craft\elements\db\ElementQuery->count()
#8 [internal function]: rias\scout\utilities\ScoutUtility::rias\scout\utilities\{closure}()
#9 /home/forge/jc.sunscreem.xyz/vendor/illuminate/collections/Arr.php(600): array_map()
#10 /home/forge/jc.sunscreem.xyz/vendor/illuminate/collections/Collection.php(777): Illuminate\Support\Arr::map()
#11 /home/forge/jc.sunscreem.xyz/vendor/studioespresso/craft-scout/src/utilities/ScoutUtility.php(33): Illuminate\Support\Collection->map()
#12 /home/forge/jc.sunscreem.xyz/vendor/craftcms/cms/src/controllers/UtilitiesController.php(89): rias\scout\utilities\ScoutUtility::contentHtml()
#13 [internal function]: craft\controllers\UtilitiesController->actionShowUtility()
#14 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()
#15 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams()
#16 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction()
#17 /home/forge/jc.sunscreem.xyz/vendor/craftcms/cms/src/web/Application.php(349): yii\base\Module->runAction()
#18 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/web/Application.php(103): craft\web\Application->runAction()
#19 /home/forge/jc.sunscreem.xyz/vendor/craftcms/cms/src/web/Application.php(317): yii\web\Application->handleRequest()
#20 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest()
#21 /home/forge/jc.sunscreem.xyz/web/index.php(14): yii\base\Application->run()
#22 {main}

Next yii\db\Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'purchasables_stores.hasUnlimitedStock' in 'where clause'
The SQL being executed was: SELECT COUNT(*)
FROM `elements` `elements`
INNER JOIN `commerce_products` `commerce_products` ON `commerce_products`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
WHERE (EXISTS (SELECT *
FROM (SELECT `commerce_variants`.`primaryOwnerId`
FROM (SELECT `catalogprices`.`price`, `catalogpromotionalprices`.`price` AS `promotionalPrice`, `catalogsaleprices`.`price` AS `salePrice`, `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `siteSettingsId`
FROM `elements` `elements`
INNER JOIN `commerce_variants` `commerce_variants` ON `commerce_variants`.`id` = `elements`.`id`
INNER JOIN `elements_owners` `elements_owners` ON (`elements_owners`.`elementId` = `elements`.`id`) AND (`elements_owners`.`ownerId` = `commerce_variants`.`primaryOwnerId`)
LEFT JOIN `commerce_products` `commerce_products` ON `elements_owners`.`ownerId` = `commerce_products`.`id`
LEFT JOIN `commerce_producttypes` `commerce_producttypes` ON `commerce_products`.`typeId` = `commerce_producttypes`.`id`
INNER JOIN `commerce_purchasables` `commerce_purchasables` ON `commerce_purchasables`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
LEFT JOIN `commerce_site_stores` `sitestores` ON `elements_sites`.`siteId` = `sitestores`.`siteId`
LEFT JOIN `commerce_purchasables_stores` `purchasables_stores` ON `purchasables_stores`.`storeId` = `sitestores`.`storeId` AND `purchasables_stores`.`purchasableId` = `commerce_purchasables`.`id`
LEFT JOIN (SELECT MIN(price) as price, `cp`.`purchasableId`, `cp`.`storeId`
FROM `commerce_catalogpricing` `cp`
WHERE ((`catalogPricingRuleId` IS NULL) OR (`catalogPricingRuleId` IN (SELECT `cpr`.`id` AS `cprid`
FROM `commerce_catalogpricingrules` `cpr`
LEFT JOIN `commerce_catalogpricingrules_users` `cpru` ON `cpr`.`id` = `cpru`.`catalogPricingRuleId`
WHERE `cpru`.`id` IS NULL
GROUP BY `cpr`.`id`)) OR (`catalogPricingRuleId` IN (SELECT `cpr`.`id` AS `cprid`
FROM `commerce_catalogpricingrules` `cpr`
LEFT JOIN `commerce_catalogpricingrules_users` `cpru` ON `cpr`.`id` = `cpru`.`catalogPricingRuleId`
WHERE (`cpru`.`userId`=15901) AND (NOT (`cpru`.`id` IS NULL))
GROUP BY `cpr`.`id`))) AND ((`dateFrom` IS NULL) OR (`dateFrom` <= '2024-05-17 17:40:03')) AND ((`dateTo` IS NULL) OR (`dateTo` >= '2024-05-17 17:40:03')) AND (`isPromotionalPrice`=FALSE)
GROUP BY `purchasableId`, `storeId`
ORDER BY `purchasableId`, `price`) `catalogprices` ON `catalogprices`.`purchasableId` = `commerce_purchasables`.`id` AND `catalogprices`.`storeId` = `sitestores`.`storeId`
LEFT JOIN (SELECT MIN(price) as price, `cp`.`purchasableId`, `cp`.`storeId`
FROM `commerce_catalogpricing` `cp`
WHERE ((`catalogPricingRuleId` IS NULL) OR (`catalogPricingRuleId` IN (SELECT `cpr`.`id` AS `cprid`
FROM `commerce_catalogpricingrules` `cpr`
LEFT JOIN `commerce_catalogpricingrules_users` `cpru` ON `cpr`.`id` = `cpru`.`catalogPricingRuleId`
WHERE `cpru`.`id` IS NULL
GROUP BY `cpr`.`id`)) OR (`catalogPricingRuleId` IN (SELECT `cpr`.`id` AS `cprid`
FROM `commerce_catalogpricingrules` `cpr`
LEFT JOIN `commerce_catalogpricingrules_users` `cpru` ON `cpr`.`id` = `cpru`.`catalogPricingRuleId`
WHERE (`cpru`.`userId`=15901) AND (NOT (`cpru`.`id` IS NULL))
GROUP BY `cpr`.`id`))) AND ((`dateFrom` IS NULL) OR (`dateFrom` <= '2024-05-17 17:40:03')) AND ((`dateTo` IS NULL) OR (`dateTo` >= '2024-05-17 17:40:03')) AND (`isPromotionalPrice`=TRUE)
GROUP BY `purchasableId`, `storeId`
ORDER BY `purchasableId`, `price`) `catalogpromotionalprices` ON `catalogpromotionalprices`.`purchasableId` = `commerce_purchasables`.`id` AND `catalogpromotionalprices`.`storeId` = `sitestores`.`storeId`
LEFT JOIN (SELECT MIN(price) as price, `cp`.`purchasableId`, `cp`.`storeId`
FROM `commerce_catalogpricing` `cp`
WHERE ((`catalogPricingRuleId` IS NULL) OR (`catalogPricingRuleId` IN (SELECT `cpr`.`id` AS `cprid`
FROM `commerce_catalogpricingrules` `cpr`
LEFT JOIN `commerce_catalogpricingrules_users` `cpru` ON `cpr`.`id` = `cpru`.`catalogPricingRuleId`
WHERE `cpru`.`id` IS NULL
GROUP BY `cpr`.`id`)) OR (`catalogPricingRuleId` IN (SELECT `cpr`.`id` AS `cprid`
FROM `commerce_catalogpricingrules` `cpr`
LEFT JOIN `commerce_catalogpricingrules_users` `cpru` ON `cpr`.`id` = `cpru`.`catalogPricingRuleId`
WHERE (`cpru`.`userId`=15901) AND (NOT (`cpru`.`id` IS NULL))
GROUP BY `cpr`.`id`))) AND ((`dateFrom` IS NULL) OR (`dateFrom` <= '2024-05-17 17:40:03')) AND ((`dateTo` IS NULL) OR (`dateTo` >= '2024-05-17 17:40:03'))
GROUP BY `purchasableId`, `storeId`
ORDER BY `purchasableId`, `price`) `catalogsaleprices` ON `catalogsaleprices`.`purchasableId` = `commerce_purchasables`.`id` AND `catalogsaleprices`.`storeId` = `sitestores`.`storeId`
LEFT JOIN `commerce_inventoryitems` `inventoryitems` ON `inventoryitems`.`purchasableId` = `commerce_purchasables`.`id`
WHERE ((`purchasables_stores`.`inventoryTracked`=FALSE) OR ((NOT (`purchasables_stores`.`inventoryTracked`=FALSE)) AND (`purchasables_stores`.`stock` > 0))) AND ((`purchasables_stores`.`hasUnlimitedStock`=TRUE) OR ((NOT (`purchasables_stores`.`hasUnlimitedStock`=TRUE)) AND (`purchasables_stores`.`stock` > 0))) AND (NOT (`commerce_variants`.`primaryOwnerId` IS NULL)) AND ((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `elements_owners`.`sortOrder`) `subquery`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`siteSettingsId`
INNER JOIN `commerce_variants` `commerce_variants` ON `commerce_variants`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_owners` `elements_owners` ON (`elements_owners`.`elementId` = `elements`.`id`) AND (`elements_owners`.`ownerId` = `commerce_variants`.`primaryOwnerId`)
LEFT JOIN `commerce_products` `commerce_products` ON `elements_owners`.`ownerId` = `commerce_products`.`id`
LEFT JOIN `commerce_producttypes` `commerce_producttypes` ON `commerce_products`.`typeId` = `commerce_producttypes`.`id`
LEFT JOIN `elements_sites` `commerce_products_elements_sites` ON `elements_owners`.`ownerId` = `commerce_products_elements_sites`.`elementId` and `commerce_products_elements_sites`.`siteId` =  `elements_sites`.`siteId`
INNER JOIN `commerce_purchasables` `commerce_purchasables` ON `commerce_purchasables`.`id` = `subquery`.`elementsId`
LEFT JOIN `commerce_site_stores` `sitestores` ON `elements_sites`.`siteId` = `sitestores`.`siteId`
LEFT JOIN `commerce_purchasables_stores` `purchasables_stores` ON `purchasables_stores`.`storeId` = `sitestores`.`storeId` AND `purchasables_stores`.`purchasableId` = `commerce_purchasables`.`id`
LEFT JOIN `commerce_inventoryitems` `inventoryitems` ON `inventoryitems`.`purchasableId` = `commerce_purchasables`.`id`
ORDER BY `elements_owners`.`sortOrder`) `existssub`
WHERE `existssub`.`primaryOwnerId`=`commerce_products`.`id`)) AND ((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL) in /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/db/Schema.php:676
Stack trace:
#0 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException()
#1 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/db/Command.php(1168): yii\db\Command->internalExecute()
#2 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/db/Command.php(436): yii\db\Command->queryInternal()
#3 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/db/Query.php(497): yii\db\Command->queryScalar()
#4 /home/forge/jc.sunscreem.xyz/vendor/craftcms/cms/src/db/Query.php(366): yii\db\Query->queryScalar()
#5 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/db/Query.php(368): craft\db\Query->queryScalar()
#6 /home/forge/jc.sunscreem.xyz/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1723): yii\db\Query->count()
#7 /home/forge/jc.sunscreem.xyz/vendor/studioespresso/craft-scout/src/utilities/ScoutUtility.php(56): craft\elements\db\ElementQuery->count()
#8 [internal function]: rias\scout\utilities\ScoutUtility::rias\scout\utilities\{closure}()
#9 /home/forge/jc.sunscreem.xyz/vendor/illuminate/collections/Arr.php(600): array_map()
#10 /home/forge/jc.sunscreem.xyz/vendor/illuminate/collections/Collection.php(777): Illuminate\Support\Arr::map()
#11 /home/forge/jc.sunscreem.xyz/vendor/studioespresso/craft-scout/src/utilities/ScoutUtility.php(33): Illuminate\Support\Collection->map()
#12 /home/forge/jc.sunscreem.xyz/vendor/craftcms/cms/src/controllers/UtilitiesController.php(89): rias\scout\utilities\ScoutUtility::contentHtml()
#13 [internal function]: craft\controllers\UtilitiesController->actionShowUtility()
#14 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()
#15 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams()
#16 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction()
#17 /home/forge/jc.sunscreem.xyz/vendor/craftcms/cms/src/web/Application.php(349): yii\base\Module->runAction()
#18 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/web/Application.php(103): craft\web\Application->runAction()
#19 /home/forge/jc.sunscreem.xyz/vendor/craftcms/cms/src/web/Application.php(317): yii\web\Application->handleRequest()
#20 /home/forge/jc.sunscreem.xyz/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest()
#21 /home/forge/jc.sunscreem.xyz/web/index.php(14): yii\base\Application->run()
#22 {main}
Additional Information:
Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'purchasables_stores.hasUnlimitedStock' in 'where clause'
)
janhenckens commented 1 month ago

Hey @sunscreem, which version of Craft and Commerce are you running?

Seems like a problem with the query and possibly with Commerce - not with Scout.

I tried this with a fresh Commerce install and on my end the ->hasVariant(['hasStock' => true]) doesn't throw any errors. (on the latest Craft 4 & matching Commerce version).

sunscreem commented 1 month ago

Hey @janhenckens I just did a clean install of Craft 5, Craft Commerce and Scout, copied over my scout.php and got the same error.

So it's either a commerce issue or my scout config. So - I agree - I'm going to close this. I'll keep looking into it.

sunscreem commented 1 month ago

Hey @janhenckens Yup - not a scout issue.

Thanks again.

https://github.com/craftcms/commerce/issues/3505