Как запросить Django ORM по моделям, связанным с последней записью

Здравствуйте, у меня есть проект Django со следующими моделями

class Organization(models.Model):
    name = models.CharField("Name", max_length=128, unique=True)
    description = models.CharField("Description", max_length=256)

class Scholar(models.Model):
    organization = models.ForeignKey(Organization, on_delete=models.CASCADE)
    name = models.CharField("Name", max_length=128)
    title = models.CharField("Title", max_length=256)
    
    def get_latest_snapshot(self):
        return self.snapshotscholar_set.latest('date_crawled')

class SnapshotScholar(models.Model):
    scholar = models.ForeignKey(Scholar, on_delete=models.CASCADE)
    date_crawled = models.DateTimeField("Date Crawled", auto_now_add=True, db_index=True)
    title = models.CharField("Title", max_length=256)

class SnapshotScholarPublication(models.Model):
    snapshot_scholar = models.ForeignKey(SnapshotScholar, on_delete=models.CASCADE)
    title = models.ForeignKey("Title", max_length=256)
    citation_count = models.IntegerField()

Теперь, когда я хочу получить подсчет количества ученых по каждой организации, я могу сделать Organization.objects.annotate(num_scholars=Count('scholar')). Но как мне получить количество публикаций по организации, если я хочу подсчитать только публикации последнего моментального снимка. То есть я хочу, чтобы он был отсортирован по SnapshotScholar.date_crawled, и чтобы в базе данных были все публикации по последнему SnapshotScholar.

По некоторым вопросам здесь мне удалось создать этот SQL -

SELECT COUNT(pub.id) as publications, org.id
FROM (main_snapshotscholarpublication pub, main_snapshotscholar snap, main_scholar scholar, main_organization org)
INNER JOIN (
    SELECT MAX(main_snapshotscholar.date_crawled) as latest_date, main_snapshotscholar.scholar_id as 'id'
    FROM main_snapshotscholar
    GROUP BY main_snapshotscholar.scholar_id
) as latest_snap ON (latest_snap.id = snap.id)
WHERE pub.snapshot_scholar_id = snap.id
AND snap.scholar_id = scholar.id
AND scholar.organization_id = org.id
GROUP BY org.id

Результаты, которые я получаю с помощью этого необработанного SQL, - это погрешность в 1-5% от фактических цифр подсчета. Может ли кто-нибудь помочь мне выяснить, как получить правильные результаты?

Спасибо

Для получения результатов можно использовать Subquery. Например:

from django.db.models import OuterRef, Subquery, Q, F, Count

subquery = SnapshotScholar.objects.filter(scholar__organization=OuterRef('pk')).order_by('date_crawled')
queryset = Organization.objects.annotate(max_scholar_snapshot=Subquery(subquery.values('pk')[0])).annotate(publication_count=Count('scholar__snapshotscholar__snapshotscholarpublication', filter=Q(scholar__snapshotscholar=F('max_scholar_snapshot'))))
queryset.values()

Здесь я сначала аннотирую информацию SnapshotScholar с набором запросов с помощью Subquery. Я упорядочиваю SnapshotScholar с помощью date_crawled, затем присоединяю id последнего из них к набору запросов. Затем я запускаю Count для SnapshotScholarPublication на основе фильтрации значения SnapshotScholar, которое было найдено на последнем шаге.

Я бы изменил ваши модели, создав свойство в модели Organization, чтобы получить его:

class Organization(models.Model):
    name = models.CharField("Name", max_length=128, unique=True)
    description = models.CharField("Description", max_length=256)
    @property
    def last_publications_number(self):
        total = 0
        for s in self.scholar_set.all():
            total += s.get_latest_snapshot_publication_number
        return total

class Scholar(models.Model):
    organization = models.ForeignKey(Organization, on_delete=models.CASCADE)
    name = models.CharField("Name", max_length=128)
    title = models.CharField("Title", max_length=256)
    @property
    def get_latest_snapshot_publication_number(self):
        return self.snapshotscholar_set.order_by('-date_crawled').first().publication_number

class SnapshotScholar(models.Model):
    scholar = models.ForeignKey(Scholar, on_delete=models.CASCADE)
    date_crawled = models.DateTimeField("Date Crawled", auto_now_add=True, db_index=True)
    title = models.CharField("Title", max_length=256)
    @property
    def publication_number(self):
        return snapshotscholarpublication_set.count()

class SnapshotScholarPublication(models.Model):
    snapshot_scholar = models.ForeignKey(SnapshotScholar, on_delete=models.CASCADE)
    title = models.ForeignKey("Title", max_length=256)
    citation_count = models.IntegerField()

Так что вы можете получить доступ через:

publication_numbers = Organization.objects.get(<whatever>).last_publications_number

Развивая ответ @ruddra, попробуйте следующее:

snapshot_subquery = SnapshotScholar.objects.filter(
    scholar__organization=OuterRef('pk'),
).order_by('-date_crawled')

organizations = Organization.objects.annotate(
    latest_snapshot=Subquery(snapshot_subquery.values('pk')[:1]),
).annotate(
    latest_snapshot_publication_count=Count(
        'scholar__snapshotscholar__snapshotscholarpublication', 
        filter=Q(scholar__snapshotscholar=F('latest_snapshot'))
    ),
)

Важно сначала упорядочить ученый снимок по убыванию date_crawled, а затем limiting the subquery rows to one row...[Django-doc] используя нотацию срезов.

Вернуться на верх