I decided to get back to blogging after a very long break. Since I am quite busy with our Kontolino! project, I also decided that I need to get back to blogging with small articles that require little time – but might still be useful. So here is the idea: I am struggling with Glorp and O/R mapping much more often than I’d like to and since Glorp is not documented well enough when it comes to “more advanced” techniques, I thought I should probably just write a blog post whenever I solve a more or less difficult problem that required some searching and trying. This is in the hopes that my blog will be a source of information for myself and maybe other readers.

So here is my first (well, actually I have written a little bit about my Glorp “discoveries” in the past, like here, here or here) installment of this new series:

If you need to query objects using a NOT EXISTS in the resulting SQL, Glorp can of course help you. Let’s say you need to find all Customer s in your DB that haven’t ordered anything in the last 100 days and you’d like to send a nice Coupon code to them. So you need to find all Customers who do not have an order that is younger than 100 days. This is of course easy in SQL, but the Glorp syntax for such queries is a bit “different”:

myDbSession read: Customer 
     where: [:cust| 
       cust notExists: 
         (Query read: Order 
           where: [:ord|
             ord customer = cust 
             AND: (ord date >= (Date today subtractDays: 100))])].

Let’s start a little competition / blog parade:

Do you know of nicer ways to do this? Any hints/tipps about this query you’d like to share. Just write a blog post and trackback to this post to see if we can improve on this.

What Glorp related tips like this one can you think of that are worth mentioning? Respond in a comment or write it to your blog and let’s help each others and new users learn as much as possible about Glorp.


One thought on “Glorp Subqueries – notExists:

Comments are closed.