filter on multiple columns in subquery

Question

I need to be able to filter on a multiple column row in a subquery

In sql this would be something like

where (account_details.account_detail_id, tasks.base_action_type_id)  IN 
(
SELECT tasks.account_detail_id  , tasks.base_action_type_id
FROM tasks 
WHERE tasks.dt_created > now() - interval '1 day'
GROUP BY tasks.account_detail_id ,tasks.base_action_type_id
HAVING count(tasks.account_detail_id) < 3
)

But I Cannot find any information on the format for sqlalchmey

I tried

dayBefore = datetime.utcnow() - timedelta(hours=24)

subquery = Tasks.query.with_entities(Tasks.account_detail_id, Tasks.base_action_type_id).\
    filter(Tasks.dt_created >  dayBefore).\
    group_by(Tasks.account_detail_id, Tasks.base_action_type_id).\
    having(func.count(Tasks.account_detail_id) < 3)

sql = AccountDetails.query.join(Tasks, Tasks.account_detail_id == AccountDetails.account_detail_id).\
    filter(AccountDetails.account_detail_id == 3,  (AccountDetails.account_detail_id,Tasks.base_action_type_id).in_(subquery))

Note the line

(AccountDetails.account_detail_id,Tasks.base_action_type_id).in_(subquery)

But that just errors out as invalid syntax

score 0 · Answer 1 · answered Oct 01 '14 at 15:36

0

well as usual, hours of searching before posting found nothing. First search after posting question found the answer

Using sqlalchemy to query using multiple column where in clause

answered Oct 01 '14 at 15:36

Martin

837
1
10
18

filter on multiple columns in subquery

1 Answers1