Hybris Felixible Search Snippets

Hybris Felixible Search Snippets

#Select only products which have at least 1 variant with stock >0

SELECT {p.PK} FROM {Product AS p} WHERE {p.varianttype} IS NOT NULL and 
exists ({{SELECT {v.PK} FROM {VariantProduct AS v JOIN stocklevel AS s ON {v.code}={s.productCode}} WHERE {v.baseProduct} = {p.PK} AND {s.available} > 0}})

#Select available stock for a product or variant 
select {sl.available} from {stocklevel as sl} where {sl.productCode}="000001"

SELECT count({pk}) FROM {Customer} where {uid} not LIKE '%anonymous%' and {type}='8796099280987'

select {t.name}, count({c.pk}) from {SimpleCMSComponent as c},{ComposedType as t} where {c:itemtype}={t.pk} group by {t.name} order by count({c.pk})

select {name},{url},{visible} from {SimpleCMSLinkComponent} where {catalogVersion}='8796093481561' 
and {visible}=1 
and {url} is not null 

#Note use of e:order, object reference syntax
select {e.creationtime},{o.code},{e.totalPrice},{e.quantity}
from {OrderEntry as e},{Order as o},{VariantProduct as v},{Product as p} where {e:order}={o.pk} 
and {e.product}={v.pk} and {v.baseProduct}={p.pk} and {o.site}='8796093056040' 
and {o.creationtime} 

select {e.creationtime},{o.code},{v.code},{e.totalPrice},{e.quantity}
from {OrderEntry as e},{Order as o},{VariantProduct as v} where {e:order}={o.pk} and {e.product}={v.pk} and {o.site}='8796093056040'