Glorp and Oracle: limiting query result

Question

I'm using Glorp in VisualWorks and with an Oracle database. Because Oracle does not know the LIMIT command, following query returns all the records of myTable for which the WHERE clause evaluates to true.

q := Glorp.SimpleQuery
       returningManyOf: MyTable
       where: [:each | each name = 'test']
       limit: 10.
q orderBy: [:each | each id descending].
results:= aGlorpSession execute: q.

How would I go to incorporate ROWNUM in this Glorp query?

//edit Generated SQL:

SELECT t1.id, t1.name
 FROM MyTable t1
 WHERE (t1.name= ?) ORDER BY t1.id DESC

Answer 1

Even if you can add ROWNUM to this query you may still not get the results you want. The problem is that the WHERE clause is applied before the ORDER BY - thus, by limiting to the first 10 rows returned you'll get those ten rows, and then they'll be sorted. Here's an example:

CREATE TABLE order_test(seq_num  NUMBER);

INSERT INTO order_test(seq_num) VALUES(20);
INSERT INTO order_test(seq_num) VALUES(19);
INSERT INTO order_test(seq_num) VALUES(18);
INSERT INTO order_test(seq_num) VALUES(17);
INSERT INTO order_test(seq_num) VALUES(16);
INSERT INTO order_test(seq_num) VALUES(15);
INSERT INTO order_test(seq_num) VALUES(14);
INSERT INTO order_test(seq_num) VALUES(13);
INSERT INTO order_test(seq_num) VALUES(12);
INSERT INTO order_test(seq_num) VALUES(11);
INSERT INTO order_test(seq_num) VALUES(10);
INSERT INTO order_test(seq_num) VALUES(09);
INSERT INTO order_test(seq_num) VALUES(08);
INSERT INTO order_test(seq_num) VALUES(07);
INSERT INTO order_test(seq_num) VALUES(06);
INSERT INTO order_test(seq_num) VALUES(05);
INSERT INTO order_test(seq_num) VALUES(04);
INSERT INTO order_test(seq_num) VALUES(03);
INSERT INTO order_test(seq_num) VALUES(02);
INSERT INTO order_test(seq_num) VALUES(01);

SELECT * FROM order_test WHERE ROWNUM < 10 ORDER BY seq_num;

The query returns

12
13
14
15
16
17
18
19
20

on my system. I suspect that what you want is the equivalent of

SELECT *
  FROM (SELECT * FROM ORDER_TEST ORDER BY SEQ_NUM)
  WHERE ROWNUM < 10

which returns 1 through 9.

I don't know if or how you can nest queries in Glorp. (FWIW - I know and love Smalltalk, but I detest persistence frameworks for reasons such as this). YMMV, obviously.

(BTW - thinking about this for a minute, you might be able to modify the #limit: method on Glorp.SimpleQuery to inject a comparison to ROWNUM into the WHERE clause - but as I said above, the results may still not be what's intended. An implementation is left as an exercise to the interested reader :-).

Share and enjoy.