python - sqlalchemy select duplicates from multiple lists

Question

I Have two list (a real case):

user_1_list = [(3, True), (4, True), (5, True), (6, True), (7, True), (8, True), (9, True), (12, True), (13, True), (14, True)]  # 10 items
user_2_list = [(1, True), (2, True), (3, False), (4, True), (5, True), (6, True), (7, True), (8, False)]  # 8 items

both lists I'm getting via sqlalchemy query:

db.query(Table.post_id, Table.bool_value).filter(Table.user_id == user_id).all()

How I can combine a single sqlalchemy query to get post_id - bool_value pairs that present (matching) in both lists? P.S. list with distinct pairs I also need.

Answer 1

UPD

There are couple of ways I have on my mind:

1. utilize count and window functions, and then filter where count = 2 (but it just a bit complex to write this in sqlalchemy)
2. Just do inner join

Please, try something like this

user_query_1 = db.query(Table.post_id, Table.bool_value).filter(Table.user_id == user_id_1).subquery()
user_query_2 = db.query(Table.post_id, Table.bool_value).filter(Table.user_id == user_id_2).subquery()

(
    user_query_1
    .join(
        user_query_2,
        and_(
            user_query_1.c.post_id == user_query_2.c.post_id,
            user_query_1.c.bool_value == user_query_2.c.bool_value,
        )
    )
    .all()
)