Looking for a specific database query

Using Django
1

I am looking for a specific ORM query that I have yet to build. Here is the description:

I have three models: Topic, Entry and User. Each entry have a ForeignKey to both Topic and User (Author).

I want to fetch Topics on condition that they have had entries created by a user instance in 24 hour period AND there have been other entries after the user’s (last entry). I also want to annotate the count of these entries.

Reference raw sql solution

In the comments, there is a link to an orm solution that is dreadfully slow. Those solutions include other irrevelant details, such as user status, but you can just ignore these. You can also find model reference in the same repo.

I will greatly appreciate your support. Thank you.

2

Hi - I can’t point you to the exact query but it sounds like you might want window functions. It’s probably easiest to learn how they work in SQL before attempting to build the query in the ORM.

3

@adamchainz Thanks to your suggestion, I was able to narrow down the problem/come up with different situation. Here is my attempted solution:

exclusions = Q(author__in=user.blocked.all()) | Q(author=user)

if not user.is_novice:
    exclusions |= Q(author__is_novice=True)

following_entries = (
    Entry.objects_published.order_by()
    .exclude(exclusions)
    .filter(topic=OuterRef("topic_id"), date_created__gte=OuterRef("date_created"))
    .annotate(count=Window(expression=Count("pk")))
    .values("count")
)

new_entries = (
    Entry.objects_published.filter(author=user, date_created__gte=time_threshold(hours=120))
    .annotate(count=Subquery(following_entries))
    .filter(count__gt=0)
    .select_related("topic")
    .only("topic__title", "topic__slug")
    .order_by("-date_created")
)

In the second query, entries are selected from 5-day period, but these might include multiple entries from the same topic. So now, I need to select entries provided that they are the last ones posted in that topic by the user.

Which could be achieved using this query:

Entry.objects_published.order_by()
.values("topic__title", "topic__slug")
.filter(author=user, date_created__gte=time_threshold(hours=120))
.alias(d=Max("date_created"))

But I couldn’t quite merge these queries. As replacing this query in new_entries raises:

AttributeError: 'NoneType' object has no attribute 'get_source_expressions'