Doing a Join on a django queryset

Using Django
1

Hi All,

I want to learn the proper Django way of doing a Joint between 2 related models in Django, and I’m struggling a lot to do this, at the end I want to get just one queryset that I can render to an HTML page

class Leads(models.Model):

    project_id = models.BigAutoField(primary_key=True, serialize=False)
    created_at = models.DateTimeField(auto_now_add=True)
    expected_revenue = MoneyField(decimal_places=2,max_digits=14, default_currency='USD')
    expected_licenses = models.IntegerField()
    country = CountryField(blank_label='(select_country)')
    status = models.CharField(choices=[('Open', 'Open'), ('Closed', 'Closed'), ('Canceled', 'Canceled'),
                                       ('Idle', 'Idle')
                                       ], max_length=10)
    estimated_closing_date = models.DateField()
    services = models.CharField(choices=[('Illumination Studies', 'Illumination Studies'),
                                  ('Training', 'Training'),('Survey Design Consultancy', 'Survey Design Consultancy'),
                                  ('Software License', 'Software License'),
                                  ('Software Development','Software Development')], max_length=40)
    agent = models.ForeignKey(Profile, default='agent',on_delete=models.CASCADE)
    company = models.ForeignKey(Company,on_delete=models.CASCADE)
    point_of_contact = models.ForeignKey(Client, default='agent',on_delete=models.CASCADE)
    updated_at = models.DateTimeField(auto_now=True)
    application = models.CharField(choices=[('O&G','O&G'),('Renewables','Renewables'),('Mining','Mining'),
                                         ('Other','Other'),('CSS','CSS')],
                                default='O&G',max_length=20)
    sub_category = models.CharField(choices=[('Wind','Wind'),('Geo-Thermal','Geo-Thermal'),('Solar','Solar'),
                                             ('Tidal','Tidal')], max_length=20, blank=True)


class LeadEntry(models.Model):
    revenue = MoneyField(decimal_places=2,max_digits=14, default_currency='USD',blank=True)
    date = models.DateField()
    lead_id = models.ForeignKey(Leads,on_delete=models.CASCADE)
    id = models.BigAutoField(primary_key=True, serialize=False)
    probability = models.DecimalField(max_digits=2,
                                      decimal_places=1,
                                      default=0,
                                      validators=[MaxValueValidator(1.0,'The value should be less than 1.0'),
                                                  MinValueValidator(0.0,'The value should be more than 0.0')]
                                      )
    stage = models.CharField(choices=[('Lead','Lead'),('Deal','Deal')], default='Lead',max_length=10, blank=True)
    sales_order = models.CharField(
        max_length=9,
        validators=[RegexValidator(r'\d{4}.\d{4}', 'The value should be four digits, a character and four digits')],
        default='0000-0000',
        blank=True
    )

Lead ID is related to Project ID, so what I want to do is this type of Joint that I use very commonly on Pandas

merged = pd.merge(Leads ,Lead_Entry, on=[“project_id”])

So in the end I only want to merge the Leads that have a Lead_entry and throw away the leads that do not have a Lead_Entry

for example, would this be correct:

forecast = LeadEntry.objects.all()
leads = Leads.objects.all()
queryset = forecast | leads

Well, I just trued that and got an assertionError, so I guess that is not really the way to do it

2

See Making queries | Django documentation | Django

There’s nothing special to do.

Because of the foreign key relationship between LeadEntry and Leads, the queryset LeadEntry.objects.all() automatically gives you access to the related Leads.

Now, if you’re iterating over the set, you can use the select_related clause to avoid an N+1 query problem - LeadEntry.objects.all().select_related('lead_id')

If you’re looking at the query from the other direction, where you want to query on Leads that have LeadEntry objects related to it, you can use a filter: Leads.objects.filter(leadentry__isnull=False), and then you can avoid the N+1 query problem in this case by using prefetch_related.

1 Like
3

Ken,

Thanks that is very clear, but I still get 2 distinct querysets, with exactly the same length, how can I get all the information in one queryset?, as I want to render just one queryset to the HTML so I can build a table

Regards,
FCS

4

I don’t understand. What do you mean you’re getting two distinct querysets? Please post your view.

This statement:

is only going to give you one queryset.

5

Ken,

Please see below my view using select_related, returning only one queryset

@login_required
def forecast_table(request):
    forecast = LeadEntry.objects.all().select_related('lead_id')

    return render(request,'account/forecast_table.html',{'forecast':forecast
                                                         })

The result is a queryset that contains all my Lead_Entry with the correspondent information in the LeadEntry table, but how can I access the related information from the Lead table ?

example

  for i in forecast:
        print(i.stage)

gives me stage variable in LeadEntry

If I do the following:

  for i in forecast:
        print(i.country)

I get an error, AttributeError: ‘LeadEntry’ object has no attribute ‘country’

My question is how do I go about and getting that information from the Leads table, which is related to the same project_id /lead_id

6

Ken,

I got how it works, you have to use the related key name in order to get access to the data in the other model

    for i in forecast:
        print(i.lead_id.country)

Everything makes sense now