Unable to add/save custom columns for catalog
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!
Always add your Laravel, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
-
- Posts: 2
- Joined: 21 Aug 2024, 11:24
Unable to add/save custom columns for catalog
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:
, which occurs in the file /app/vendor/aimeos/aimeos-core/src/MW/Tree/Manager/DBNestedSet.php(459).
Here is the relevant code snippet:
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
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()
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;
}
Aimeos 2024 and Laravel11 from composer, Linux Ubuntu, PHP 8.2
Re: Unable to add/save custom columns for catalog
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).
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, give us a star
If you like Aimeos, give us a star
-
- Posts: 2
- Joined: 21 Aug 2024, 11:24
Re: Unable to add/save custom columns for catalog
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:
Migration
Decorator
Manager
Item
mshop.php
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 =)
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>
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 );
},
),
);
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);
}
}
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);
}
}
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;
}
}
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'",
),
),
),
);
If you see mistake please reply here =)
Re: Unable to add/save custom columns for catalog
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, give us a star
If you like Aimeos, give us a star