使用 SQLite 按字段排序

2022-01-15 00:00:00 lucene mysql sqlite

我实际上正在从事一个 Symfony 项目，我们正在使用 Lucene 作为我们的搜索引擎.我试图使用 SQLite 内存数据库进行单元测试(我们使用的是 MySQL)，但我偶然发现了一些东西.

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.

项目的搜索引擎部分使用 Lucene 索引.基本上，您查询它并获得一个有序的 id 列表，您可以使用 Where In() 子句查询您的数据库.问题是查询中有一个 ORDER BY Field(id, ...) 子句，它按照与 Lucene 返回的结果相同的顺序对结果进行排序.

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.

有没有使用 SQLite 的 ORDER BY Field 的替代方法?还是有另一种方法可以像 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 ?

谢谢:)

简化的查询可能如下所示:

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)

推荐答案

这是相当讨厌和笨拙，但它应该工作.创建一个临时表，并插入 Lucene 返回的有序 ID 列表.将包含项目的表加入到包含有序 ID 列表的表中:

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;

输出:

thing 2
thing 3
thing 1

是的，这种 SQL 会让人不寒而栗，但我不知道 ORDER BY FIELD 的 SQLite 等价物.

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.

相关文章