使用SQLite按字段排序 [英] Order by field with 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.

这篇关于使用SQLite按字段排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！