javascript - 如何使用 knex 从 SQL 表中选择行值连续的情况？

Question

假设我有一个代表图书馆的数据库，以及一个存储每本书中单词的表。我们将该表称为“books”，并假设它有如下行:

| book_name | word_in_book | word    |
|-----------|--------------|---------|
| Moby Dick | 1            | call    |
| Moby Dick | 2            | me      |
| Moby Dick | 3            | ishmael |

如果我有一个想要查找的单词序列(可以是任意数量的单词长)，我可以运行什么 SQL 查询来返回具有该序列的 book_name 列表word_in_book 连续的单词数？例如，如果我有列表 [“call”、“me”、“ishmael”]，则查询将返回“Moby Dick”，因为这本书按顺序排列了该单词序列。但是，使用 ["call", "me", "ahab"] 运行它不会返回该书，因为这些单词不是书中单词的子数组(因此它应该只返回具有匹配子数组的书籍，不是匹配的子序列)。

我正在使用knex使用 Express 构建我的 SQL 语句。我的预感是，我需要使用 knex 循环遍历要搜索的单词数组，并为每个单词在查询对象上添加一些内容，但我不知道该怎么做。

目前我能想到的就是这些:

const knex = require("knex")({
  // Connection details here ...
});
const words = ["call", "me", "ishmael"];

let query = knex("books");
words.forEach(word => {
  query = ??? // Not sure how to build my query
});

我在工作中使用的真实数据库与此非常相似。不同的是，有几千本书，但每本书没有那么多字(最多只有几百字)。问题是，选择每本书的所有内容并使用 JavaScript 检查所有单词会非常慢，所以我希望 knex/SQL 完成尽可能多的工作。最好的方法是什么？

Answer 1

首先，您想要执行的查询类似于:

SELECT books.book_name
From books
join books bw2 on bw2.book_name = books.book_name AND bw2.word_in_book = books.word_in_book + 1 AND bw2.word = 'me'
join books bw3 on bw3.book_name = books.book_name AND bw3.word_in_book = books.word_in_book + 2 AND bw3.word = 'ishmael' 
where books.word = 'call'
Group by books.book_name -- avoid having twice the same book.

如您所见，您必须多次加入同一个表才能找到下一个单词。在某些数据库上使用用户定义的变量可能有一个更简单的查询，但 knex 似乎不支持它(无法在您给出的链接中读取它)。

为了让这个查询工作得不太慢，你应该在三列上添加一个复合索引(你没有提供你的支持数据库，但如果你使用 mysql/mariadb 它将是:

ALTER TABLE books ADD INDEX (word, book_name, word_in_book);

)。为表建立索引对于此查询非常重要。 SQL Demo

接下来，使用 knex 创建查询:

const words = ["call", "me", "ishmael"];

var query = knex("books").select({
    book_name_searched: 'books.book_name'
}).where('books.word', words[0]);
words.forEach( (word, index) => {
    if (index < 1) return;
    query = query.join('books as bw' + index, function() {
        this.on('bw' + index + '.book_name', '=', 'books.book_name')
           .andOn(knex.raw('bw' + index + '.word = \'' + words[index] + '\''))
           .andOn(knex.raw('bw' + index + '.word_in_book = books.word_in_book + ' + index))
    })
});

query.groupBy('books.book_name');

query.toString();
// "select `books`.`book_name` as `book_name_searched` from `books` inner join `books` as `bw1` on `bw1`.`book_name` = `books`.`book_name` and bw1.word = 'me' and bw1.word_in_book = books.word_in_book + 1 inner join `books` as `bw2` on `bw2`.`book_name` = `books`.`book_name` and bw2.word = 'ishmael' and bw2.word_in_book = books.word_in_book + 2 where `books`.`word` = 'call' group by `books`.`book_name`"

我没有使用 knex 对真实数据库运行它，但查询字符串似乎不错。如果它不起作用请告诉我，我希望您至少有想法来编写您的查询。