CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-queryset
hustas88
hustas88

Reputation: 307

Convert raw SQL to Django QuerySet

Could someone show how to convert the following SQL query to the Django QuerySet call? I'm a new to Django and I could use the raw query, but it's interesting for me to know how do this query could be written as a native Django call.

There are 2 models User & Profile 1:1 I need to fetch the emails from the User and the phone from the Profile if the same phone number appears in 2 or more profiles.

SELECT GROUP_CONCAT(u.email) as emails, p.phone as phone_number FROM profile AS p JOIN auth_user AS u on u.id = p.user_id GROUP BY phone HAVING COUNT(phone) > 1

Here what I tried to do:

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

Profile.objects
       .exclude(phone='')
       .annotate(phone_count=Count('phone'))
       .values('phone')

..and the result is:

[
  {
    'phone': '***'
  },
  {
    'phone': '***'
  }
]

If to add .filter(phone_count__gt=1) to the query then an empty result will be returned (don't understand why).

The desired output is:

[
    {
      'phone': '***',
      'emails': '[email protected]'
    },
    {        
      'phone': '***',
      'emails': '[email protected],[email protected]'
    }
  ]

UPD

class Profile(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    phone = models.CharField(max_length=100, blank=True, null=True)

Upvotes: 0

Views: 2524

Answers (1)

2ps
2ps

Reputation: 15926

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

Upvotes: 1

Related Questions