My first (somewhat) complex GLORP Query that actually works


For some of you, this is old hats. For me, it is a little victory that needs a little pause from coding and leaning back and looking outside at the sun and feel like a super hero.

I just wrote my first more complex Glorp Query that actually works.

Here is what it looks like:

self dbSession read: Invoice where: [:inv |
    (inv notExists: [Query read: Payment where: [:z | z invoice = inv]])
    OR: [(inv sum: [:eachInvoice | eachInvoice payments amount]) < each amount]]

Reading the code, it is quite obvious what it is searching for: return all Invoices that are not paid in full or not at all. My first attempts always either found all Invoices without an associated payment or all invoices where the sum of payments is lower than the amount of the invoice. So I had started a few attempts at building a better solution, but always gave up after a few hours. In the end I used Smalltalk’s Collection methods do work on loaded objects. I guess it is obvious that this is not really performant…

Finding the solution is not as easy as understanding it. But it is worth the effort. The query is so much faster than doing selects and rejects on the Smalltalk side. I had to wait for results for a few seconds in the old (ugly and undisclosed) version and now the results just pop up in the blink of an eye.

If you use Glorp and want to speed things up by building intelligent queries (not that I think mine is an exceptionally good example of an “intelligent” query, it’s more an eye-opener to a new world) I recommend downloading the slides of Niall Ross’ 2013 ESUG presentation “How and Where in GLORP” and keeping it close to your keyboard.

I only hope he’ll continue his talk from 2013 this year and maybe even publish the slides in some tutorial-like form. I’d be the first to order a copy if it were published as a book.

Oh, and if you wonder what the generated SQL looks like, wonder no longer, here it is:

SELECT t1.id, t1.version, t1.date, t1.amount, t1.cust_id
  FROM INVOICE t1
  WHERE ( 
    NOT EXISTS (
      SELECT s2t1.id, s2t1.version, s2t1.date, s2t1.amount, s2t1.inv_id FROM PAYMENT s2t1
      WHERE (s2t1.inv_id = t1.id)) 
    OR ((SELECT SUM(s1t1.amount) FROM PAYMENT s1t1 WHERE (s1t1.inv_id = t1.id)) < t1.amount))

That is not the SQL version I had built by hand, but it yields the same results

 

2 thoughts on “My first (somewhat) complex GLORP Query that actually works

    1. Randal,

      I don’t really understand your question.
      The query says

      OR: [(inv sum: [:eachInvoice | eachInvoice payments amount]) < each amount]

      which will be a boolean once the sum of payments has been calculated and compared to the invoice's amount…

Comments are closed.