CODEWITHSUNDEEP
javasap-commerce-cloudflexible-search
Alexander Golovinov
Alexander Golovinov

Reputation: 533

Hybris relational FlexibleSearch selection

Hybris creates several carts for the user for the same base store. How to select users who have more than one cart per one store?

Later on I will sort them and remove the oldest one.

SELECT {u.code} as userCode,
       {c.code} as cartCode,
       {c.site} as cartSite
FROM {User as u join Cart as c on {u.pk} = {c.user}}
WHERE...

Upvotes: 2

Views: 716

Answers (2)

HybrisHelp
HybrisHelp

Reputation: 5810

If you are using saved cart and quote functionality then you can think of not including it in result by adding WHERE {c.saveTime} IS NULL AND {c.quoteReference} IS NULL 

select {u.pk}, {b.pk}, count(distinct({c.pk})) 
from {
   user as u 
   join cart as c on {c.user} = {u.pk} 
   join basestore as b on {b.pk} = {c.store}
}

WHERE {c.saveTime} IS NULL AND {c.quoteReference} IS NULL
GROUP BY {u.pk}, {b.pk}
having count(distinct({c.pk})) > 1

Update:

To get the only list of the user:

select {u.pk} 
from {...

Upvotes: 1

Johannes von Zmuda
Johannes von Zmuda

Reputation: 1822

Use this one: 

select {u.pk}, {b.pk}, count(distinct({c.pk})) 
from {
   user as u 
   join cart as c on {c.user} = {u.pk} 
   join basestore as b on {b.pk} = {c.store}
} 
group by {u.pk}, {b.pk} 
having count(distinct({c.pk})) > 1

Upvotes: 3

Related Questions