SQLSTATE[42S22]: Column not found: 1054 Unknown column 'craft_elements.id' in 'group statement'

[michaelrog]

{% set testProduct = craft.products({id: 19641}).one() %}

{% set similarProducts = craft.similar.find({ element: testProduct, context: testProduct.productKeywords, critieria: craft.products.limit(3) }) %}

{% for similarProduct in similarProducts %}
   ...
{% endfor %}

...produces a lovely SQL error...

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'craft_elements.id' in 'group statement'
The SQL being executed was: SELECT `elements`.`id`, `elements_sites`.`siteId`, COUNT(*) as count
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `craft_elements` `elements`
INNER JOIN `craft_commerce_products` `commerce_products` ON `commerce_products`.`id` = `elements`.`id`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `craft_content` `content` ON `content`.`elementId` = `elements`.`id`
LEFT JOIN `craft_relations` ON elements.id=`craft_relations`.sourceId
WHERE (`elements_sites`.`siteId`='1') AND ((elements.id != '19641') AND (`craft_relations`.`targetId` IN ('9445', '8021', '8208', '921', '15773', '18230'))) AND (`content`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`commerce_products`.`postDate` <= '2018-07-22 00:50:42') AND ((`commerce_products`.`expiryDate` IS NULL) OR (`commerce_products`.`expiryDate` > '2018-07-22 00:50:42'))) AND (`elements_sites`.`enabled`=TRUE)
GROUP BY `craft_elements`.`id`
ORDER BY `commerce_products`.`postDate` DESC) `subquery`
INNER JOIN `craft_commerce_products` `commerce_products` ON `commerce_products`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `craft_content` `content` ON `content`.`id` = `subquery`.`contentId`
LEFT JOIN `craft_relations` ON elements.id=`craft_relations`.sourceId
GROUP BY `craft_relations`.`sourceId`
ORDER BY `count` DESC

Caused by: PDOException SQLSTATE[42S22]: Column not found: 1054 Unknown column 'craft_elements.id' in 'group statement' in .../vendor/yiisoft/yii2/db/Command.php at line 1258

(Possibly related to the fix for #1 introduced in v1.0.1)

[khalwat]

It works if you don't have a database prefix... anyway, fixed in: https://github.com/nystudio107/craft-similar/commit/b816ad0acb36cddb1fc33ab3190ce8b0256d4680

[JayBox325]

Hi @khalwat,

I have just installed version 1.0.4 and I've tried to get similar products based on a category field and when I have the following code I get the same error code as above, but a different SQL error. Here is my tag:

craft.similar.find({ element: product, context: product.suitability })

I was wondering if it was product potentially not being set, but this has 100% been set as it is being used elsewhere in the template. But here's the SQL response I'm getting with the error message:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'structureelements.lft' in 'group statement'
The SQL being executed was: SELECT `elements`.`id`, `elements_sites`.`siteId`, COUNT(*) as count
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `craft_elements` `elements`
INNER JOIN `craft_commerce_products` `commerce_products` ON `commerce_products`.`id` = `elements`.`id`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `craft_content` `content` ON `content`.`elementId` = `elements`.`id`
LEFT JOIN `craft_relations` ON elements.id=`craft_relations`.sourceId
WHERE (`elements_sites`.`siteId`='1') AND ((elements.id != '183') AND (0=1)) AND (`content`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`commerce_products`.`postDate` <= '2018-12-20 12:28:55') AND ((`commerce_products`.`expiryDate` IS NULL) OR (`commerce_products`.`expiryDate` > '2018-12-20 12:28:55'))) AND (`elements_sites`.`enabled`=TRUE)
GROUP BY `structureelements`.`lft`, `elements`.`id`
ORDER BY `commerce_products`.`postDate` DESC) `subquery`
INNER JOIN `craft_commerce_products` `commerce_products` ON `commerce_products`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `craft_content` `content` ON `content`.`id` = `subquery`.`contentId`
LEFT JOIN `craft_relations` ON elements.id=`craft_relations`.sourceId
WHERE 0=1
GROUP BY `craft_relations`.`sourceId`, `elements`.`id`
ORDER BY `count` DESC

Merry Christmas to you, Andrew! 🎄😂