2

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...
Alexander Golovinov
  • 456
  • 7
  • 14

2 Answers2

3

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
Johannes von Zmuda
  • 1,814
  • 8
  • 14
1

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 {...
HybrisHelp
  • 5,518
  • 2
  • 27
  • 65
  • How can I retrieve the values from here and to get only the list of users who have such carts? Currently FlexibleSearch does not allow me to get it as Java objects, because of two PK's values. The desired result would be List? – Alexander Golovinov May 17 '19 at 09:21
  • 1
    Just remove this part from the select clause: ", {b.pk}, count(distinct({c.pk})) " so that it looks like this: "select {u.pk} from..." – Johannes von Zmuda May 17 '19 at 09:28