I have a model that stores the cover images for different magazines (publications).
In a simplified version it looks like this
class Publication(models.Model):
name = models.CharField(max_length=100)
website = models.CharField(max_length=100)
class PublicationIssue(models.Model):
publication = models.ForeignKey(Publication, null=False)
issue_number = models.IntegerField(null=False)
cover_image = models.CharField(max_length=100)
In SQL I can execute something like this:
select
publication_id,
max(issue_number) current_issue,
cover_image
from conditionalsum_publicationissue
group by publication_id
This looks for the max issue number for a publication and easily gives me the current issue number and the associated cover image. The output looks something like this:
publication_id current_issue cover_image
1 12 ae43fb33.jpg
2 26 445fbb45.jpg
3 213 db8489e3.jpg
I know I can use rawsql in a Django query, but I wanted to see if there are solutions that avoid this.
It's easy enough to get the current issue:
PublicationIssue.objects. \
values('publication_id'). \
annotate(current_issue=Max('issue_number'))
This returns the publication_id with the current_issue. But this look like a dead end as far as adding the cover_image.
Adding 'cover_image' to the values clause does not work and any further annotation will similarly expand the queryset in ways incorrect for my purposes. I have attempted going with a subquery, but that is getting fairly long and complicated for a relatively simple SQL statement.
In summary: my question is how do I translate
select
publication_id,
max(issue_number) current_issue,
cover_image
from conditionalsum_publicationissue
group by publication_id
- with the challenge being the cover_image field - into a Django query without using rawsql?
