LIKE SQL-query on an array using Peewee

Question

I have a PostgreSQL table that has a column with an array of tags (an array datatype in the tags column). Using Peewee (the python ORM), I would like to select all rows where any of the tags matches a part/substring of any string in a list.

So, for example, I could have a list that looks like this:

["stan", "tina"]

and that should match all rows in the table that has any of the tags afghanistan, pakistan, stanford or argentina.

The SQL query could look something like this:

SELECT * FROM media WHERE tags::text LIKE ANY (ARRAY[‘%stan%‘, ‘%tina%‘]);

How would I do this using Peewee?

The tags column is modelled like this:

tags = ArrayField(TextField)

Answer 1

assuming your model looks like

from peewee import Model, TextField
from playhouse.postgres_ext import ArrayField


class Media(Model):
    tags = ArrayField(TextField)

creating subquery with array field unnested

from peewee import fn

subquery = (Media.select(Media.id.alias('id'),
                         fn.unnest(Media.tags).alias('unnested_tags'))
            .alias('subquery'))

generating tags filter like

tags = ["stan", "tina"]
tags_filters = [subquery.c.unnested_tags.contains(tag) for tag in tags]
tags_filter = reduce(operator.or_, tags_filters)

and final query will be

query = (Media.select().join(subquery,
                             on=subquery.c.id == Media.id)
         .filter(tags_filter)
         # gets rid of duplicates
         .group_by(Media.id))

P. S.: in Python 3 reduce function is available at functools module while in Python 2 it is a built-in