Convert join SQL query into django

Question

I am new to django and trying out stuff with it.

How do I display selected fields from the joined table. For example:

I have two models, X and Y. I am merging these two models based on the foreign key of model Y.

class X(models.Model):
    name = models.CharField()
    id = models.AutoField(primary_key=True)


class Y(models.Model):
    owner_user = models.ForeignKey(X, models.DO_NOTHING, 
    db_column='id')
    detail = models.CharField()

How do I write this query as a django code?

SELECT name, id, Body_details FROM X, Y WHERE X.id = Y.OwnerUserId;

Can you please share the actual models? – ruddra Feb 12 '20 at 04:53 — ruddra, Feb 12 '20 at 04:53
https://docs.djangoproject.com/en/3.0/topics/db/queries/ – iklinac Feb 12 '20 at 04:59 — iklinac, Feb 12 '20 at 04:59
I have added the model files. – Vasanth S Feb 12 '20 at 05:01 — Vasanth S, Feb 12 '20 at 05:01

score 1 · Answer 1 · answered Feb 12 '20 at 05:12

1

You can use select_related

a = Y.objects.select_related('OwnerUserId').all()

for object in a:
    print(object.OwneruserId.name, object.OwneruserId.id, object.body)

answered Feb 12 '20 at 05:12

Vijay

17
3

Hey thank you for the response. But is there a way to return it as a HttpResponse in Json format instead of using print statement. I am stuck with that part. – Vasanth S Feb 12 '20 at 05:17
@lazycoderboi, checkout : https://stackoverflow.com/a/2428119/5588787 – Vijay Feb 12 '20 at 05:33

dipesh · Answer 2 · 2020-02-12T06:31:35.787

1

You can make use of select_related here.

result = Y.objects.select_related('owner_use')

All the work behind joining will automatically be done by this ORM using select_related. You can see previously asked questions similar to this one here.

edited Feb 12 '20 at 06:31

answered Feb 12 '20 at 05:16

dipesh

763
2
9
27

score 0 · Answer 3 · answered Feb 12 '20 at 05:25

0

You need to use the related_name of the ForeignKey field, which is y_set by default, to access the reverse relationship of model :

some_id = 1
instance = X.objects.get(id=some_id)
instance.y_set.all()

answered Feb 12 '20 at 05:25

Lord Elrond

13,430
7
40
80

Convert join SQL query into django

3 Answers3