What is the internal function in django to add new tables to a queryset in a sensible way?

Question

In django 1.2:

I have a queryset with an extra parameter which refers to a table which is not currently included in the query django generates for this queryset.

If I add an order_by to the queryset which refers to the other table, django adds joins to the other table in the proper way and the extra works. But without the order_by, the extra parameter is failing. I could just add a useless secondary order_by to something in the other table, but I think there should be a better way to do it.

What is the django function to add joins in a sensible way? I know this must be getting called somewhere.

Here is some sample code. It selects all readings for a given user, and annotates the results with the rating (if any) given by another user stored in 'friend'.

class Book(models.Model):
    name = models.CharField(max_length=200)
    urlname = models.CharField(max_length=200)
    entrydate=models.DateTimeField(auto_now_add=True)

class Reading(models.Model):
    book=models.ForeignKey(Book,related_name='readings')
    user=models.ForeignKey(User)
    rating=models.IntegerField()
    entrydate=models.DateTimeField(auto_now_add=True)

readings=Reading.objects.filter(user=user).order_by('entrydate')

friendrating='(select rating from proj_reading where user_id=%d and \
book_id=proj_book.id and rating in (1,2,3,4,5,6))'%friend.id

readings=readings.extra(select={'friendrating':friendrating})

at the moment, readings won't work because the join to readings is not set up correctly. however, if I add an order by such as:

.order_by('entrydate','reading__entrydate')

django magically knows to add an inner join through the foreign key and I get what I want.

additional information:

print readings.query ==>

select ((select rating from proj_reading where user_id=2 and book_id=proj_book.id and  rating in (1,2,3,4,5,6)) as 'hisrating', proj_reading.id, proj_reading.user_id, proj_reading.rating, proj_reading.entrydate from proj_reading where proj_reading.user_id=1;

assuming user.id=1 friend.id=2

the error is:

OperationalError: Unknown column proj_book.id in 'where clause'

and it happens because the table proj_book is not included in the query. To restate what I said above - if I now do readings2=readings.order_by('book__entrydate') I can see the proper join is set up and the query works.

Ideally I'd just like to figure out what the name of the qs.query function is that looks at two tables and figures out how they are joined by foreign keys, and just call that manually.

Answer 1

you mean:

class SomeModel(models.Model)
    id = models.IntegerField()
    ...


class SomeOtherModel(models.Model)
    otherfield = models.ForeignKey(SomeModel)

qrst = SomeOtherModel.objects.filter(otherfield__id=1)

You can use "__" to create table joins.

EDIT: It wont work because you do not define table join correctly.

myrating='(select rating from proj_reading inner join proj_book on (proj_book.id=proj_reading_id) where proj_reading.user_id=%d and rating in (1,2,3,4,5,6))'%user.id)'

This is a pesdocode and it is not tested.

But, i advice you to use django filters instead of writing sql queries.

read = Reading.objects.filter(book__urlname__icontains="smith", user_id=user.id, rating__in=(1,2,3,4,5,6)).values('rating')

Documentation for more details.

Answer 2

Your generated query:

select ((select rating from proj_reading where user_id=2 and book_id=proj_book.id and rating in (1,2,3,4,5,6)) as 'hisrating', proj_reading.id, proj_reading.user_id, proj_reading.rating, proj_reading.entrydate from proj_reading where proj_reading.user_id=1;

1. The db has no way to understand what does it mean by proj_book, since it is not included in (from tables or inner join).

2. You are getting expected results, when you add order_by, because that order_by query is adding inner join between proj_book and proj_reading.

As far as I understand, if you refer any other column in Book, not just order_by, you will get similar results.

• Q1 = Reading.objects.filter(user=user).exclude(Book__name='') # Exclude forces to add JOIN
• Q2 = "Select rating from proj_reading where user_id=%d" % user.id
• Result = Q1.extra("foo":Q2)

This way, at step Q1, you are forcing DJango to add join on Book table, which is not default, unless you access any field of Book table.