0

I have the following Django models (greatly simplified here):

class Author:
    name = models.TextField()

class Editor:
    name = model.TextField()

class Publication:
    authors = models.ManyToManyField(Author)
    editors = models.ManyToManyField(Editor)

A publication can have either authors or editors, but not both. I would like to order a the list of publications by the unification of authors and editors, alphabetically sorted. In other words, I would like to order by a virtual field, let's call it creators which is the concatenation of authors and editors.

The query:

Publication.objects.all().order_by('authors__name', 'editors__name')

... will clump together the publications that have NO authors, and within that group sort according to editors, which is NOT what I want. Effectively, it should use whatever of authors or editors is available for a publication and sort by that.

This ordering has to happen at the database level, the result sets can be huge.

Charl Botha
  • 4,373
  • 34
  • 53

3 Answers3

0

One approach is creating a method called creators:

class Publication:
    authors = models.ManyToManyField(Author)
    editors = models.ManyToManyField(Editor)

    def creators(self):
        return self.authors + self.creators

But the disadvantage is that you cannot perform the sorting at the database-level but at the Python level:

sorted(Publication.objects.all(), key=lambda k: " ".join(p.name for p in k))
arocks
  • 2,862
  • 1
  • 12
  • 20
  • The result sets are huge, so this ordering has to happen at the DB level. I've added this as a note to my question. :) – Charl Botha Jan 21 '14 at 14:26
  • Why not add an additional column with the sorted list of names while saving? You will have to anyway sort the names while performing the join. – arocks Jan 21 '14 at 14:37
  • M2M fields remember? The list of M2M relations can change without the save() ever being called. – Charl Botha Jan 21 '14 at 14:41
  • True. But you can still use a `through` model and signals [mentioned in this answer](http://stackoverflow.com/questions/1958540/django-manytomany-signals) – arocks Jan 21 '14 at 15:02
0

Can I just throw this out there?

You may want Authors and Editors to inherit from Contributors. Since both models will have stuff like first and last names, address, etc.

Rob L
  • 3,634
  • 2
  • 19
  • 38
  • This is a severely simplified example. That inheritance would not change the problem, and would only add unnecessary extra lines to the example. – Charl Botha Jan 21 '14 at 14:48
  • (that being said, actually merging the two fields into one would be a solution, but is in this case not suitable due to other constraints. :) – Charl Botha Jan 21 '14 at 14:51
  • Hmm. You could do it with raw sql, then. But that's a last resort, isn't it. – Rob L Jan 21 '14 at 14:53
  • Well, using .extra() with `select` is what I'm looking at now to concatenate the two lists of joined M2M values. Tips appreciated. :) – Charl Botha Jan 21 '14 at 14:57
0

To sort by whichever field has a value defined, you'll need to create a combined field somewhere the QuerySet can access, either at the model level, using an custom Aggregate, or using an extra clause.

If you don't need to be totally DB agnostic extra is probably the easiest to implement:

qs = Publication.objects.extra({
   select={'contributor': "COALESCE(author.name,editor.name)"}
})
qs.extra(order_by = ['contributor'])

https://docs.djangoproject.com/en/1.6/ref/models/querysets/#django.db.models.query.QuerySet.extra

If you do need to be DB independent, you should look at using one of the implementations of concat as an aggregate. For example,

http://harkablog.com/inside-the-django-orm-aggregates.html

aaronelliotross
  • 1
  • 1
  • I would love to be able to use something like your `extra()` suggestion, but I've not been able to make it work with my M2M fields. Remember that there can be multiple authors associated with every publication, same for the editors. Any ideas? – Charl Botha Jan 21 '14 at 15:16