Pewee index search with SQLite

Question

I am using the following query to perform a text search using SQLite.

docs = DocumentIndex.search(
    'search term',
    weights={'title': 2.0, 'content': 1.0},
    with_score=True,
    score_alias='search_score')

for result in docs:
    print result.title, result.search_score

The result is a ranked list of DocumentIndex. How can I join this result with the Document table, so that it returns a list of Document preserving the original ranked order?

I tried the following:

return Document.select().where(docs)

but it doesn't work saying that only a single result allowed

Answer 1

The way I prefer to handle this kind of thing is to ensure that the docid of the full-text-search table is the same as the primary key of whatever is being searched.

So I'd have:

class Document(Model):
    content = TextField()

class DocumentIndex(FTSModel):
    docid = DocIDField()
    content = TextField()

    class Meta:
        extension_options = {'tokenize': 'porter'}

document = Document.create(content='something')
DocumentIndex.create(content=document.content, docid=document.id)

Then when searching, I'd do something like:

query = (Document
         .select(Document, DocumentIndex.bm25().alias('score'))
         .join(DocumentIndex, on=(Document.id == DocumentIndex.docid))
         .where(DocumentIndex.match(search_term))
         .order_by(SQL('score'))

Note: FTS3 and FTS5 use "rowid" instead of "docid".