ReQL : Filtering Documents from Python List of Strings

Question

I'd to like to filter a table with ReQL using a (Python) list of strings (variable number of values) applied on several fields, ie in the logic of more strings in the list more result is accurate. Ideally the filtering should be case incensitive.

SQL equivalent could be something close to :

select * from mytable
where (field1 like '%AA%' and field1 like '%BB%'...)
or    (field2 like '%AA%' and field2 like '%BB%'...)
or    (field3 like '%AA%' and field3 like '%BB%'...)
...

I tested lot of solutions without success, for intance the one described here :

selection = list(r.table("mytable").filter(lambda d: 
       r.expr(searchWords).contains(d["field"]) 
).run(g.rdb_conn))

But 0 doc is returned (?).

Answer 1

Answering my own question. For those who could be interested I finally workarounded the problem by :

1. Iterating on all search words of input string

2. Searching and grabbing all DocIDs matching each word using :

    selectionDict = list(r.table('mytable').filter( \
    ( r.row["field1"].match("(?i)"+searchWord)) \
    | (r.row["field2"]["body"].match("(?i)"+searchWord) ) )  \
    .pluck("id") \
    .run(g.rdb_conn))

3. Building a dict for each DocID (key) with a "weight" as value. On each word found for a DocID the "weight" value is incremented by 1.

4. Once iteration is over all the DocIDs getting the same "weight" as number of words are relevant to be returned, meaning they match all search words. For instance with a 3 words string all DocIDs getting a 3 as "weight" (at the end) means that all words have been found for them.

5. get_all with DocIDs is then used to retrieve and return them.

Note the search is case insensitive, on multiple fields and can use partial words as I wanted initially. Likely not the best and cleanest way but works at least on not-too-large database.