Conntinuing on the Customer/Order example from my last post, we might want to only send some ads to customers who have ordered at least twice in the past. For this we’d first have to cunt all orders of our customers who haven’t ordered for at least 100 days.

So the first step is to create an SQL Statement that counts a customers’ orders and then select only those with more than one order. As usual in Glorp, it is extremely easy to do so – once you know how 😉

So this is how to create such a subquery in Glorp:

myDbSession read: Customer where: [:c| 
  (c count: [:cust| cust invoices]) > 1 ].

This will result in:

SELECT t1.id, t1.name, t1. ...
FROM MYSCHEMA.CUSTOMER t1
WHERE (
  (SELECT COUNT(*)
    FROM MYSCHEMA.ORDER s1t1
    WHERE (s1t1.cust_id = t1.id)) 
  > 1)

And return all customers that have ordered at least twice.

The next step is to combine this with our notExists: clause from the last post:

myDbSession read: Customer 
where: [:c|
  (c count: [:x| x orders]) >1 
  AND: (c notExists: (Query read: Order 
       where: [:p| p customer = c 
               AND: [p date < (Date today subtractDays: 100)]])) ].

It’s really that easy. You just need to remember who is the receiver for count: and notExists: etc. And that’s exactly why I find this hard sometimes.

 

Advertisements