Impex Export collection data on many lines

[Former Member]

Hi all,

I've created an impex to export data from a Flexible Search. The impex should export the category code and the code of the products linked to this category.

The result of my impex returns all the products code in the same line, with a comma separator.

 CATEGORY_CODE;PRDCODE1,PRDCODE2,PRDCODE3,...

Whereras I'd like to get the following result :

 CATEGORY_CODE;PRDCODE1
 CATEGORY_CODE;PRDCODE2
 CATEGORY_CODE;PRDCODE3
 ...

My impex :

insert_update Category;code;products(code) "#% impex.exportItems( ""SELECT DISTINCT {CT:pk} FROM {Product as P}, {CatalogVersion as CV}, {Catalog as C}, {CategoryProductRelation as CPR}, {Category as CT} WHERE {P:catalogversion}={CV:PK} AND {CV:catalog}={C:PK} AND {C:id}='$catalog' AND {CV:version}='$catalogVersion' AND {CPR:target}={P:PK} AND {CPR.source}={CT:PK} AND {CT:code} = '$categoryCode' AND {CT:code} = 'XXXXX' "", Collections.EMPTY_MAP, Collections.singletonList( Item.class ), true, true, -1, -1 );"

Can someone help me ?

Best regards

Sylvain

[Former Member]

Indeed, it is a better solution to do like this ! 😉

Thanks a lot

[andyfletcher]

Glad it helps. Why not just go ahead and accept the answer that answers your question!

[andyfletcher]

Why not try exporting the relationship directly since that's what you seem to be looking for

 $catalogId=???
 $catalogVersion=???
 $categoryCode=???

 INSERT_UPDATE CategoryProductRelation;source(code);target(code)
 "#%impex.exportItemsFlexibleSearch(""select {cpr.pk} from {categoryproductrelation as cpr join category as cat on {cpr.source}={cat.pk} join catalogversion as cv on {cat.catalogversion}={cv.pk} join catalog as c on {cv.catalog}={c.pk} join product as p on {cpr.target}={p.pk}} where {c.id}='$catalogId' and {cv.version}='$catalogVersion' and {cat.code}='$categoryCode'"")"

[Former Member]

Thanks a lot for your answer

I understand what you mean. The problem is that my products are linked to many category types. And I only want to get one type of category.

In your example, I only want to get the "A-typed categories" corresponding to "1360", but not the "B-types categories" corresponding to "brand_980"

 # code;supercategories
  1101690;1360,brand_980
  1221417;1360,brand_980
  ... ...
  2116282;1360,brand_980,drill_engraving
  ... ...
  ... ...

And I don't know if it's possible to fix a category type in the header, to get only returned the categories of that type.

I fixed the category code in my example just to test it, but I will not have any category code parameter in my final impex.

Best regards

[Former Member]

Hello Sylvain,

I think because the header of the impEx is like:

 insert_update Category;code;products(code)  ...

Which means one Category maps a collection of products So the result will be like:

  CATEGORY_CODE;PRDCODE1,PRDCODE2,PRDCODE3,...

Actually I use the ImpEx scripts like (OOTB data)

 "#% impex.setLocale( new Locale( ""en"" , """" ) );"
 
 $catalog=powertoolsProductCatalog
 $categoryCode=1360
 $catalogVersion=Staged
  
 insert_update Product;code;supercategories(code) 
 "#% impex.exportItems( ""SELECT DISTINCT {P:pk} FROM {Product as P}, {CatalogVersion as CV}, {Catalog as C}, {CategoryProductRelation as CPR}, {Category as CT} WHERE {P:catalogversion}={CV:PK} AND {CV:catalog}={C:PK} AND {C:id}='$catalog' AND {CV:version}='$catalogVersion' AND {CPR:target}={P:PK} AND {CPR.source}={CT:PK} AND {CT:code} = '$categoryCode' "", Collections.EMPTY_MAP, Collections.singletonList( Item.class ), true, true, -1, -1 );"
 

It exports the result like:

 # code;supercategories
 1101690;1360,brand_980
 1221417;1360,brand_980
 ... ...
 2116282;1360,brand_980,drill_engraving
 ... ...
 ... ...

Or you can just only export the products, because the Category code has been fixed to the specific one in the parameter area:

 "#% impex.setLocale( new Locale( ""en"" , """" ) );"
 
 $catalog=powertoolsProductCatalog
 $categoryCode=1360
 $catalogVersion=Staged
 
 insert_update Product;code
 "#% impex.exportItems( ""SELECT DISTINCT {P:pk} FROM {Product as P}, {CatalogVersion as CV}, {Catalog as C}, {CategoryProductRelation as CPR}, {Category as CT} WHERE {P:catalogversion}={CV:PK} AND {CV:catalog}={C:PK} AND {C:id}='$catalog' AND {CV:version}='$catalogVersion' AND {CPR:target}={P:PK} AND {CPR.source}={CT:PK} AND {CT:code} = '$categoryCode' "", Collections.EMPTY_MAP, Collections.singletonList( Item.class ), true, true, -1, -1 );"
 

However, this may not hit your requirement, but the idea is:
To export Product items, along with each product's supercategories (a collection), in which it should contain the specific Category code in the parameter (1360 in this case).
As Product - Category is a many-to-many relation itemType, it may give the result like that.

Hope this could help you a little bit.

Thanks & Regards,
Hongxian