CODEWITHSUNDEEP
pythonmysqldjango
wjh
wjh

Reputation: 597

Django order by key in another table that does not exist

Using Django, I have 2 models.

Model1 -> User
          -> ID
          -> Some properties

Model2 -> Activeness
          -> UserId (Foreign Key to User)
          ->rating

This is a simplified case. I am trying to get a list of users sorted by their rating in the activeness table. User.objects.all().order_by('activeness__rating')

This works ok.

However, I have some situations where the activeness object for new users has not been created yet. This will result in those new users appearing at the top of the list obtained. I want them to appear at the bottom of the list instead.

I can't resort in python because I am using paging and retrieving 1 page at a time.

Can anyone advise. Thanks.

Upvotes: 0

Views: 671

Answers (2)

Caspar
Caspar

Reputation: 7759

TLDR: filter out the user objects where the activeness is null, and add them in later.

Long version: given some models like 

class MyUser(models.Model):
    activeness = models.ForeignKey('Activeness', null=True)
    name = models.CharField(max_length=50)

    def __unicode__(self):
        return "{}: {}".format(self.name, self.activeness.rating if self.activeness else "no rating")

class Activeness(models.Model):
    rating = models.FloatField()

and some sample data like this

<MyUser: Bob: 3.62810036125>,
<MyUser: Tim: no rating>,
<MyUser: Jim: 2.41014167534>,
<MyUser: Rod: 1.35651839383>]

you can exclude the users without ratings like this:

>>> MyUser.objects.filter(activeness__rating__isnull=False).order_by('activeness__rating')
[<MyUser: Rod: 1.35651839383>, <MyUser: Jim: 2.41014167534>, <MyUser: Bob: 3.62810036125>]

and then append them to the end, which you can make a bit more efficient by using chain() from itertools:

>>> from itertools import chain
>>> chain(MyUser.objects.filter(activeness__rating__isnull=False).order_by('activeness__rating'), MyUser.objects.filter(activeness__rating__isnull=True))
[<MyUser: Rod: 1.35651839383>,
 <MyUser: Jim: 2.41014167534>,
 <MyUser: Bob: 3.62810036125>,
 <MyUser: Tim: no rating>]

For example, if we have debug on we can see this prevents Query Sets from being pointlessly evaluated.

>>> from django.db import connection
>>> import pprint
>>> connection.queries = []
>>> for u in chain(MyUser.objects.filter(activeness__rating__isnull=False).order_by('activeness__rating').select_related('activeness'), MyUser.objects.filter(activeness__rating__isnull=True)):
>>>     print "At {u} we've done {count} SQL queries".format(u=u, count=len(connection.queries))
At Rod: 1.35651839383 we've done 1 SQL queries
At Jim: 2.41014167534 we've done 1 SQL queries
At Bob: 3.62810036125 we've done 1 SQL queries
At Tim: no rating we've done 2 SQL queries

Of course, you'll still need to do some more work to get it to work with a Django paginator, but that'd be beyond the scope of this question.

Upvotes: 1

Jonathan
Jonathan

Reputation: 2738

Have you tried using annotate? It might help

With annotate, you can do something like this

User.objects.all().annotate(num_rating=Count('activeness__rating')).order_by('num_rating')

I hope it helps

Upvotes: 1

Related Questions