Django Queryset get all values and first related

Question

I need to get a json response with "customer" fields and the first phone number associated.

# models.py
class Customer(models.Model):
    
    name = models.CharField(max_length=255)
    surname = models.CharField(max_length=255,)
    ...

class CustomerPhone(models.Model):
    
    customer = models.ForeignKey(Customer, related_name = 'phones', on_delete=models.CASCADE)
    phone_number = PhoneNumberField()
    ...

My customer can have more phone numbers but in summary table I would like to see only the first phone (with min id)

When I try with this queryset:

Customer.objects.all().values('surname','phones__phone_number')

I obtain

<QuerySet [{'surname': 'Gates', 'phones__phone_number': '+39123456789'}, {'surname': 'Gates', 'phones__phone_number': '+3998765431'}, {'surname': 'Trump', 'phones__phone_number': '+32123456001'}, {'surname': 'Trump', 'phones__phone_number': '+3298765000'}]>

What I can obtain only one result for customer with only one phone number? what can I get

willeM_ Van Onsem · Accepted Answer · 2020-08-11 12:12:58Z

3

You can make use of a SubQuery expresssion [Django-doc]:

from django.db.models import OuterRef, Subquery

Customer.objects.values('surname').annotate(
    phone_number=Subquery(
        CustomerPhone.objects.filter(
            customer_id=OuterRef('pk')
        ).order_by('pk').values('phone_number')[:1]
    )
)

answered Aug 11, 2020 at 12:12

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Diego Magalhães · Accepted Answer · 2020-08-11 12:20:19Z

1

Your relationship between CustomerPhone and Customer is a ForeignKey, so Customer will have a customer_phone_set field. What you need to do is:

 customer = Customer.objects.get(surname="example")
 first_phone_number = customer.customer_phone_set.first()

answered Aug 11, 2020 at 12:20

Diego Magalhães

1,77212 silver badges14 bronze badges

Collectives™ on Stack Overflow

Django Queryset get all values and first related

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related