Search code examples
pythonpeewee

Peewee: how to make a query with a set of tuple?

My SQL table:

CREATE TABLE Message (
    message_id varchar(40) PRIMARY KEY NOT NULL,
    channel_id integer NOT NULL,
    user_id integer NOT NULL,
)

I have a set of tuple, the first item is a user_id field and the second is a channel_id field. Example of set:

user_channel_set = {(156, 23), (235, 15), (156, 15)}

I would like to know if there is a way to do this but with only one query.

for t in user_channel_set:
    Message.select().where((Message.user_id == t[0]) & (Message.channel_id == t[1]))
Solution

  • Nice question. You can try using a VALUES statement and a common-table expression.

    val_list = ValuesList(list(user_channel_set)).cte('vals', columns=['uid', 'cid'])
query = (Message
         .select()
         .join(val_list, on=(
             (Message.user_id == val_list.c.uid) &
             (Message.channel_id == val_list.c.cid)))
         .with_cte(val_list))