OliveStudio / olivemenus

A powerful menus plugin for Craft CMS built for the need of simplicity and flexibility
MIT License
40 stars 22 forks source link

SQL error with PostgreSQL database #63

Open nicodeboer opened 1 month ago

nicodeboer commented 1 month ago

When using a PostgreSQL database opening a menu throws an error:

Database Exception – [yii\db\Exception](https://www.yiiframework.com/doc-2.0/yii-db-exception.html)

SQLSTATE[42703]: Undefined column: 7 ERROR: column sections_sites.sectionid does not exist
LINE 3: LEFT JOIN "sections_sites" ON "sections_sites".sectionId = "...
^
HINT: Perhaps you meant to reference the column "sections_sites.sectionId".
The SQL being executed was: SELECT "name", "handle"
FROM "sections"
LEFT JOIN "sections_sites" ON "sections_sites".sectionId = "sections".id
WHERE ("type"='single') AND ("dateDeleted" IS NULL) AND ("siteId"=2)
ORDER BY "name"
Error Info: Array
(
    [0] => 42703
    [1] => 7
    [2] => ERROR:  column sections_sites.sectionid does not exist
LINE 3: LEFT JOIN "sections_sites" ON "sections_sites".sectionId = "...
                                      ^
HINT:  Perhaps you meant to reference the column "sections_sites.sectionId".
)
↵
Caused by: PDOException

SQLSTATE[42703]: Undefined column: 7 ERROR: column sections_sites.sectionid does not exist
LINE 3: LEFT JOIN "sections_sites" ON "sections_sites".sectionId = "...
^
HINT: Perhaps you meant to reference the column "sections_sites.sectionId".

in /var/www/vhosts/yellowbello.nl/staging/vendor/yiisoft/yii2/db/Command.php at line 1320

This can be fixed by rewriting the queries in OlivemenuItemsService::getSections() as follows:

<?php

...

use craft\db\Table;

...

    private function getSections(int $site_id): mixed
    {
        $sections = [];

        $sections['single'] = (new \craft\db\Query())
            ->select(["name AS name", "handle AS handle"])
            ->from(['sections' => Table::SECTIONS])
            ->leftJoin(['sections_sites' => Table::SECTIONS_SITES], '[[sections_sites.sectionId]] = [[sections.id]]')
            ->where(['type' => 'single', 'dateDeleted'=>NULL, 'siteId' => $site_id])
            ->orderBy('name')
            ->all();

        $sections['structure'] = (new \craft\db\Query())
            ->select(["name AS name", "handle AS handle"])
            ->from(['sections' => Table::SECTIONS])
            ->leftJoin(['sections_sites' => Table::SECTIONS_SITES], '[[sections_sites.sectionId]] = [[sections.id]]')
            ->where(['type' => 'structure', 'dateDeleted'=>NULL, 'siteId' => $site_id])
            ->orderBy('name')
            ->all();

        $sections['channel'] = (new \craft\db\Query())
            ->select(["name AS name", "handle AS handle"])
            ->from(['sections' => Table::SECTIONS])
            ->leftJoin(['sections_sites' => Table::SECTIONS_SITES], '[[sections_sites.sectionId]] = [[sections.id]]')
            ->where(['type' => 'channel', 'dateDeleted'=>NULL, 'siteId' => $site_id])
            ->orderBy('name')
            ->all();

        return $sections;
    }
nicodeboer commented 1 month ago

Ah, I see there is a pull request open since April 2022 for this...