Impex Export Products where Price Rows are Null

[Former Member]

I'm trying to find all the products in our system that don't have a price row. My first instinct was to have something like the following:

 $catalog=ProductCatalog
 $version=Online
     
 INSERT_UPDATE Product; code;
 "#% impex.exportItems(""SELECT {P:pk} FROM {Product as P}, {CatalogVersion as CV}, {Catalog as C} WHERE {CV:catalog}={C:PK} AND {C:id}='$catalog' AND {CV:version}='$version' AND {P:catalogversion}={CV:PK} AND {P:europe1Prices} IS NULL"", Collections.EMPTY_MAP, Collections.singletonList( Item.class ), true, true, -1, -1 );"
 

This brought up the error that P:europe1Prices is not a table name. Is any one able to tell me what that table name would be? Or how to go about finding this in another way?

[andyfletcher · Accepted solution]

europe1prices is a jalo only property which means that it isn't stored in the database and you can't query it with flexible search.

From europe1-items.xml

 <attribute autocreate="true" qualifier="europe1Prices" type="PriceRowCollectionType">
     <persistence type="jalo"/>
     <modifiers read="true" write="true" search="false" initial="false" optional="true" partof="true"/>
 </attribute>

It is populated from the relationship to a product OR a product's pricegroup. You can read more here https://help.hybris.com/6.0.0/hcd/8bbb40fc86691014bd39c6f327b993a1.html

To find products that don't have pricerows directly mapped to them try this flexiblesearch

 select {p.code}
 from {product as p join catalogversion as cv on {p.catalogversion}={cv.pk} join catalog as c on {cv.catalog}={c.pk} left join pricerow as pr on {pr.product}={p.pk}}
 where {c.id}='ProductCatalog' and {cv.version}='Online' and {pr.pk} is null

[Former Member · Accepted solution]

Thank you Andrew. That helped me out.

As a follow up question. Is there a way to use flexible search in any other area other than the HAC (I'm not a java developer)?

[andyfletcher · Accepted solution]

If you don't want to write Java code then the hac is really your only option.

If you want to query the db with flexible search outside of Hybris you could try the vjdbc extension https://help.hybris.com/6.0.0/hcd/8c7ec0628669101481ec9d2d8dbb3a7c.html

You can then use some other tool to query the database.

I have seen someone use Excel before with some sort of ODBC->JDBC bridge but I've not tried it myself.

[Former Member]

Hi Karl,

You can select all products without a pricerow by using a sub select:

 SELECT {P:code} FROM {Product AS P} WHERE NOT EXISTS (
    {{ SELECT * FROM {PriceRow as pr} WHERE {pr:product}={P:pk} }}
 )

[Former Member]

for the above query , how do i query the prices along with the code?

[Former Member]

Hi Karl there is some relation between product and pricerow so that is name is PriceRow. So you will get Item type. Just check whether it is null or not.