Imagine I have a database that represents a library, with a table storing the words within each book. Let's refer to this table as "books" and assume it includes rows like the following:
| book_name | word_in_book | word |
|-----------|--------------|---------|
| Moby Dick | 1 | call |
| Moby Dick | 2 | me |
| Moby Dick | 3 | ishmael |
If I am looking for a specific sequence of words (which could vary in length), what SQL query could I execute to retrieve a list of book_names containing that exact sequence where word_in_book is consecutive? For instance, if my sequence is ["call", "me", "ishmael"], the query should return "Moby Dick" since the book contains those words in order. However, searching for ["call", "me", "ahab"] would not yield any results because those words do not form a subarray within the book's words (the query should only return books with a matching subarray, not a matching subsequence).
I am utilizing knex alongside Express to construct my SQL statements. My assumption is that I will need to use knex to iterate through the array of words being searched and dynamically add elements to my query object for each word, but I am uncertain about how to go about doing this.
Here is the approach I have considered so far:
const knex = require("knex")({
// Connection details here ...
});
const words = ["call", "me", "ishmael"];
let query = knex("books");
words.forEach(word => {
query = ??? // Unsure about constructing my query
});
The actual database I am dealing with at my workplace closely resembles this example. The main distinction is that there are thousands of books, yet each book does not contain an extensive number of words (typically only a few hundred at most). The challenge lies in the fact that retrieving all content from every book and cross-checking all words using JavaScript would be quite sluggish, hence why I prefer knex/SQL to manage as much of this process as possible. What would be the most effective way to achieve this?