CODEWITHSUNDEEP
django
Guillaume Vincent
Guillaume Vincent

Reputation: 14791

Django find all rows matching 2 columns criteria

Imagine the model Event like this

name email
A [email protected]
B [email protected]
B [email protected]
C [email protected]
B [email protected]
B [email protected]
A [email protected]
B [email protected]

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]).

Upvotes: 0

Views: 80

Answers (2)

Guillaume Vincent
Guillaume Vincent

Reputation: 14791

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.

Upvotes: 1

Niko
Niko

Reputation: 3798

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')

Upvotes: 0

Related Questions