Issue: sqlite_ext / apsw bm25 search AND regex select
Author: inktrap
State: closed
Created: 2022-07-07 09:47:58

I would like to combine a ranked search via sqlite-ext (bm25) on a set of documents where the content matches a regexp. It is basicly a filter, not only documents that match the search *term* are returned, but those that match the *regex* **and** (then) the search term.

Suppose I have this model:

```
from playhouse.sqlite_ext import FTS5Model, RowIDField, SearchField

class ItemIndex(FTS5Model):
    rowid = RowIDField()
    content = SearchField()

    class Meta:
        database = db
        options = {"tokenize": "porter"}
```

And a regex function like this:

```
import re
from playhouse.sqlite_ext import (SqliteExtDatabase)

def search(expr, item):
    return re.search(expr, item) is not None


db = SqliteExtDatabase("db.db", regexp_function=search)
```

Then I'll just create the table and while the functions work individually:

```
query = ItemIndex.search_bm25(search_query, with_score=True)
query = ItemIndex.select().where(ItemIndex.content.regexp(search_regex))
```

I can't chain or combine them.

Seems like the search result doesn't support select and select result don't support searches.

```
        ItemIndex.search_bm25(search_query, with_score=True)
        .select()
        .where(ItemIndex.content.regexp(search_regex))
```

```
peewee.OperationalError: near "FROM": syntax error
```

This is similar with apsw. And the other way around I'll just get `AttributeError: 'ModelSelect' object has no attribute 'search_bm25'` which I expected.

I tried to use `intersect` but then I'll get different error messages, depending on wether I'll turn scoring off or not and depending on calling `execute()` of the search query before intersecting. I don't think intersect is ment to work with search results but with another select-object anyways.

So the last idea is to get the IDs via search, then select via regex and then refine via  `.in_(value)` where `value` is the list of IDs. Then somehow get the ranking info back. That seems inefficient but doable.

How would you approach this?

Facetting is typically done on the set of search results anyways, so this seems like an application for `in_`, may it be a matching regex or some attribute value that is more commonly used for facetting.

I haven't looked at it in detail but I guess sqlite_ext does a phrase search (and won't tokenize my input and just assume some operator) and thus I have to query it per-term (which is a good thing) and combine the results. But all of that is out of peewee's scope. The point is that I'll have to combine/process the search results anyways if I want a query language with operators.

You don't need to re-select, just write: 

```
query = (ItemIndex
         .search_bm25(search_query, with_score=True)
         .where(ItemIndex.content.regexp(search_regex)))
```

Also, Peewee provides a `regexp_function` already and the parameter is a boolean - not a callable.

Example:

```python
db = SqliteExtDatabase(':memory:', regexp_function=True)

class Idx(FTS5Model):
    name = SearchField(unindexed=True)
    content = SearchField()
    class Meta:
        database = db

db.create_tables([Idx])

Idx.insert_many([
    ('item1', 'item1 alpha'),
    ('item2', 'item2 beta'),
    ('item1-1', 'item1-1 alpha epsilon'),
    ('item1-2', 'item1-2 alpha sigma'),
    ('item1-3', 'item1-3 alpha rho'),
    ('item1-4', 'item1-4 alpha tau'),
]).execute()


query = (Idx
         .search_bm25('alpha', with_score=True)
         .where(Idx.content.regexp('item1-[123]')))
for row in query:
    print(row.name, row.content)

# item1-1 item1-1 alpha epsilon
# item1-2 item1-2 alpha sigma
# item1-3 item1-3 alpha rho
```

Yes, this is clear to me now because i read more docs. Thanks for the examples and your help! Appreciate it!