CODEWITHSUNDEEP
pythonmysqldjango
Pradeep
Pradeep

Reputation: 116

How to join tables in Django 1.8

I have tried to access joined data in my django template but nothing works, a little help is deeply appreciated. 

Model1():
   project_code = Foreignkey(another table1)
   shot_code = charfield(primary_key = True)
   shot_name = charfield()
   sequence_name = Integerfield()

Model2():
   vendor_id = Foreignkey(another table2)
   shot_code = Foreignkey(Model1, on_delete= models.CASCADE)
   shot_rate = integerfield()
   shot_bid = integerfield()

I wanted to display

Select * from Model1 a, Model2 b, where a.shot_code = b.shot_code 
and model1.project_code = "XXX"

and columns to be accessed in template are

1. Shot code
2. Shot name
3. Sequence name
4. Shot rate
5. Shot bid
6. Vendor id

I tried the following method

1. Using Select_related
    result = only values of model2 is displayed
    unable to access model1's data, 
    error = 'QuerySet' object has no attribute model1

Upvotes: 1

Views: 83

Answers (2)

mfrackowiak
mfrackowiak

Reputation: 1304

Do you expect this to return one or multiple instances? The best way to do this would be still with select_related, e.g.:

Model2.objects.filter(shot_code__project_code=<your value>).select_related("shot_code")

For queryset with multiple Model2 instances, or add .get() at the end if you expect only single instance.

Alternatively, you can add .values() and instead of operating on two related models, get dict-like join result (although note that you won't be able to reuse shot_code straightforward, as it would clash with your foreign key name):

Model2.objects.filter(shot_code__project_code=<your value>).annotate(
    sequence_name=F("shot_code__sequence_name"),
    shot_name=F("shot_code__shot_name"),
    real_shot_code=F("shot_code__shot_code")
).values(
    "sequence_name", "shot_name", "real_shot_code", "shot_rate", "shot_bid", "vendor_id"
)

And as always, I recommend to refrain from naming your ForeignKey as vendor_id, since it will place the real id under the vendor_id_id, and naming will be a bit unclear.

Upvotes: 1

Amit Nanaware
Amit Nanaware

Reputation: 3346

You can use object of Model1 query set in Model2 and get the data see below example:

model1obj = Model1.objects.get(project_code = "XXX")
model2obj = Model2.objects.get(shot_code = model1obj)

# now access all the fields using model1obj  and model2obj

Upvotes: 0

Related Questions