Django retrieve data from two tables

Question

i have the following model:

class Purchases(models.Model):
    p_id = models.IntegerField(primary_key=True, default=0)
    date = models.DateField(default=datetime.now)

    def __str__(self):
        return self.date.strftime('%Y-%m-%d')

    class Meta:
        verbose_name_plural = "Purchases"


class Purchased_Items(models.Model):
    p_id = models.ForeignKey(Purchases, on_delete=models.CASCADE)
    item = models.CharField(max_length=80)
    size = models.IntegerField(max_length=2)
    quantity = models.IntegerField(max_length=3)
    price = models.IntegerField(max_length=4)
    total = models.IntegerField(default=0)

    def __str__(self):
        return self.item

    class Meta:
        verbose_name_plural = "Purchased Items"

I am trying to retrieve using inner join:

pur = Purchased_Items.objects.all().select_related()

But i am not getting the required result. Basically I want the following sql:

select * from finance_purchases as fp
     inner join finance_purchased_items pi 
           ON (fp.p_id = pi.p_id_id);

another question is although the foreign key in Purchased_Items is p_id but in the column in sql table is p_id_id! why? What is the logic behind it?

Thanks

have you seen this post? https://stackoverflow.com/questions/4125379/django-implementing-join-using-django-orm — Ofir, Apr 23 '18 at 18:02
Thanks for the quick reply! I have read this post and all others in internet even the documentation,but don't know what i have done wrong that it does not select the data from both tables. it only selects from the Purchased_Items! I am using Django version2. — Rafa, Apr 23 '18 at 18:07

rtindru · Answer 1 · 2018-04-24T02:23:38.623

1

Re: Select Related

The Django ORM allows you to access the Purchases object from the Purhcased_Items object directly using a simple attribute reference.

Example:

item = Purchased_Items.objects.get(pk=123)  # This is the `Purchase_Items` instance
purchase = item.p_id  # This is the `Purchases` instance

This means that the "join" operation is permitted by default in the ORM. The reason for using select_related is when you perform this foreign-key reference, Django fires a discrete query each time. So if you had code that looked like this:

items = Purchased_Items.objects.filter()[:100]  # This fires 1 sql query
for i in items:
    print i.purchase  # This line fires 1 SQL query

then a total of 101 SQL queries are fired. This is also called the N+1 Select Query Issue1

This can be avoided if you change the line to:

items = Purchased_Items.objects.filter().select_related('p_id')[:100]  # This fires just 1 sql query for the entire operation
for i in items:
    print i.purchase  # No query fired, data is already selected in Join

You can see the difference by printing the query in django

print Purchased_Items.objects.filter().query
print Purchased_Items.objects.filter().select_related('p_id').query

Re: p_id_id

Django ORM auto-creates the field name with a _id suffix for ForeignKey relationships. The idea is that the object is accessible directly as item.p and the database field key is set to be p_id. This helps you retrieve the purchase object

edited Apr 24 '18 at 02:23

answered Apr 23 '18 at 18:20

rtindru

5,107
9
41
59

I run items = Purchased_Items.objects.filter().select_related('Purchases')[:100] and then for i in items: print(i.Purchases), but i am getting this error: django.core.exceptions.FieldError: Invalid field name(s) given in select_related: 'Purchases'. Choices are: p_id – Rafa Apr 23 '18 at 19:02
Updated code, the attribute to use as `p_id`. Also, do read up on django model naming convention. Plurals are generally not used in model names, except for m2m fields. – rtindru Apr 24 '18 at 02:25
by running items = Purchased_Items.objects.filter().select_related('p_id') i am getting Purchased_Items fields but not getting Purchases fields. However when running print Purchased_Items.objects.filter().select_related('p_id').query and pasting the output in mysql terminal works without any problem. Still dont know what to do! – Rafa Apr 26 '18 at 06:56
How are you accessing the purchases field. Share code please – rtindru Apr 26 '18 at 07:38
in my view: items = Purchased_Items.objects.select_related('p_id') context = { 'items': items, } in the template: {% for i in items %} {{forloop.counter}}. {{ i.date }}{{ i.total }} {% endfor %} – Rafa Apr 26 '18 at 09:13
Whenever you are accessing from the related object, you need to specify that in code. Change `{{ i.date }}` to `{{ i.p_id.date }}`. The `i.p_id` references the `Purchases` instance, and then you have access to the `date` attribute – rtindru Apr 26 '18 at 13:26
When django performs `select_related` it fetches the data from DB, but keeps it as part of the related field. It does not flatten everything into one object. That way, the sanity of each object is preserved, `Purchased_Items` has only the fields that it is supposed to have, and likewise `Purchases`. The underlying query fetches all the data at once, but Django keeps them distinct, and allows you to access using the `.` notation. Hope this is clear! – rtindru Apr 26 '18 at 13:28

Django retrieve data from two tables

1 Answers1

Re: Select Related

Re: p_id_id