CODEWITHSUNDEEP
pythondjangodjango-orm
Daniel Passaro
Daniel Passaro

Reputation: 21

Django exclude on multiple fields in subquery

Given some code like this:

# coding: utf-8
import datetime

from django.db import models
from django.contrib.auth.models import User
from django.contrib.sites.models import Site


class Premium(models.Model):
    """Access to Premium Features™®."""
    end = models.DateField()
    user = models.ForeignKey(User)
    site = models.ForeignKey(Site)


def get_ending_premiums():
    """Get a queryset of all Premiums for which a user has none following."""
    tomorrow = datetime.date.today() + datetime.timedelta(days=1)
    future_premiums = Premium.objects.filter(end__gt=tomorrow).values('user', 'site')
    return Premium.objects.filter(end=tomorrow).exclude(

        # Would love if something like this actually worked...
        user_and_site__in=future_premiums,
    )

How can I complete get_ending_premiums()? One of the key things is I want Premiums only when there isn't another one that ends later, but on a per-site basis. So if a user has another Premium on groceries.com, the one about to end tomorrow doesn't get returned, but if they don't have another Premium on officesupplies.com, that one does get returned.

(Note the line with with the comments before it doesn’t actually work... that’s the part I need to complete.)

I can work out how to do this outside the ORM but I’d really prefer an ORM solution, as we’re planning on switching database vendors in a few months, so I’m trying to avoid raw SQL as much as possible.

Here’s a test for the behavior I’d like to get:

class PremiumTest(TestCase):

    def test_gets_ending_premiums(self):
        today = date(2020, 6, 5)
        tomorrow = today + timedelta(days=1)
        next_year = today + timedelta(days=366)
        groceries = Site.objects.create(domain='groceries.com')
        catvids = Site.objects.create(domain='catvids.com')
        dave = User.objects.create_user('dave')
        sally = User.objects.create_user('sally')
        Premium.objects.create(user=dave, site=groceries, end=tomorrow)
        Premium.objects.create(user=dave, site=groceries, end=next_year)
        Premium.objects.create(user=dave, site=catvids, end=tomorrow)
        Premium.objects.create(user=sally, site=groceries, end=tomorrow)
        Premium.objects.create(user=sally, site=catvids, end=tomorrow)
        Premium.objects.create(user=sally, site=catvids, end=next_year)

        ending_premiums = get_ending_premiums(today)
        ending = set((p.user, p.site) for p in ending_premiums)

        self.assertNotIn((dave, groceries), ending)
        self.assertIn((dave, catvids), ending)
        self.assertIn((sally, groceries), ending)
        self.assertNotIn((sally, catvids), ending)
        self.assertEqual(2, len(ending_premiums))

Upvotes: 1

Views: 620

Answers (1)

Daniel Passaro
Daniel Passaro

Reputation: 21

I've come up with this... It's got some raw SQL but it still returns a QuerySet with normal QuerySet methods (although it uses the apparently deprecated QuerySet.extra() method)

def get_ending_premiums(day=None):
    """Get a queryset of Premiums for which a user has none following."""
    if day is None:
        day = date.today()
    tomorrow = day + timedelta(days=1)

    ending_premiums = Premium.objects.filter(
        end=tomorrow,
    ).extra(
        where=['NOT EXISTS (SELECT NULL FROM premium_premium child where premium_premium.site_id = site_id AND premium_premium.user_id = user_id AND end > %s )'],
        params=[tomorrow],
    )
    return ending_premiums

Still wondering if there isn’t a better way...

Upvotes: 1

Related Questions