I'm building a Django app that manages client data. I store phone numbers, email addresses, addresses, etc., in lookup tables. I would like to create a queryset that returns the primary numbers for all clients.

Here is an abbreviated version of the client table:

An example of the phones table:

And finally, the phone_type table:

An extract of the client model:

class Client(models.Model):
    id = models.IntegerField(primary_key=True)     
    last = models.CharField(max_length=32)
    first = models.CharField(max_length=32)
    phone = models.ForeignKey(
        Phone,
        on_delete=models.PROTECT,
        blank=False,
        null=False
    )

The phone model:

class Phone(models.Model):
    id = models.IntegerField(primary_key=True)
    client_id = models.IntegerField
    type_id = models.ForeignKey(
        PhoneType,        
        on_delete=models.PROTECT,
        blank=False,
        null=False)
    is_primary = models.BooleanField
    country_code = models.CharField(max_length=5)
    phone_number = models.CharField(max_length=16)

And the phone_type:

class PhoneType(models.Model):
    id = models.SmallIntegerField(primary_key=True)
    type = models.CharField(max_length=16, blank=False, null=False)

My ClientListView:

class ClientListView(ListView):
    model = Client
    template_name = 'client/client_list.html'
    context_object_name = 'clients'

    def get_queryset(self):
        return Client.objects.order_by('-id').filter(status_id=3).select_related(Phone)

The get_queryset function is a placeholder for now.

How can I replace the get_queryset function so that I'm able to list the primary phone number for each client based on the "is_primary" field in the phones table?

CodePudding user response：

I would use the prefetch api with a queryset on top of it.

from django.models import Prefetch

Client.objects
    .prefetch_related(
        'phone_numbers',
        queryset=Phone.objects.filter(is_primary=True),
    )

But this would only work if your model is correctly defined. In your Phone class, client_id seems to be a foreign key to Client. So you should have:

Phone(models.Model):
    client = models.ForeignKey('Client', related_name='phone_numbers')