Craft 5 upgrade - database error - SQLSTATE[42S22]: Column not found: 1054 Unknown column 'content.id' in 'group statement'

[alexagui]

Summary

We’re upgrading a Craft Commerce site to Craft 5 and running into a databaese error.

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

We believe the problem is with line 203 in vendor/nystudio107/craft-similar/src/services/Similar.php

Included snippet of template code below.

Screenshots

Versions

• Similar: 4.0.1

• Craft: Craft Pro 4.9.5

• Commerce: 4.6.1

• Database: MySQL 8.0.36

• PHP: 8.3.3

Template snippet

{% set relatedProducts = product.relatedProducts.with("productImages") %}
                        {% set limit = 4 - relatedProducts|length %}
                        {% set criteria = craft.products.with("productImages").productImages(":notempty:").limit(limit) %}

                        {% set similarProducts = craft.similar.find({
                                element: product,
                                context: [(category ? category.id), categoryParent.id],
                                criteria: criteria
                            }) %}

                        {% for rProduct in relatedProducts|merge(similarProducts) %}
                            {{ macros.productListing(rProduct, rProduct.productImages, cart, false ) }}
                        {% endfor %}

[khalwat]

I'm confused... this plugin hasn't been ported to Craft 5 yet?

[alexagui]

Oh the Craft 5 upgrade utility says it's ready.

[khalwat]

haha my bad, I don't know what I was thinking. It has indeed been ported to Craft 5.

[alexagui]

I believe the plugin is still referencing a content table which no longer exists in Craft 5.

[khalwat]

Yep, sure looks like it, in services/Similar.php:

        $query->subQuery->groupBy(['elements.id', 'content.id', 'elements_sites.id']);

Try changing that to:

        $query->subQuery->groupBy(['elements.id', 'elements_sites.id']);

and see if it works for you?

[khalwat]

Addressed in the above commit

You can try it now by setting your semver in your composer.json to look like this:

    "nystudio107/craft-similar": "dev-develop-v5 as 5.0.1”,

Then do a composer clear-cache && composer update

Let me know how it works out for you!

[alexagui]

Thanks for the prompt.

Actually we did run into another issue after trying the Craft 5 upgrade again.

[khalwat]

So you're saying that you tried the version of Similar in the dev-develop-v5 branch, and you then ran into this issue?

Can you post the full text of the error rather than a screenshot, hopefully including the PHP code line numbers, etc?

[alexagui]

Here you go. Thanks Andrew!

8. in /var/www/html/vendor/nystudio107/craft-similar/src/services/Similar.php at line 114 – craft\elements\db\ElementQuery::all()
108109110111112113114115116117118119120

            $query->andWhere(['elements_sites.siteId' => $element->siteId]);
        }
 
        $query->andWhere(['in', 'relations.targetId', $tagIds]);
        $query->leftJoin(['relations' => Table::RELATIONS], '[[elements.id]] = [[relations.sourceId]]');
 
        $results = $query->all();
 
        // Fetch the elements based on the returned `id` and `siteId`
        $queryConditions = [];
        $similarCounts = [];
 
        // Build the query conditions for a new element query.

9. in /var/www/html/vendor/nystudio107/craft-similar/src/variables/SimilarVariable.php at line 35 – nystudio107\similar\services\Similar::find()
293031323334353637

     *
     * @return array|ElementInterface
     * @throws Exception
     */
    public function find(array $data): array|ElementInterface
    {
        return Similar::$plugin->similar->find($data);
    }
}

10. in /var/www/html/vendor/twig/twig/src/Extension/CoreExtension.php at line 1635 – nystudio107\similar\variables\SimilarVariable::find()
11. in /var/www/html/vendor/craftcms/cms/src/helpers/Template.php at line 141 – twig_get_attribute()
135136137138139140141142143144145146147

            if (is_object($value) && get_class($value) === Markup::class) {
                $arguments[$key] = (string)$value;
            }
        }
 
        try {
            return twig_get_attribute(
                $env,
                $source,
                $object,
                $item,
                $arguments,
                $type,

[khalwat]

Alright, I think I've got it. Addressed in the above commit. Do this again and it should update with the latest changes from the dev-develop-v5 branch, and let me know how you go.

You can try it now by setting your semver in your composer.json to look like this:

    "nystudio107/craft-similar": "dev-develop-v5 as 5.0.1”,

Then do a composer clear-cache && composer update

Let me know how it works out for you!

[khalwat]

I've released version 5.0.1 -> https://github.com/nystudio107/craft-similar/releases/tag/5.0.1

Which fixes two verified, legit bugs. If there are more issues you find after updating to that version, I can continue to work with you on them.

[alexagui]

Thanks Andrew. Unfortunately, we're still getting an error.

Database Exception – yii\db\Exception
SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'order clause'
The SQL being executed was: SELECT `elements`.`id`, `elements_sites`.`siteId`, COUNT(*) AS `count`
FROM (SELECT `catalogprices`.`price`, `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `siteSettingsId`
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`
LEFT JOIN `relations` `relations` ON `elements`.`id` = `relations`.`sourceId`
LEFT JOIN `commerce_site_stores` `sitestores` ON `elements_sites`.`siteId` = `sitestores`.`siteId`
LEFT JOIN (SELECT MIN(price) as price, `cp`.`purchasableId`, `cp`.`storeId`
FROM `commerce_catalogpricing` `cp`
LEFT JOIN `commerce_variants` `purvariants` ON `purvariants`.`id` = `cp`.`purchasableId`
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`))) AND ((`dateFrom` IS NULL) OR (`dateFrom` <= '2024-06-19 19:36:34')) AND ((`dateTo` IS NULL) OR (`dateTo` >= '2024-06-19 19:36:34')) AND (`purvariants`.`isDefault`=TRUE) AND (`isPromotionalPrice`=FALSE)
GROUP BY `purchasableId`, `storeId`
ORDER BY `purchasableId`, `price`) `catalogprices` ON `catalogprices`.`purchasableId` = `commerce_products`.`defaultVariantId` AND `catalogprices`.`storeId` = `sitestores`.`storeId`
WHERE (NOT (`elements`.`id`=6660)) AND (`elements_sites`.`siteId`=1) AND (`relations`.`targetId` IN (6340, 13)) AND (EXISTS (SELECT *
FROM `relations` `relations_productImages_lpeya`
INNER JOIN `elements` `elements_productImages_lpeya` ON `elements_productImages_lpeya`.`id` = `relations_productImages_lpeya`.`targetId`
LEFT JOIN `elements_sites` `elements_sites_productImages_lpeya` ON `elements_sites_productImages_lpeya`.`elementId` = `elements_productImages_lpeya`.`id`
WHERE (`relations_productImages_lpeya`.`sourceId` = `elements`.`id`) AND ((`relations_productImages_lpeya`.`fieldId`=53) AND (`elements_productImages_lpeya`.`dateDeleted` IS NULL)) AND ((`relations_productImages_lpeya`.`sourceSiteId` IS NULL) OR (`relations_productImages_lpeya`.`sourceSiteId`=`elements_sites`.`siteId`)) AND ((`elements_productImages_lpeya`.`enabled`=TRUE) AND (`elements_sites_productImages_lpeya`.`enabled`=TRUE)) AND (`elements_sites_productImages_lpeya`.`siteId`=`elements_sites`.`siteId`))) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`commerce_products`.`postDate` <= '2024-06-19 19:36:34') AND ((`commerce_products`.`expiryDate` IS NULL) OR (`commerce_products`.`expiryDate` > '2024-06-19 19:36:34'))) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
GROUP BY `elements`.`id`, `elements_sites`.`id`
ORDER BY `0`) `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_products` `commerce_products` ON `commerce_products`.`id` = `subquery`.`elementsId`
LEFT JOIN `relations` `relations` ON `elements`.`id` = `relations`.`sourceId`
WHERE `relations`.`targetId` IN (6340, 13)
GROUP BY `relations`.`sourceId`, `elements`.`id`, `elements_sites`.`siteId`
ORDER BY `count`, `0`
LIMIT 4

Error Info: Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column '0' in 'order clause'
)

↵
Caused by: PDOException
SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'order clause'

in /var/www/html/vendor/yiisoft/yii2/db/Command.php at line 1320

    1. in /var/www/html/vendor/yiisoft/yii2/db/Schema.php at line 676
    667668669670671672673674675676677678679680681682683684685

     
            $exceptionClass = '\yii\db\Exception';
            foreach ($this->exceptionMap as $error => $class) {
                if (strpos($e->getMessage(), $error) !== false) {
                    $exceptionClass = $class;
                }
            }
            $message = $e->getMessage() . "\nThe SQL being executed was: $rawSql";
            $errorInfo = $e instanceof \PDOException ? $e->errorInfo : null;
            return new $exceptionClass($message, $errorInfo, $e->getCode(), $e);
        }
     
        /**
         * Returns a value indicating whether a SQL statement is for read purpose.
         * @param string $sql the SQL statement
         * @return bool whether a SQL statement is for read purpose.
         */
        public function isReadQuery($sql)
        {

    2. in /var/www/html/vendor/yiisoft/yii2/db/Command.php at line 1325 – yii\db\Schema::convertException()
    3. in /var/www/html/vendor/yiisoft/yii2/db/Command.php at line 1186 – yii\db\Command::internalExecute()
    4. in /var/www/html/vendor/yiisoft/yii2/db/Command.php at line 417 – yii\db\Command::queryInternal()
    5. in /var/www/html/vendor/yiisoft/yii2/db/Query.php at line 249 – yii\db\Command::queryAll()
    6. in /var/www/html/vendor/craftcms/cms/src/db/Query.php at line 256 – yii\db\Query::all()
    250251252253254255256257258259260261262

         * @inheritdoc
         * @return array<TKey,TValue>
         */
        public function all($db = null): array
        {
            try {
                return parent::all($db);
            } catch (QueryAbortedException) {
                return [];
            }
        }
     
        /**

    7. in /var/www/html/vendor/craftcms/cms/src/elements/db/ElementQuery.php at line 1778 – craft\db\Query::all()
    1772177317741775177617771778177917801781178217831784

                if ($this->with) {
                    Craft::$app->getElements()->eagerLoadElements($this->elementType, $cachedResult, $this->with);
                }
                return $cachedResult;
            }
     
            return $this->eagerLoad()?->all() ?? parent::all($db);
        }
     
        /**
         * @param YiiConnection|null $db
         * @return ElementCollection<TKey,TElement>
         */

    8. in /var/www/html/vendor/nystudio107/craft-similar/src/services/Similar.php at line 115 – craft\elements\db\ElementQuery::all()
    109110111112113114115116117118119120121

                $query->andWhere(['elements_sites.siteId' => $element->siteId]);
            }
     
            $query->andWhere(['in', 'relations.targetId', $tagIds]);
            $query->leftJoin(['relations' => Table::RELATIONS], '[[elements.id]] = [[relations.sourceId]]');
     
            $results = $query->all();
     
            // Fetch the elements based on the returned `id` and `siteId`
            $queryConditions = [];
            $similarCounts = [];
     
            // Build the query conditions for a new element query.

    9. in /var/www/html/vendor/nystudio107/craft-similar/src/variables/SimilarVariable.php at line 35 – nystudio107\similar\services\Similar::find()
    293031323334353637

         *
         * @return array|ElementInterface
         * @throws Exception
         */
        public function find(array $data): array|ElementInterface
        {
            return Similar::$plugin->similar->find($data);
        }
    }

    10. in /var/www/html/vendor/twig/twig/src/Extension/CoreExtension.php at line 1635 – nystudio107\similar\variables\SimilarVariable::find()
    11. in /var/www/html/vendor/craftcms/cms/src/helpers/Template.php at line 141 – twig_get_attribute()
    135136137138139140141142143144145146147

                if (is_object($value) && get_class($value) === Markup::class) {
                    $arguments[$key] = (string)$value;
                }
            }
     
            try {
                return twig_get_attribute(
                    $env,
                    $source,
                    $object,
                    $item,
                    $arguments,
                    $type,

    12. in /var/www/html/templates/products/_product.html at line 160 – craft\helpers\Template::attribute()
    154155156157158159160161162163164165166

                        <h2 class="text-center text-3xl font-inter-tight tracking-tight text-arlex-logo-maroon mb-3 md:mb-6">You May Also Like</h2>
                        <div class="grid grid-cols-2 lg:grid-cols-4 gap-6 justify-center">
                            {% set relatedProducts = product.relatedProducts.with("productImages") %}
                            {% set limit = 4 - relatedProducts|length %}
                            {% set criteria = craft.products.with("productImages").productImages(":notempty:").limit(limit) %}
     
                            {% set similarProducts = craft.similar.find({
                                    element: product,
                                    context: [(category ? category.id), categoryParent.id],
                                    criteria: criteria
                                }) %}
     
                            {% for rProduct in relatedProducts|merge(similarProducts) %}

    13. in /var/www/html/vendor/twig/twig/src/Template.php at line 171 – __TwigTemplate_5f5b85bcc5f37933e8af5fa6750a1e1d::block_content()
    14. in /var/www/html/templates/_layouts/_layout.twig at line 35 – Twig\Template::displayBlock()
    29303132333435363738394041

            </div>
     
            <div class="offcanvas-wrapper">
     
                    <main>
                        {# -- Primary content block -- #}
                        {% block content %}
                        {% endblock %}
     
                        <div class="container mx-auto flex-grow">
                            {% block main %}
                            {% endblock %}
                        </div>

    15. in /var/www/html/vendor/twig/twig/src/Template.php at line 394 – __TwigTemplate_e04e4f62ec3c767eac13caa7df24a3f0::doDisplay()
    16. in /var/www/html/vendor/twig/twig/src/Template.php at line 367 – Twig\Template::displayWithErrorHandling()
    17. in /var/www/html/templates/products/_product.html at line 1 – Twig\Template::display()
    1234567

    {% extends '_layouts/_layout' %}
    {% import "_includes/macros" as macros %}
    {% do craft.app.elements.eagerLoadElements(
        className(product),
        [product],
        ['productImages', 'relatedProducts.productImages']
    ) %}

    18. in /var/www/html/vendor/twig/twig/src/Template.php at line 394 – __TwigTemplate_5f5b85bcc5f37933e8af5fa6750a1e1d::doDisplay()
    19. in /var/www/html/vendor/twig/twig/src/Template.php at line 367 – Twig\Template::displayWithErrorHandling()
    20. in /var/www/html/vendor/twig/twig/src/Template.php at line 379 – Twig\Template::display()
    21. in /var/www/html/vendor/twig/twig/src/TemplateWrapper.php at line 38 – Twig\Template::render()
    22. in /var/www/html/vendor/twig/twig/src/Environment.php at line 280 – Twig\TemplateWrapper::render()
    23. in /var/www/html/vendor/craftcms/cms/src/web/View.php at line 494 – Twig\Environment::render()
    488489490491492493494495496497498499500

     
            // Render and return
            $renderingTemplate = $this->_renderingTemplate;
            $this->_renderingTemplate = $template;
     
            try {
                $output = $this->getTwig()->render($template, $variables);
            } finally {
                $this->_renderingTemplate = $renderingTemplate;
                $this->setTemplateMode($oldTemplateMode);
            }
     
            $this->afterRenderTemplate($template, $variables, $templateMode, $output);

    24. in /var/www/html/vendor/craftcms/cms/src/web/View.php at line 547 – craft\web\View::renderTemplate()
    541542543544545546547548549550551552553

     
            $isRenderingPageTemplate = $this->_isRenderingPageTemplate;
            $this->_isRenderingPageTemplate = true;
     
            try {
                $this->beginPage();
                echo $this->renderTemplate($template, $variables);
                $this->endPage();
            } finally {
                $this->_isRenderingPageTemplate = $isRenderingPageTemplate;
                $this->setTemplateMode($oldTemplateMode);
                $output = ob_get_clean();
            }

    25. in /var/www/html/vendor/craftcms/cms/src/web/TemplateResponseFormatter.php at line 57 – craft\web\View::renderPageTemplate()
    51525354555657585960616263

            ) {
                $view->registerAssetBundle(ContentWindowAsset::class);
            }
     
            // Render and return the template
            try {
                $response->content = $view->renderPageTemplate($behavior->template, $behavior->variables, $behavior->templateMode);
            } catch (Throwable $e) {
                $previous = $e->getPrevious();
                if ($previous instanceof YiiExitException) {
                    // Something called Craft::$app->end()
                    if ($previous instanceof ExitException && $previous->output !== null) {
                        echo $previous->output;

    26. in /var/www/html/vendor/yiisoft/yii2/web/Response.php at line 1109 – craft\web\TemplateResponseFormatter::format()
    27. in /var/www/html/vendor/craftcms/cms/src/web/Response.php at line 338 – yii\web\Response::prepare()
    332333334335336337338339340341342343344

     
        /**
         * @inheritdoc
         */
        protected function prepare(): void
        {
            parent::prepare();
            $this->_isPrepared = true;
        }
     
        /**
         * Clear the output buffer to prevent corrupt downloads.
         *

    28. in /var/www/html/vendor/yiisoft/yii2/web/Response.php at line 340 – craft\web\Response::prepare()
    29. in /var/www/html/vendor/yiisoft/yii2/base/Application.php at line 390 – yii\web\Response::send()
    30. in /var/www/html/web/index.php at line 13 – yii\base\Application::run()
    78910111213

    // Load shared bootstrap
    require dirname(__DIR__) . '/bootstrap.php';
     
    // Load and run Craft
    /** @var craft\web\Application $app */
    $app = require CRAFT_VENDOR_PATH . '/craftcms/cms/bootstrap/web.php';
    $app->run();

$_COOKIE = [
    'e349f3cd050b66ac7b4f9b12d0345804_commerce_cart' => '0f9c6c8cd5705866030accaf39715775f19339d0278b4ee6ccda1b91b691aa59a:2:{i:0;s:46:"e349f3cd050b66ac7b4f9b12d0345804_commerce_cart";i:1;s:32:"369e1422ace1af2acbb6c89f05a65b6b";}',
    'db7ecff34996fb7ae8516af24249ec47_commerce_cart' => 'db154ccbb32b77d91429906af5f901e1a476e5d3cbee39676dcb4b7725b33325a:2:{i:0;s:46:"db7ecff34996fb7ae8516af24249ec47_commerce_cart";i:1;s:32:"c659801e999ed6cd47ede27d36e5d791";}',
    'CraftSessionId' => '9orbs2u5cidnt9j38d17qc8cb4',
    'CRAFT_CSRF_TOKEN' => 'abc123:2:{i:0;s:16:"CRAFT_CSRF_TOKEN";i:1;s:40:"abc123";}',
];

$_SESSION = [
    'bd62416aa8538ede709019a5e113eea5__flash' => [],
];

Yii Framework

2024-06-19, 15:36:34

nginx/1.26.1

Yii Framework/2.0.50

[khalwat]

@alexagui please 100% verify for me that you're running the release 5.0.1 version?

[alexagui]

Hi Andrew. Yes we tried it twice to confirm.

[khalwat]

Let's debug this together:

https://savvycal.com/nystudio107/chat

[alexagui]

Hi Andrew,

We traced it to line 229 in Similar.php

This worked for us:

P.S. We're currently moving so a bit crazy on my end.

[khalwat]

You gave me enough info here that I figured out what is going on here. Fixed in these tagged releases:

Craft 3: https://github.com/nystudio107/craft-similar/releases/tag/1.1.7

Craft 4: https://github.com/nystudio107/craft-similar/releases/tag/4.0.2

Craft 5: https://github.com/nystudio107/craft-similar/releases/tag/5.0.2

[khalwat]

We good to go @alexagui ?

[alexagui]

We are 👍

Thanks Andrew!