[5.x]: Commerce Variants field throws SQL "Column not found" error

[benfeather]

What happened?

Description

I am using the new product structure option:

When I try to use the "Commerce Variant" field, I get the following SQL error when I try to add a variant to an entry.
The "Commerce Product" field seems to work.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'lft' in 'order clause' The SQL being executed was: SELECT `elements`.`id`, `elements`.`canonicalId`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateLastMerged`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`siteId`, `elements_sites`.`title`, `elements_sites`.`slug`, `elements_sites`.`uri`, `elements_sites`.`content`, `elements_sites`.`enabled` AS `enabledForSite`, `commerce_variants`.`id`, `commerce_variants`.`primaryOwnerId`, CASE WHEN `commerce_variants`.`id` = `commerce_products`.`defaultVariantId` THEN TRUE ELSE FALSE END AS `isDefault`, `commerce_products_elements_sites`.`slug` AS `productSlug`, `commerce_producttypes`.`handle` AS `productTypeHandle`, `elements_owners`.`ownerId`, `elements_owners`.`sortOrder`, `commerce_purchasables`.`sku`, `commerce_purchasables`.`width`, `commerce_purchasables`.`height`, `commerce_purchasables`.`length`, `commerce_purchasables`.`weight`, `commerce_purchasables`.`taxCategoryId`, `purchasables_stores`.`availableForPurchase`, `purchasables_stores`.`basePrice`, `purchasables_stores`.`basePromotionalPrice`, `purchasables_stores`.`freeShipping`, `purchasables_stores`.`maxQty`, `purchasables_stores`.`minQty`, `purchasables_stores`.`inventoryTracked`, `purchasables_stores`.`allowOutOfStockPurchases`, `purchasables_stores`.`promotable`, `purchasables_stores`.`shippingCategoryId`, `subquery`.`price`, `subquery`.`promotionalPrice` AS `promotionalPrice`, `subquery`.`salePrice` AS `salePrice`, `inventoryitems`.`id` AS `inventoryItemId` FROM (SELECT `catalogprices`.`price`, `catalogprices`.`promotionalPrice`, `catalogprices`.`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(CASE WHEN `isPromotionalPrice` = FALSE THEN `price` END) AS `price`, MIN(CASE WHEN `isPromotionalPrice` = TRUE THEN `price` END) AS `promotionalPrice`, MIN(`price`) AS `salePrice`, `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`=1) AND (NOT (`cpru`.`id` IS NULL)) GROUP BY `cpr`.`id`))) AND ((`dateFrom` IS NULL) OR (`dateFrom` <= '2025-03-16 23:50:36')) AND ((`dateTo` IS NULL) OR (`dateTo` >= '2025-03-16 23:50:36')) GROUP BY `purchasableId`, `storeId`) `catalogprices` ON `catalogprices`.`purchasableId` = `commerce_purchasables`.`id` AND `catalogprices`.`storeId` = `sitestores`.`storeId` LEFT JOIN `commerce_inventoryitems` `inventoryitems` ON `inventoryitems`.`purchasableId` = `commerce_purchasables`.`id` WHERE (`commerce_products`.`typeId`=2) AND (`elements_sites`.`siteId`=1) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL) ORDER BY `lft` LIMIT 100) `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 `lft`

Craft CMS version

Craft Pro 5.6.11

Craft Commerce version

5.3.5

PHP version

8.3.17

Operating system and version

No response

Database type and version

MySQL 8.0.40

Image driver and version

No response

Installed plugins and versions

[linear]

PT-2651 [5.x]: Commerce Variants field throws SQL "Column not found" error

[lukeholder]

Thanks for reporting this! I have fixed it for the next release in this PR: #3947

We will update this issue once the release is out.

[nfourtythree]

Commerce 5.3.6 has been released with this fix included.

Thanks!