Django - Following a foriegn key relationship (i.e JOIN in SQL)

Question

Busy playing with django, but one thing seems to be tripping me up is following a foreign key relationship. Now, I have a ton of experience in writing SQL, so i could prob. return the result if the ORM was not there.

Basically this is the SQL query i want returned

Select
  table1.id
  table1.text
  table1.user
  table2.user_name
  table2.url
from table1, table2
where table1.user_id = table2.id

My model classes have been defined as:

class Table1(models.Model):
    #other fields
    text        = models.TextField()
    user        = models.ForeignKey('table2')

class Table2(models.Model):
    # other fields
    user_name = models.CharField(max_length=50)
    url = models.URLField(blank=True, null=True)

I have been through the documentation and reference for querysets, models and views on the django website. But its still not clear on how to do this.

I have also setup the url with a generic list view, but would like to access the *user_name* field from the second table in the template. I tried *select_related* in urls.py and also via the shell but it does not seem to work. See examples below.

config in urls

url(r'^$','django.views.generic.list_detail.object_list', { 'queryset': Table1.objects.select_related() }),

At the shell

>>> a = Table1.objects.select_related().get(id=1)
>>> a.id
1
>>> a.user_name
Traceback (most recent call last):
  File "<console>", line 1, in <module>
AttributeError: 'Table1' object has no attribute 'user_name'

So basically,

• What am i doing wrong?
• Am i missing something?
• What's the best way to pass fields from two tables in the same queryset to your template (So fields from both tables can be accessed)
• Can this be done with generic views?

Answer 1

Something like this should work:

u = Table1.objects.get(id=1)
print u.id
print u.user.user_name

If you want to follow a foreign key, you must do it explicitly. You don't get an automatic join, when you retrieve an object from Table1. You will only get an object from Table2, when you access a foreign key field, which is user in this example

Answer 2

select_related() do not add second table result directly to the query, it just "Loads" them, insead of giving to you lazily. You should only use it, when you are sure it can boost your site performance. To get second table you need to write

a.user.username

In Django to get related table you need to follow them through foreign-keys that you have designed. Query itself do not directly translate to SQL query, because it is "lazy". It will execute only SQL that you need and only when you need.

If you have select_related SQL would have been executed at the moment when you do original query for a. But if you didn't have select_related then it would load DB only when you actually execute a.user.username.