CODEWITHSUNDEEP
djangoorm
alTus
alTus

Reputation: 2205

How can I order by ForeignKey field itself in django without join?

So I have a model MyModel with a ForeignKey field fkfield. And i need to do something like this (simplified):

MyModel.objects.values_list('id', 'fkfield').order_by('fkfield')

For example I want to groupby them further by fkfield so I need my objects to be sorted by this field. And the only thing I will use later is fkfield_id. I mean I dont need any data from related model.

But django performs a join sql query (as described in docs) and uses related model's ordering. The same happens if i explicitly try to order by id:

MyModel.objects.values_list('id', 'fkfield').order_by('fkfield__id')

and I get:

SELECT `mymodel`.`id`, 
       `mymodel`.`fkfield_id` 
  FROM `mymodel` 
       LEFT OUTER JOIN `related_table` 
            ON ( `mymodel`.`fkfield_id` = `related_table`.`id` ) 
ORDER BY 
       `related_table`.`id` ASC

What i really expect is:

SELECT `id`, 
       `fkfield_id` 
  FROM `mymodel` 
ORDER BY 
       `fkfield_id` ASC

But I can't find a way to do it. .order_by('fkfield_id') raises exception that says that there is no such a field.

I managed to get things work using extra but I can't understand why such a simple and obvious behaviour can't be used without hacks. Or maybe i missed smth?

UPDATE: models.py

class Producer(models.Model):
    name = models.CharField(max_length=100)

    class Meta:
        ordering = ('name',)

class Collection(models.Model):
    name = models.CharField(max_length=100)
    producer = models.ForeignKey('Producer')

    class Meta:
        ordering = ('name',)

print Collection.objects.values_list('producer', 'id').order_by('producer').query
>>> SELECT `catalog_collection`.`producer_id`, `catalog_collection`.`id`
>>> FROM `catalog_collection`
>>> INNER JOIN `catalog_producer` ON
>>> (`catalog_collection`.`producer_id` = `catalog_producer`.`id`)
>>> ORDER BY `catalog_producer`.`name` ASC

Upvotes: 1

Views: 302

Answers (1)

Andrey Nelubin
Andrey Nelubin

Reputation: 3294

Try

.order_by('fkfield')

My query is 

Post.objects.values('author', 'id').order_by('author')

as sql:

SELECT "blogs_post"."author_id", 
       "blogs_post"."id" 
FROM "blogs_post" 
ORDER BY "blogs_post"."author_id" ASC

UPDATE

Kind of messy solution:

MyModel.objects.extra(select={'fkfield_id': 'fkfield_id'})\
       .values_list('id', 'fkfield_id')\
       .order_by('fkfield_id')

Upvotes: 1

Related Questions