[Fixed]-Convert raw SQL to Django QuerySet

by

1👍

✅

from myapp.models import Profile
from django.db.models import Count

qs = Profile.objects.exclude(
    phone=''
).exclude(
    phone__isnull=True
).values('phone').annotate(
    phone_count=Count('user_id')
).filter(
    phone_count__gt=1
).values_list('phone', flat=True)
# the above will get you a list of phone numbers that appear more than once
# now get the user_ids, too, and add in the emails
qs = Profile.objects.filter(phone__in=qs).values_list('user__email', 'phone')
# now convert to a dict
from collections import defaultdict
data = defaultdict(list)
for email, phone in qs:
    data[phone].append(email)
# now convert to desired format
result = [{
    'phone': phone,
    'emails': ','.join(emails),
} for phone, emails in data.itervalues()]

# Of course, you could always just use raw and do a raw
# SQL query as described here:  https://docs.djangoproject.com/en/1.9/topics/db/sql/#executing-custom-sql-directly
👤2ps

Leave a comment