Order by field with SQLite

Question

I'm actually working on a Symfony project at work and we are using Lucene for our search engine. I was trying to use SQLite in-memory database for unit tests (we are using MySQL) but I stumbled upon something.

The search engine part of the project use Lucene indexing. Basically, you query it and you get an ordered list of ids, which you can use to query your database with a Where In() clause. The problem is that there is an ORDER BY Field(id, ...) clause in the query, which order the result in the same order as the results returned by Lucene.

Is there any alternative to ORDER BY Field using SQLite ? Or is there another way to order the results the same way Lucene does ?

Thanks :)

Edit:

Simplified query might looks like this :

SELECT i.* FROM item i
WHERE i.id IN(1, 2, 3, 4, 5)
ORDER BY FIELD(i.id, 5, 1, 3, 2, 4)

Answer 1

This is quite nasty and clunky, but it should work. Create a temporary table, and insert the ordered list of IDs, as returned by Lucene. Join the table containing the items to the table containing the list of ordered IDs:

CREATE TABLE item (
    id INTEGER PRIMARY KEY ASC,
    thing TEXT);

INSERT INTO item (thing) VALUES ("thing 1");
INSERT INTO item (thing) VALUES ("thing 2");
INSERT INTO item (thing) VALUES ("thing 3");

CREATE TEMP TABLE ordered (
    id INTEGER PRIMARY KEY ASC,
    item_id INTEGER);

INSERT INTO ordered (item_id) VALUES (2);
INSERT INTO ordered (item_id) VALUES (3);
INSERT INTO ordered (item_id) VALUES (1);

SELECT item.thing
FROM item
JOIN ordered
ON ordered.item_id = item.id
ORDER BY ordered.id;

Output:

thing 2
thing 3
thing 1

Yes, it's the sort of SQL that will make people shudder, but I don't know of a SQLite equivalent for ORDER BY FIELD.