Retrieve items / products of two catalogs / categories?

How to configure and adapt Aimeos based shops as developer
Forum rules
Always add your Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
jossnaz
Posts: 46
Joined: 22 Jul 2016, 01:19

Retrieve items / products of two catalogs / categories?

Post by jossnaz » 22 Jul 2016, 21:52

i created 2 catalogs. I want all items, that are in both catalogs, the ids of the catalogs are 7 and 13.

product with id 36 is in both



What I tried is this:

Code: Select all

  $context = CmcAimeosHelper::getContext();
  $manager = \Aimeos\MShop\Index\Manager\Factory::createManager($context );
  $search = $manager->createSearch(true);
  $expr[] = $search->compare('==', 'index.catalog.id', 7);
  $expr[] = $search->compare('==', 'index.catalog.id', 13);
  $search->setConditions($search->combine( '&&', $expr ));
  $myitems = $manager->searchItems($search);
   var_dump($myitems );;
myitems is empty array

now when commenting out one category requirement I get both times a resultset that contains the product id 36

Code: Select all

  $context = CmcAimeosHelper::getContext();
  $manager = \Aimeos\MShop\Index\Manager\Factory::createManager($context );
  $search = $manager->createSearch(true);
  //$expr[] = $search->compare('==', 'index.catalog.id', 7);
  $expr[] = $search->compare('==', 'index.catalog.id', 13);
  $search->setConditions($search->combine( '&&', $expr ));
  $myitems = $manager->searchItems($search);
   var_dump($myitems );;

Code: Select all

  $context = CmcAimeosHelper::getContext();
  $manager = \Aimeos\MShop\Index\Manager\Factory::createManager($context );
  $search = $manager->createSearch(true);
  $expr[] = $search->compare('==', 'index.catalog.id', 7);
  //$expr[] = $search->compare('==', 'index.catalog.id', 13);
  $search->setConditions($search->combine( '&&', $expr ));
  $myitems = $manager->searchItems($search);
   var_dump($myitems );;

am i missing something?

jossnaz
Posts: 46
Joined: 22 Jul 2016, 01:19

Re: Retrieve items / products of two catalogs / categories?

Post by jossnaz » 22 Jul 2016, 22:55

**EDIT** so turns out i updated the shop like half a week ago but did not seem to have deployed the files to my local dev server folder. Yes, i hate myself.

So turns out the resulting SQL is this:

Code: Select all

SELECT mpro."id"
			FROM "mshop_product" AS mpro
			LEFT JOIN "mshop_index_catalog" AS mindca USE INDEX ("idx_msindca_s_ca_lt_po", "unq_msindca_p_s_cid_lt_po") ON mindca."prodid" = mpro."id"
			WHERE ( mpro."siteid" IN (1) AND ( ( mpro."status" = 1 AND ( mpro."start" IS NULL OR mpro."start" <= '2016-07-22 23:04:00' ) AND ( mpro."end" IS NULL OR mpro."end" >= '2016-07-22 23:04:00' ) ) AND mindca."catid" IN (7) AND mindca."catid" IN (13) ) )
			GROUP BY mpro."id" 
			
			LIMIT 100 OFFSET 0
i mean yes, this cannot work...


OLD POST

i tried debugging the code but it quickly turns into a big mess because my debugger is not jumping to the right lines in the code, e.g. a couple lines below or above where it should be, for whatever reason (i just switched to using nginx and thus am forced to use php-fpm)


is there an easy way to see what sql is actually written used? how can i debug what the things i am doing are actually producing as sql?
Last edited by jossnaz on 22 Jul 2016, 23:08, edited 1 time in total.

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

Re: Retrieve items / products of two catalogs / categories?

Post by aimeos » 22 Jul 2016, 22:57

You are working with lists, e.g. your index table contains:
- prodid: 36, catid: 7, listtype: default
- prodid: 36, catid: 13, listtype: default
Using 7 AND 13 will always return an empty result set.

The "index.catalogcount()" search function of Aimeos will be your friend in this case:

Code: Select all

$fcn = $search->createFunction( 'index.catalogcount', array( 'default', array( (int) 7, (int) 13 ) ) );
$search->setConditions( $search->compare( '==', $fcn, 2 ) );
This asks for all products that are in category 7 or 13 and the number of found records for each product is exactly 2.

More documentation about search functions is available here:
https://aimeos.org/docs/Developers/Libr ... _functions
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

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

Re: Retrieve items / products of two catalogs / categories?

Post by aimeos » 22 Jul 2016, 23:00

jossnaz wrote: is there an easy way to see what sql is actually written used? how can i debug what the things i am doing are actually producing as sql?
Set the log level to 7 (debug) and you will get all information you are looking for:
https://aimeos.org/docs/Configuration/C ... d/loglevel
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

jossnaz
Posts: 46
Joined: 22 Jul 2016, 01:19

Re: Retrieve items / products of two catalogs / categories?

Post by jossnaz » 22 Jul 2016, 23:15

thanks man, you are my hero

now I really read through the search functions part a day or two back, but once back at coding, I couldnt recall that note there.... so i ended up looking at the things written in the managers, the name of the search functions etc.

I remember reading this of catalog count today:

Code: Select all

'label'=>'Number of product categories, parameter(<list type code>,<category IDs>)',
It just didn't connect in my brain that this is what i wanted...

Post Reply