Unable to add/save custom columns for catalog

Help for integrating the Laravel package
Forum rules
Always add your Laravel, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
arthur.webware
Posts: 2
Joined: 21 Aug 2024, 11:24

Unable to add/save custom columns for catalog

Post by arthur.webware » 21 Aug 2024, 12:00

Hello,

I have encountered an issue while trying to add new fields to the catalog. I used a migration and was able to create new fields using aimeos:setup. However, the problem arises when I need to use, display, and save values for these fields.

According to the documentation https://aimeos.org/docs/2024.x/models/extend-managers/, the "Custom way" approach didn't work for me. The example provided is either incomplete or specific to products.

I couldn't find a description of what Node is. Could you please describe it or provide a link to what it is?

Nevertheless, I was partially able to figure it out and add the necessary code to display values from the database. But the last issue I face is saving the new values.

I am receiving the following error:

Code: Select all

SQLSTATE[08P01]: <<Unknown error>>: 7 ERROR:  bind message supplies 4 parameters, but prepared statement "" requires 7: 
				UPDATE "mshop_catalog"
				SET "label" = ?, "code" = ?, "status" = ?, "mainactive" = ?, "mainorder" = ?, "sortorder" = ?
				WHERE "siteid" = '1.' AND "id" = ?
			["new catalog label","new_catalog",1,"40"]
#0 /app/vendor/aimeos/aimeos-core/src/MW/Tree/Manager/DBNestedSet.php(459): Aimeos\Base\DB\Statement\DBAL\Prepared->execute()
#1 /app/packages/ultra/src/MShop/Catalog/Manager/Ustandard.php(523): Aimeos\MW\Tree\Manager\DBNestedSet->saveNode(Object(Aimeos\MW\Tree\Node\Standard))
#2 /app/vendor/aimeos/aimeos-core/src/MShop/Common/Manager/Base.php(212): Aimeos\MShop\Catalog\Manager\Ustandard->saveItem(Object(Aimeos\MShop\Catalog\Item\Ustandard), true)
#3 /app/vendor/aimeos/aimeos-core/src/MShop/Catalog/Manager/Standard.php(611): Aimeos\MShop\Common\Manager\Base->save(Object(Aimeos\MShop\Catalog\Item\Ustandard), true)
#4 /app/packages/ultra/src/MShop/Catalog/Manager/Ustandard.php(498): Aimeos\MShop\Catalog\Manager\Standard->save(Object(Aimeos\MShop\Catalog\Item\Ustandard), true)
#5 /app/vendor/aimeos/aimeos-core/src/MShop/Common/Manager/Decorator/Base.php(240): Aimeos\MShop\Catalog\Manager\Ustandard->save(Object(Aimeos\MShop\Catalog\Item\Ustandard), true)
#6 /app/vendor/aimeos/aimeos-core/src/MShop/Common/Manager/Decorator/Base.php(240): Aimeos\MShop\Common\Manager\Decorator\Base->save(Object(Aimeos\MShop\Catalog\Item\Ustandard), true)
#7 /app/vendor/aimeos/ai-admin-jqadm/src/Admin/JQAdm/Catalog/Standard.php(242): Aimeos\MShop\Common\Manager\Decorator\Base->save(Object(Aimeos\MShop\Catalog\Item\Ustandard))
#8 /app/vendor/aimeos/ai-admin-jqadm/src/Admin/JQAdm/Common/Decorator/Base.php(151): Aimeos\Admin\JQAdm\Catalog\Standard->save()
#9 /app/vendor/aimeos/aimeos-laravel/src/Controller/JqadmController.php(219): Aimeos\Admin\JQAdm\Common\Decorator\Base->save()
, which occurs in the file /app/vendor/aimeos/aimeos-core/src/MW/Tree/Manager/DBNestedSet.php(459).

Here is the relevant code snippet:

Code: Select all

	/**
	 * Stores the values of the given node to the storage.
	 *
	 * This method does only store values like the node label but doesn't change
	 * the tree layout by adding, moving or deleting nodes.
	 *
	 * @param \Aimeos\MW\Tree\Node\Iface $node Tree node object
	 * @return \Aimeos\MW\Tree\Node\Iface Updated node item
	 */
	public function saveNode( \Aimeos\MW\Tree\Node\Iface $node ) : \Aimeos\MW\Tree\Node\Iface
	{
		if( $node->getId() === null ) {
			throw new \Aimeos\MW\Tree\Exception( sprintf( 'Unable to save newly created nodes, use insert method instead' ) );
		}

		if( $node->isModified() === false ) {
			return $node;
		}

		$stmt = $this->conn->create( $this->config['update'] );
		$stmt->bind( 1, $node->getLabel(), \Aimeos\Base\DB\Statement\Base::PARAM_STR );
		$stmt->bind( 2, $node->getCode(), \Aimeos\Base\DB\Statement\Base::PARAM_STR );
		$stmt->bind( 3, $node->getStatus(), \Aimeos\Base\DB\Statement\Base::PARAM_INT );
		$stmt->bind( 4, $node->getId(), \Aimeos\Base\DB\Statement\Base::PARAM_INT );
		$stmt->execute()->finish();

		return $node;
	}
Could you please advise on how I can extend this method with three new fields without modifying the existing class?

Aimeos 2024 and Laravel11 from composer, Linux Ubuntu, PHP 8.2

User avatar
aimeos
Administrator
Posts: 8244
Joined: 01 Jan 1970, 00:00

Re: Unable to add/save custom columns for catalog

Post by aimeos » 22 Aug 2024, 10:21

The the easy way by creating a decorator for the catalog manager:
https://aimeos.org/docs/2024.x/models/e ... /#easy-way

The custom way works too but you have to extend the catalog standard manager class and overwrite the methods you need (updateUsage() for example).
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

arthur.webware
Posts: 2
Joined: 21 Aug 2024, 11:24

Re: Unable to add/save custom columns for catalog

Post by arthur.webware » 23 Aug 2024, 06:21

Thank you for your response. I spent a lot of time re-reading the documentation over and over again. My goal was to use insert instead of insert-usage for updates, and similarly for updates. However, it turned out that to achieve this, I had to inherit from a large number of dependencies, including extending interfaces. This path seemed too complex (especially in the case of the catalog).

After your message, I decided to start from scratch. However, instead of fully reverting to the original position, I rolled back the changes in reverse order and, out of curiosity, added a decorator as well. The final code ended up as follows:

Create a migration for the new fields.
Then create/update the configuration file of your extension mshop.php, where you need to specify the name of the future decorator and the name of the future manager.
Create a decorator following the example from the documentation.
Create a manager also following the example from the documentation, but extend it with the createItemBase method, where in the return function, you replace \Aimeos\MShop\Catalog\Item\Standard with your own ItemStandard class, which you will create later.
Create a subclass of \Aimeos\MShop\Catalog\Item\Standard based on the documentation example, but instead of $this->myvalues, you should refer to $this->node->customvalue.
You can copy the admin panel template for the catalog into your extension and modify the code to manage the fields there. Here's an example:

Code: Select all

<div class="form-group row optional advanced">
    <label class="col-sm-4 form-control-label help"><?= $enc->html($this->translate('admin', 'Main Order')) ?></label>
    <div class="col-sm-8">
        <input class="form-control item-label" type="number" tabindex="1"
               name="<?= $this->formparam(array('item', 'catalog.mainorder')) ?>"
               placeholder="<?= $enc->attr($this->translate('admin', 'Order in main page')) ?>"
               value="<?= $enc->attr($this->get('itemData/catalog.mainorder')) ?>">
    </div>
    <div class="col-sm-12 form-text text-muted help-text">
        <?= $enc->html($this->translate('admin', 'Order to show on main page')) ?>
    </div>
</div>
Migration

Code: Select all

return array(
	'table' => array(

		'mshop_catalog' => function( \Aimeos\Upscheme\Schema\Table $table ) {
            $table->smallint( 'mainactive' )->default( 0 );
            $table->smallint( 'mainorder' )->default( 0 );
            $table->smallint( 'sortorder' )->default( 0 );
		},
	),
);
Decorator

Code: Select all

<?php

namespace Aimeos\MShop\Catalog\Manager\Decorator;

use Aimeos\MShop\Catalog\Manager\Decorator\Base;

/**
 * Manager decorator for Catalog
 *
 * @see https://aimeos.org/docs/2024.x/models/extend-managers/#easy-way
 */
class Udecorator extends Base
{
    private $attr = [
        'mainactive' => [
            'code' => 'catalog.mainactive',
            'internalcode' => 'mcat."mainactive"',
            'label' => 'Show on Main Page',
            'type' => 'int',
        ],
        'mainorder' => [
            'code' => 'catalog.mainorder',
            'internalcode' => 'mcat."mainorder"',
            'label' => 'Main Page Order',
            'type' => 'int',
        ],
        'sortorder' => [
            'code' => 'catalog.sortorder',
            'internalcode' => 'mcat."sortorder"',
            'label' => 'Sort Order',
            'type' => 'int',
        ],
    ];

    /**
     * @return array
     */
    public function getSaveAttributes(): array
    {
        return parent::getSaveAttributes() + $this->createAttributes($this->attr);
    }

    /**
     * @param bool $withsub
     * @return array|\Aimeos\Base\Criteria\Attribute\Iface[]
     */
    public function getSearchAttributes(bool $withsub = true): array
    {
        return parent::getSearchAttributes($withsub) + $this->createAttributes($this->attr);
    }
}
Manager

Code: Select all

<?php

namespace Aimeos\MShop\Catalog\Manager;

use Aimeos\MShop\Catalog\Manager\Standard;
use Aimeos\MShop\Catalog\Exception;

class Ustandard extends Standard
{
    private array $customSearchConfig = array(
        'id' => array(
            'code' => 'catalog.id',
            'internalcode' => 'mcat."id"',
            'label' => 'ID',
            'type' => 'int',
            'public' => false,
        ),
        'catalog.siteid' => array(
            'code' => 'catalog.siteid',
            'internalcode' => 'mcat."siteid"',
            'label' => 'Site ID',
            'type' => 'string',
            'public' => false,
        ),
        'parentid' => array(
            'code' => 'catalog.parentid',
            'internalcode' => 'mcat."parentid"',
            'label' => 'Parent ID',
            'type' => 'int',
            'public' => false,
        ),
        'level' => array(
            'code' => 'catalog.level',
            'internalcode' => 'mcat."level"',
            'label' => 'Tree level',
            'type' => 'int',
            'public' => false,
        ),
        'left' => array(
            'code' => 'catalog.left',
            'internalcode' => 'mcat."nleft"',
            'label' => 'Left value',
            'type' => 'int',
            'public' => false,
        ),
        'right' => array(
            'code' => 'catalog.right',
            'internalcode' => 'mcat."nright"',
            'label' => 'Right value',
            'type' => 'int',
            'public' => false,
        ),
        'label' => array(
            'code' => 'catalog.label',
            'internalcode' => 'mcat."label"',
            'label' => 'Label',
            'type' => 'string',
        ),
        'code' => array(
            'code' => 'catalog.code',
            'internalcode' => 'mcat."code"',
            'label' => 'Code',
            'type' => 'string',
        ),
        'status' => array(
            'code' => 'catalog.status',
            'internalcode' => 'mcat."status"',
            'label' => 'Status',
            'type' => 'int',
        ),
        'catalog.url' => array(
            'code' => 'catalog.url',
            'internalcode' => 'mcat."url"',
            'label' => 'URL segment',
            'type' => 'string',
        ),
        'catalog.target' => array(
            'code' => 'catalog.target',
            'internalcode' => 'mcat."target"',
            'label' => 'URL target',
            'type' => 'string',
        ),
        'catalog.config' => array(
            'code' => 'catalog.config',
            'internalcode' => 'mcat."config"',
            'label' => 'Config',
            'type' => 'json',
            'public' => false,
        ),
        'catalog.ctime' => array(
            'label' => 'Create date/time',
            'code' => 'catalog.ctime',
            'internalcode' => 'mcat."ctime"',
            'type' => 'datetime',
            'public' => false,
        ),
        'catalog.mtime' => array(
            'label' => 'Modify date/time',
            'code' => 'catalog.mtime',
            'internalcode' => 'mcat."mtime"',
            'type' => 'datetime',
            'public' => false,
        ),
        'catalog.editor' => array(
            'code' => 'catalog.editor',
            'internalcode' => 'mcat."editor"',
            'label' => 'Editor',
            'type' => 'string',
            'public' => false,
        ),
        'catalog:has' => array(
            'code' => 'catalog:has()',
            'internalcode' => ':site AND :key AND mcatli."id"',
            'internaldeps' => ['LEFT JOIN "mshop_catalog_list" AS mcatli ON ( mcatli."parentid" = mcat."id" )'],
            'label' => 'Catalog has list item, parameter(<domain>[,<list type>[,<reference ID>)]]',
            'type' => 'null',
            'public' => false,
        ),
        'sort:catalog:position' => array(
            'code' => 'sort:catalog:position',
            'internalcode' => 'mcat."nleft"',
            'label' => 'Category position',
            'type' => 'int',
            'public' => false,
        ),
        'catalog.mainactive' => array(
            'code' => 'catalog.mainactive',
            'internalcode' => 'mcat."mainactive"',
            'label' => 'Main Active',
            'type' => 'int',
        ),
        'catalog.mainorder' => array(
            'code' => 'catalog.mainorder',
            'internalcode' => 'mcat."mainorder"',
            'label' => 'Main Order',
            'type' => 'int',
        ),
        'catalog.sortorder' => array(
            'code' => 'catalog.sortorder',
            'internalcode' => 'mcat."sortorder"',
            'label' => 'Sort Order',
            'type' => 'int',
        ),
    );

    private \Aimeos\Base\DB\Connection\Iface $conn;

    public function __construct(\Aimeos\MShop\ContextIface $context)
    {
        parent::__construct($context);
    }

    /**
     * Returns the attributes that can be used for searching.
     *
     * @param bool $withsub Return also attributes of sub-managers if true
     * @return \Aimeos\Base\Criteria\Attribute\Iface[] List of search attribute items
     */
    public function getSearchAttributes(bool $withsub = true): array
    {
        return parent::getSearchAttributes($withsub) + $this->createAttributes($this->customSearchConfig);
    }

    /**
     * Creates a new catalog item.
     *
     * @param array $values
     * @param array $listItems
     * @param array $refItems
     * @param array $children
     * @param \Aimeos\MW\Tree\Node\Iface|null $node
     * @return \Aimeos\MShop\Common\Item\Iface
     * @throws Exception
     */
    protected function createItemBase(
        array                      $values = [],
        array                      $listItems = [],
        array                      $refItems = [],
        array                      $children = [],
        \Aimeos\MW\Tree\Node\Iface $node = null
    ): \Aimeos\MShop\Common\Item\Iface
    {
        if ($node === null) {
            if (!isset($values['siteid'])) {
                throw new Exception('No site ID available for creating a catalog item');
            }

            $node = $this->createTreeManager($values['siteid'])->createNode();
            $node->siteid = $values['siteid'];
        }

        if (isset($node->config) && ($values['config'] = json_decode($config = $node->config, true)) === null) {
            $values['config'] = [];
        }

        return new \Aimeos\MShop\Catalog\Item\Ustandard($node, $values, $children, $listItems, $refItems);
    }
}
Item

Code: Select all

<?php

namespace Aimeos\MShop\Catalog\Item;

/**
 * Generic interface for catalog items.
 *
 * @package MShop
 * @subpackage Catalog
 */
class Ustandard extends Standard
{
    /**
     * @param \Aimeos\MW\Tree\Node\Iface $node
     * @param array $values
     * @param array $children
     * @param array $listItems
     * @param array $refItems
     */
    public function __construct(
        \Aimeos\MW\Tree\Node\Iface $node,
        array                      $values = [],
        array                      $children = [],
        array                      $listItems = [],
        array                      $refItems = []
    )
    {
        parent::__construct($node, $values, $children, $listItems, $refItems);

        $this->node = $node;
    }

    /**
     * @return int
     */
    public function getSortOrder(): int
    {
        return (int)$this->node->sortorder;
    }

    /**
     * @param int|null $value
     * @return Iface
     */
    public function setSortOrder(?int $value): \Aimeos\MShop\Catalog\Item\Iface
    {
        $this->node->sortorder = (int)$value;
        return $this;
    }

    /**
     * @return int
     */
    public function getMainOrder(): int
    {
        return (int)$this->node->mainorder;
    }

    /**
     * @param int|null $value
     * @return Iface
     */
    public function setMainOrder(?int $value): \Aimeos\MShop\Catalog\Item\Iface
    {
        $this->node->mainorder = (int)$value;
        return $this;
    }

    /**
     * @return int
     */
    public function getMainActive(): int
    {
        return (int)$this->node->mainactive;
    }

    /**
     * @param int|null $value
     * @return Iface
     */
    public function setMainActive(?int $value): \Aimeos\MShop\Catalog\Item\Iface
    {
        $this->node->mainactive = (int)$value;
        return $this;
    }


    /**
     *  Sets the item values from the given array and removes that entries from the list.
     *
     * @param array &$list Associative list of item keys and their values.
     * @param bool $private True to set private properties too, false for public only.
     * @return \Aimeos\MShop\Common\Item\Iface Catalog item for chaining method calls.
     */
    public function fromArray(array &$list, bool $private = false): \Aimeos\MShop\Common\Item\Iface
    {
        $item = parent::fromArray($list, $private);

        foreach ($list as $key => $value) {
            switch ($key) {
                case 'catalog.sortorder':
                    $item = $item->setSortOrder($value);
                    break;
                case 'catalog.mainorder':
                    $item = $item->setMainOrder($value);
                    break;
                case 'catalog.mainactive':
                    $item = $item->setMainActive($value);
                    break;
                default:
                    continue 2;
            }
            unset($list[$key]);
        }

        return $item;
    }

    /**
     * Returns the public values of the node as array.
     *
     * @param bool $private True to return private properties, false for public only.
     * @return array Associative list of key/value pairs.
     */
    public function toArray(bool $private = false): array
    {
        $list = parent::toArray($private);

        $list['catalog.sortorder'] = $this->getSortOrder();
        $list['catalog.mainorder'] = $this->getMainOrder();
        $list['catalog.mainactive'] = $this->getMainActive();

        return $list;
    }
}
mshop.php

Code: Select all

<?php

/**
 * @license LGPLv3, https://opensource.org/licenses/LGPL-3.0
 * @copyright Aimeos (aimeos.org), 2015-2024
 */


return array(
    'catalog' => array(
        'manager' => array(
            'decorators' => array(
                'local' => array('Udecorator'),
            ),
            'name' => 'Ustandard',
            'lists' => array(
                'type' => array(
                    'delete' => array(
                        'ansi' => '
						DELETE FROM "mshop_catalog_list_type"
						WHERE :cond AND "siteid" LIKE ?
					'
                    ),
                    'insert' => array(
                        'ansi' => '
						INSERT INTO "mshop_catalog_list_type" ( :names
							"code", "domain", "label", "i18n", "pos", "status",
							"mtime","editor", "siteid", "ctime"
						) VALUES ( :values
							?, ?, ?, ?, ?, ?, ?, ?, ?, ?
						)
					'
                    ),
                    'update' => array(
                        'ansi' => '
						UPDATE "mshop_catalog_list_type"
						SET :names
							"code" = ?, "domain" = ?, "label" = ?, "i18n" = ?,
							"pos" = ?, "status" = ?, "mtime" = ?, "editor" = ?
						WHERE "siteid" LIKE ? AND "id" = ?
					'
                    ),
                    'search' => array(
                        'ansi' => '
						SELECT :columns
						FROM "mshop_catalog_list_type" mcatlity
						:joins
						WHERE :cond
						ORDER BY :order
						OFFSET :start ROWS FETCH NEXT :size ROWS ONLY
					',
                        'mysql' => '
						SELECT :columns
						FROM "mshop_catalog_list_type" mcatlity
						:joins
						WHERE :cond
						ORDER BY :order
						LIMIT :size OFFSET :start
					'
                    ),
                    'count' => array(
                        'ansi' => '
						SELECT COUNT(*) AS "count"
						FROM (
							SELECT mcatlity."id"
							FROM "mshop_catalog_list_type" mcatlity
							:joins
							WHERE :cond
							ORDER BY mcatlity."id"
							OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY
						) AS list
					',
                        'mysql' => '
						SELECT COUNT(*) AS "count"
						FROM (
							SELECT mcatlity."id"
							FROM "mshop_catalog_list_type" mcatlity
							:joins
							WHERE :cond
							ORDER BY mcatlity."id"
							LIMIT 10000 OFFSET 0
						) AS list
					'
                    ),
                    'newid' => array(
                        'db2' => 'SELECT IDENTITY_VAL_LOCAL()',
                        'mysql' => 'SELECT LAST_INSERT_ID()',
                        'oracle' => 'SELECT mshop_catalog_list_type_seq.CURRVAL FROM DUAL',
                        'pgsql' => 'SELECT lastval()',
                        'sqlite' => 'SELECT last_insert_rowid()',
                        'sqlsrv' => 'SELECT @@IDENTITY',
                        'sqlanywhere' => 'SELECT @@IDENTITY',
                    ),
                ),
            ),
            'cleanup' => array(
                'ansi' => '
				DELETE FROM "mshop_catalog"
				WHERE :siteid AND "nleft" >= ? AND "nright" <= ?
			'
            ),
            'delete' => array(
                'ansi' => '
				DELETE FROM "mshop_catalog"
				WHERE "siteid" = :siteid AND "nleft" >= ? AND "nright" <= ?
			'
            ),
            'get' => array(
                'ansi' => '
				SELECT :columns
					mcat."id", mcat."code", mcat."url", mcat."label", mcat."config",
					mcat."status", mcat."level", mcat."parentid", mcat."siteid",
					mcat."nleft" AS "left", mcat."nright" AS "right",
					mcat."mtime", mcat."editor", mcat."ctime", mcat."target",
					mcat."sortorder"
				FROM "mshop_catalog" mcat, "mshop_catalog" AS parent
				WHERE parent."id" = ?
					AND mcat."siteid" = :siteid
					AND parent."siteid" = :siteid
					AND mcat."nleft" >= parent."nleft"
					AND mcat."nleft" <= parent."nright"
					AND mcat."level" <= parent."level" + ?
					AND :cond
				GROUP BY :columns
					mcat."id", mcat."code", mcat."url", mcat."label", mcat."config",
					mcat."status", mcat."level", mcat."parentid", mcat."siteid",
					mcat."nleft", mcat."nright", mcat."target",
					mcat."mtime", mcat."editor", mcat."ctime"
				ORDER BY mcat."nleft"
			'
            ),
            'insert' => array(
                'ansi' => '
				INSERT INTO "mshop_catalog" (
					"siteid", "label", "code", "status", "parentid", "level",
					"nleft", "nright", "config", "mtime", "ctime", "editor", "target"
				) VALUES (
					:siteid, ?, ?, ?, ?, ?, ?, ?, \'\', \'1970-01-01 00:00:00\', \'1970-01-01 00:00:00\', \'\', \'\'
				)
			'
            ),
            'insert-usage' => array(
                'ansi' => '
				UPDATE "mshop_catalog"
				SET :names "url" = ?, "config" = ?, "mtime" = ?, "editor" = ?, "target" = ?, "ctime" = ?
				WHERE "siteid" LIKE ? AND "id" = ?
			'
            ),
            'update' => array(
                'ansi' => '
				UPDATE "mshop_catalog"
				SET "label" = ?, "code" = ?, "status" = ?
				WHERE "siteid" = :siteid AND "id" = ?
			'
            ),
            'update-parentid' => array(
                'ansi' => '
				UPDATE "mshop_catalog"
				SET "parentid" = ?
				WHERE "siteid" = :siteid AND "id" = ?
			'
            ),
            'update-usage' => array(
                'ansi' => '
				UPDATE "mshop_catalog"
				SET :names "url" = ?, "config" = ?, "mtime" = ?, "editor" = ?, "target" = ?
				WHERE "siteid" LIKE ? AND "id" = ?
			'
            ),
            'move-left' => array(
                'ansi' => '
				UPDATE "mshop_catalog"
				SET "nleft" = "nleft" + ?, "level" = "level" + ?
				WHERE "siteid" = :siteid AND "nleft" >= ? AND "nleft" <= ?
			'
            ),
            'move-right' => array(
                'ansi' => '
				UPDATE "mshop_catalog"
				SET "nright" = "nright" + ?
				WHERE "siteid" = :siteid AND "nright" >= ? AND "nright" <= ?
			'
            ),
            'search' => array(
                'ansi' => '
				SELECT :columns
					mcat."id", mcat."code", mcat."url", mcat."label", mcat."config",
					mcat."status", mcat."level", mcat."parentid", mcat."siteid",
					mcat."nleft" AS "left", mcat."nright" AS "right",
					mcat."mtime", mcat."editor", mcat."ctime", mcat."target",
					mcat."mainactive", mcat."sortorder", mcat."mainorder"
				FROM "mshop_catalog" mcat
				WHERE mcat."siteid" = :siteid AND mcat."nleft" >= ?
					AND mcat."nright" <= ? AND :cond
				ORDER BY :order
			'
            ),
            'search-item' => array(
                'ansi' => '
				SELECT :columns,
					mcat."id", mcat."code", mcat."url", mcat."label", mcat."config",
					mcat."status", mcat."level", mcat."parentid", mcat."siteid",
					mcat."nleft" AS "left", mcat."nright" AS "right",
					mcat."mtime", mcat."editor", mcat."ctime", mcat."target",
					mcat."mainactive", mcat."sortorder", mcat."mainorder"
				FROM "mshop_catalog" mcat
				:joins
				WHERE :cond
				GROUP BY :group
				ORDER BY :order
				OFFSET :start ROWS FETCH NEXT :size ROWS ONLY
			',
                'mysql' => '
				SELECT :columns,
					mcat."id", mcat."code", mcat."url", mcat."label", mcat."config",
					mcat."status", mcat."level", mcat."parentid", mcat."siteid",
					mcat."nleft" AS "left", mcat."nright" AS "right",
					mcat."mtime", mcat."editor", mcat."ctime", mcat."target"
				FROM "mshop_catalog" mcat
				:joins
				WHERE :cond
				GROUP BY :group
				ORDER BY :order
				LIMIT :size OFFSET :start
			'
            ),
            'count' => array(
                'ansi' => '
				SELECT COUNT(*) AS "count"
				FROM (
					SELECT mcat."id"
					FROM "mshop_catalog" mcat
					:joins
					WHERE :cond
					GROUP BY mcat."id"
					ORDER BY mcat."id"
					OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY
				) AS list
			',
                'mysql' => '
				SELECT COUNT(*) AS "count"
				FROM (
					SELECT mcat."id"
					FROM "mshop_catalog" mcat
					:joins
					WHERE :cond
					GROUP BY mcat."id"
					ORDER BY mcat."id"
					LIMIT 10000 OFFSET 0
				) AS list
			'
            ),
            'newid' => array(
                'db2' => 'SELECT IDENTITY_VAL_LOCAL()',
                'mysql' => 'SELECT LAST_INSERT_ID()',
                'oracle' => 'SELECT mshop_catalog_seq.CURRVAL FROM DUAL',
                'pgsql' => 'SELECT lastval()',
                'sqlite' => 'SELECT last_insert_rowid()',
                'sqlsrv' => 'SELECT @@IDENTITY',
                'sqlanywhere' => 'SELECT @@IDENTITY',
            ),
            'lock' => array(
                'db2' => 'LOCK TABLE "mshop_catalog" IN EXCLUSIVE MODE',
                'mysql' => "DO GET_LOCK('aimeos.catalog', -1)", // LOCK TABLE implicit commits transactions
                'oracle' => 'LOCK TABLE "mshop_catalog" IN EXCLUSIVE MODE',
                'pgsql' => 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE',
                'sqlanywhere' => 'LOCK TABLE "mshop_catalog" IN EXCLUSIVE MODE',
                'sqlsrv' => "EXEC sp_getapplock @Resource = 'aimeos.catalog', @LockMode = 'Exclusive'",
            ),
            'unlock' => array(
                'mysql' => "DO RELEASE_LOCK('aimeos.catalog')",
                'sqlsrv' => "EXEC sp_releaseapplock @Resource = 'aimeos.catalog'",
            ),
        ),
    ),
);
Yes, mshop.catalog.manager is copy of catalog.php config, but without this copying code does not work.
If you see mistake please reply here =)

User avatar
aimeos
Administrator
Posts: 8244
Joined: 01 Jan 1970, 00:00

Re: Unable to add/save custom columns for catalog

Post by aimeos » 23 Aug 2024, 07:47

You don't need to copy the SQL statements, especially not for the sub-managers. What happens if you remove them from your config?
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

Post Reply