1

I'm having trouble understanding why a Queryset is being returned in the order it is. We have authors listed for articles and those get stored in a ManyToMany table called: articlepage_authors

We need to be able to pick, on an article by article basis, what order they are returned and displayed in.

For example, article with id 44918 has authors 13752 (‘Lee Bodding’) and 13751 (‘Mark Lee’).

I called these in the shell which returns :

Out[6]: <QuerySet [<User: Mark Lee (MarkLee@uss778.net)>, <User: Lee Bodding (LeeBodding@uss778.net)>]>

Calling this in postgres: SELECT * FROM articlepage_authors;

shows that user Lee Bodding id=13752 is stored first in the table.

id  | articlepage_id | user_id
-----+----------------+---------
   1 |          44508 |    7781
   2 |          44508 |    7775
   3 |          44514 |   17240
….
 465 |          44916 |   17171
 468 |          44918 |   13752
 469 |          44918 |   13751

No matter what I try e.g. deleting the authors, adding ‘Lee Bodding’, saving the article, then adding ‘Mark Lee’, and vice versa – I can still only get a query set which returns ‘Mark Lee’ first.

I am not sure how else to debug this.

One solution would be to add another field which defines the order of authors, but I’d like to understand what’s going on here first. Something seems to be defining the order already, and it’d be better to manage that.

KindOfGuy
  • 3,081
  • 5
  • 31
  • 47
  • It might be helpful to add the python code to this question. It might also be useful to add a few more names to the article (or a different one) to see if it's doing some sort of sorting (it's kind of hard to determine a pattern with just two items). – Katharine Osborne Apr 16 '19 at 17:11

1 Answers1

2

You can add an order_by to your queryset to make records appear in the order that you would like. Warning: for query optimization you may need to create an index on that field for performance reasons depending on the database:

By default, results returned by a QuerySet are ordered by the ordering tuple given by the ordering option in the model’s Meta. You can override this on a per-QuerySet basis by using the order_by method. Example:

Entry.objects.filter(pub_date__year=2005).order_by('-pub_date', 'headline')

The result above will be ordered by pub_date descending, then by headline ascending. The negative sign in front of "-pub_date" indicates descending order. Ascending order is implied.

You pair that with an extra to order by the many-to-many ID:

.extra(select={
    'creation_seq': 'articlepage_authors.id'
}).order_by("creation_seq")

If you're using django > 1.10, you can just use the field directly without the extra:

.order_by('articlepage_authors.id')
2ps
  • 15,099
  • 2
  • 27
  • 47
  • This seemed to work before but now I get the following error. Any ideas? ```File "/root/.pyenv/versions/3.7.2/lib/python3.7/site-packages/django/db/backends/utils.py", line 85, in _execute return self.cursor.execute(sql, params) django.db.utils.ProgrammingError: missing FROM-clause entry for table "articlepage_authors" LINE 1: ...authors"."legacycontentpage_id" = 5831 ORDER BY ("articlep... ^``` – KindOfGuy May 10 '19 at 17:01
  • That's what it says all the way to the end. SO comments are bad for code snippets – too short, no formatting. But it's basically this: `django.db.utils.ProgrammingError: missing FROM-clause entry for table "articlepage_authors" LINE 1: ...authors"."legacycontentpage_id" = 5831 ORDER BY ("articlep...` Any ideas? – KindOfGuy May 13 '19 at 09:55
  • I have worked it out now. The error emerged because this queryset was being used in a different context at that point. Your answer remains correct. Thanks again! – KindOfGuy May 13 '19 at 15:28