Home > Software design >  Select related is not returning all the values from the relation in Django
Select related is not returning all the values from the relation in Django

Time:12-22

I'm doing this query

SELECT [User].[User_Id], [User].[Client_id], [User].[EMail], [User].[First_Name], [User].[Family_Name], [User].[Telephone], [Clients].[Client_Id], [Clients].[Name], [Clients].[Organization_type] FROM [User] INNER JOIN [Clients] ON ([User].[Client_id] = [Clients].[Client_Id]) WHERE [User].[EMail] = '[email protected]'

In the SQL server is working fine even when I print in django the queryset looks good, but, when getting the results is not getting it from the Clients` table, It has to be something with the relation between tables in Django but I really don't know where

Here are my two models

class V2_Clients(models.Model):
    Client_Id = models.CharField(primary_key=True, max_length=50)
    Name = models.CharField(max_length=255, null=True)
    Organization_type = models.CharField(max_length=128, null=True)
    class Meta: 
        managed = True
        db_table = "[Clients]"

class V2_Users(models.Model):
    User_Id = models.CharField(primary_key=True, max_length=50)
    Client = models.ForeignKey(V2_Clients, on_delete=models.CASCADE)
    EMail = models.CharField(max_length=250)
    First_Name = models.CharField(max_length=50, null=True)
    Family_Name = models.CharField(max_length=50, null=True)
    Telephone = models.CharField(max_length=50, null=True)
    class Meta: 
        managed = True
        db_table = "[User]"

This is where I do the query, even when I do print(v2_user.query) I get the same SQL shown at the top, but is not getting the values from the Clients table only the results from the User

v2_user = V2_Users.objects.using('sl_v2').filter(EMail=jsonData['Client']['Client_Email']).select_related()

What could be the issue?

CodePudding user response:

So one Client can have many Users, but one User can have only one Client. Judging by your models. You want to get data from User and all related in this case Client table. If i understood you correctly.

views.py

user = V2_Users.objects.get(email=email_json) 
return render(request, tmpl_name, {"user":user})

template

First name: {{ user.First_name }}
Client: {{ user.client.Name }}

CodePudding user response:

The select_related is doing the join in the internal SQL (meaning that if you try to access a Client object immediately afterwards, it won't then need to do another SQL operation) but the QuerySet that Django returns will be consistent with the QuerySet definition of the object.

If you want the information from the Client table, you likely want to make use of the annotate method to add this column information to your returned Queryset

Alternatively, you should be able to access any information you want from the Client table through v2_user.client (as in the previous answer).

  • Related