python - How to build Django queries to get list of data that satisfy many conditions?

Question

I am trying to get a list of user_id by executing such sqlite query.

SELECT
    u.user_id 
FROM
    users u 
WHERE
    u.role_id IN ( 1, 2 ) 
    AND ( SELECT COUNT( * ) FROM purchases p WHERE p.user_id = u.id ) >= 1 
    AND (
    SELECT
        tagged.tag_id 
    FROM
        tagged
        INNER JOIN ( SELECT polled.answer_id FROM polled WHERE polled.user_id = u.id ) AS a 
    WHERE
    a.answer_id = tagged.answer_id 
    ) IN ( 1,2 )

How can run that sql using django orm? It's so hard to understand logic querysets like this...

Users.objects.annotate(cnt=Count('purchases')).filter(Exists(Polled.objects.filter(user=OuterRef('pk')))).filter(cnt__gt=1).filter(role__in=[1, 2]).values_list('user_id', flat=True)

ForeignKeys Relations Image

Please help to build correct queries to get list of users that satisfy the conditions. Thanks.

标签： pythondjangodjango-modelsdjango-queryset