Manually selecting labels by sql

Questions around the TYPO3 integration and plugins as well as Flow and NeosCMS
Forum rules
Always add your TYPO3/Flow, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
boettner
Posts: 51
Joined: 09 Feb 2015, 17:49

Manually selecting labels by sql

Post by boettner » 05 Jun 2015, 14:45

Hi,

I´m using a TYPO3 provider extension [1] mainly for templating. In the template extensions controller I´m outputting a customized navigation which contains Aimeos catalog categories. I can´t find any docs about the Aimeos model and how catalog (mshop_catalog) and text (mshop_text) entitites relate to each other. I am able to select catalog items but need the text labels for this multi language shop.

Do you have any hints on that? Thanks! :-)

Have sun
Robert.

[1] https://fluidtypo3.org/documentation/te ... sions.html

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

Re: Manually selecting labels by sql

Post by aimeos » 05 Jun 2015, 17:13

Hi Robert
boettner wrote: I´m using a TYPO3 provider extension [1] mainly for templating. In the template extensions controller I´m outputting a customized navigation which contains Aimeos catalog categories. I can´t find any docs about the Aimeos model and how catalog (mshop_catalog) and text (mshop_text) entitites relate to each other. I am able to select catalog items but need the text labels for this multi language shop.
Normally, we do not recommend to access the tables manually but the TYPO3 extension is not modular enough yet to get a context object easily which would enable you to do this:

Code: Select all

$manager = MShop_Factory::createManager( $context, 'catalog' );
$tree = $manager->getTree( null, array('text') );
Then, the category texts would be already available in each catalog item. We already have the refactoring on our plan so this will be possible in the next release.

In the meantime, you can use SQL statements like this:

Code: Select all

SELECT * FROM mshop_catalog WHERE siteid=<locale.site.id>
SELECT * FROM mshop_catalog_list WHERE parentid IN (<catalog.ids>) AND siteid=<locale.site.id> AND domain='text'
SELECT * FROM mshop_text t JOIN mshop_text_type tt ON t.typeid=tt.id WHERE id IN (<catalog.list.refids>) AND siteid=<locale.site.id> AND tt.code='name'
You should not try to merge the statements into one because this will result in a slow query and you need to find out the ID of your site first.
boettner wrote: Have sun
Enough, and more than 30°C 8-)

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

Re: Manually selecting labels by sql

Post by aimeos » 07 Jun 2015, 20:36

If you use the latest code from the master branch (http://aimeos.org/docs/TYPO3/Download_A ... atest_code), you can now access the shop data using the MShop objects from your own TYPO3 extension:

Code: Select all

$path = \TYPO3\CMS\Core\Utility\ExtensionManagementUtility::extPath( 'aimeos' );
require_once $path . '/Classes/Base.php';

$config = \Aimeos\Aimeos\Base::getConfig();
$context = \Aimeos\Aimeos\Base::getContext( $config );
$localeManager = \MShop_Factory::createManager( $context );

// https://github.com/aimeos/arcavias-core/blob/master/lib/mshoplib/src/MShop/Locale/Manager/Default.php#L114
$localeItem = $localeManager->bootstrap( 'default' );

// set both to null to get all items regardless of language and currency
$localeItem->setLanguageId( null );
$localeItem->setCurrencyId( null );

$context->setLocale( $localeItem );

$manager = \MShop_Factory::createManager( $context, 'catalog' );
$node = $manager->getTree( null, array( 'text' ) );

// $node is a tree of nodes, use getChildren() for next level nodes
$textItems = $node->getRefItems( 'text', 'name' );
// for each text item use getContent() for the text and getLanguageId() for the language code

boettner
Posts: 51
Joined: 09 Feb 2015, 17:49

Re: Manually selecting labels by sql

Post by boettner » 10 Jun 2015, 09:01

Hi aimeos,

sun gone, problem solved. Thanks aimeos for your quick support. ;)
aimeos wrote: In the meantime, you can use SQL statements like this:

Code: Select all

SELECT * FROM mshop_catalog WHERE siteid=<locale.site.id>
SELECT * FROM mshop_catalog_list WHERE parentid IN (<catalog.ids>) AND siteid=<locale.site.id> AND domain='text'
SELECT * FROM mshop_text t JOIN mshop_text_type tt ON t.typeid=tt.id WHERE id IN (<catalog.list.refids>) AND siteid=<locale.site.id> AND tt.code='name'
It was even simpler in my scenario because there´s only one shop in this installation and I could hardcode the site id in my settings:

Code: Select all

SELECT * FROM mshop_catalog_list WHERE parentid IN (<catalog.ids>) AND siteid=<locale.site.id> AND domain='text'
SELECT * FROM mshop_text WHERE id IN (<catalog.list.refids>) AND siteid=<locale.site.id> AND langid=<$GLOBALS['TSFE']->lang>
Best
Robert.

Post Reply