Imagine the model Event like this

I would like to find all emails that contain name A and B. In my example ["[email protected]", "[email protected]"]

Today I'm doing

emails = [
    e["email"]
    for e in models.Event.objects.filter(name__in=["A", "B"])
    .values("email")
    .annotate(count=Count("id"))
    .order_by()
    .filter(count__gt=1)
]

It's not working because I'm also getting duplicates of emails containing only one name (like [email protected]).

CodePudding user response：

If you don't need the model, there is this option, that yields the expected result:

from django.db import connection

def get_random_events(request):
    cursor = connection.cursor()
    cursor.execute("SELECT DISTINCT email FROM event WHERE name = 'A' OR 'B'")
    for row in cursor:
        print(row[0])

    return  render(request, 'blank.html')

As for the ORM the problem is the last part of the query, it does not seem possible to properly build the WHERE clause. My best attempt was using Q lookups, still...the same problem:

RandomEvent.objects.values('email').distinct().filter(Q(name='B') | Q(name='A'))

# Query Structure
SELECT DISTINCT email FROM random_event WHERE (name = 'B' OR name = 'A')

CodePudding user response：

After trying different approach, I found the solution

events = ["A", "B"]
emails = [
    e["email"]
    for e in models.Event.objects.filter(name__in=events)
    .values("email")
    .annotate(count_name=Count("name", distinct=True))
    .order_by()
    .filter(count_name=len(events))
]

I need to group by email and count number of distinct name and filter by count equals to my number of events.