Efficient alternative at Django filter by property

Question

I know that filtering by property is not possible with Django, as filtering is done at database level and properties live in Python code. However, I have the following scenario:

In one hand, I have the model RegisteredUser on the other hand Subscription. A user can have multiple subscriptions, a subscription is from one user and a user has one or none active subscriptions.

To implement this, I have a foreign key from Subscription to RegisteredUser and a property subscription at RegisteredUser that points to the active one (latest created subscription for that user) or none if he hasn't any subscriptions.

Which would be the most efficent way to filter users that have subscription "platinum", "gold", "silver"...? I could do a "fetch all subscriptions" and then iterate over them to check each one for a match. But it would be really expensive and if I have to do the same process for each kind of subscription type, then cost would be s * u (where s is the number of different subscriptions and u is the number of users).

Any help will be appreciated. Thanks in advance!

UPDATE:

When I first explained the problem, I didn't include all the models related to simplify a litte. But as you are asking me for the models and some of you haven't understood me (perhaps I wasn't clear enough) here you have the code. I've simplified the models and stripped out code that is not important now.

What do I have here? A RegisteredUser can have many subscriptions (because he may change it as many times as he wants), and a subscription is from just one user. The user has only one current subscription, which is the latest one and is returned by the property subscription. Subscription is attached with Membership and this is the model whose slug can be: platinum, gold, silver, etc.

What do I need? I need to lookup Content whose author has a specific kind of membership. If the property approach worked, I'd have done it like this:

Content.objects.filter(author__id__in=RegisteredUser.objects.filter(
    subscription__membership__slug="gold"))

But I can't do this because properties can't be used when filtering!

I thought that I could solve the problem converting the "virtual" relation created by the property into a real ForeignKey, but this may cause side effects, as I should update it manually each time a user changes its subscription and now it's automatic! Any better ideas?

Thanks so much!

class RegisteredUser(AbstractUser):
    birthdate = models.DateField(_("Birthdate"), blank=True, null=True)
    phone_number = models.CharField(_("Phone number"), max_length=9, blank=True, default="")

    @property
    def subscription(self):
        try:
            return self.subscriptions_set.filter(active=True).order_by("-date_joined",
                "-created")[0]
        except IndexError:
            return None


class Subscription(models.Model):
    date_joined = models.DateField(_("Date joined"), default=timezone.now)
    date_canceled = models.DateField(_("Date canceled"), blank=True, null=True)
    subscriber = models.ForeignKey(AUTH_USER_MODEL, verbose_name=_("Subscriber"),
        related_name="subscriptions_set")
    membership = models.ForeignKey(Membership, verbose_name=_("Membership"),
        related_name="subscriptions_set")
    created = models.DateTimeField(_("Created"), auto_now_add=True)
    last_updated = models.DateTimeField(_("Last updated"), auto_now=True)
    active = models.BooleanField(_("Active"), default=True)


class Membership(models.Model):
    name = models.CharField(_("Name"), max_length=15)
    slug = models.SlugField(_("Slug"), max_length=15, unique=True)
    price = models.DecimalField(_("Price"), max_digits=6, decimal_places=2)
    recurring = models.BooleanField(_("Recurring"))
    duration = models.PositiveSmallIntegerField(_("Duration months"))


class Content(models.Model):
    author = models.ForeignKey(AUTH_USER_MODEL, verbose_name=_("Author"),
        related_name="contents_set")
    title = models.CharField(_("Title"), max_length=50)
    slug = models.SlugField(_("Slug"), max_length=70, unique=True)
    content = RichTextField(_("Content"))
    date = models.DateField(_("Date"), default=timezone.now)
    published = models.BooleanField(_("Published"))

tried https://docs.djangoproject.com/en/dev/ref/models/querysets/ ? — Hamish, Jul 23 '13 at 00:39
@Hamish I don't catch what you mean... What exactly am I supposed to look for? — Caumons, Jul 23 '13 at 00:41
Raw query comes in handy now? And actually could you post code of your models? — Hieu Nguyen, Jul 23 '13 at 00:50
I know this is old, but, for those interested, the "platinum", "gold", "silver", ..., part sounds a lot like the example in the [Conditional Expressions docs](https://docs.djangoproject.com/en/2.1/ref/models/conditional-expressions/#the-conditional-expression-classes). — djvg, Dec 07 '18 at 21:25

score 1 · Accepted Answer · answered Aug 05 '13 at 22:40

Finally, to solve the problem I replaced the subscription property by a real foreign key and added a signal to attach the RegisteredUser with the created subscription.

Foreign key:

subscription = models.ForeignKey(Subscription, verbose_name=_("Subscription"),
    related_name='subscriber_set', blank=True, null=True)

Signal:

@receiver(post_save, sender=Subscription)
def signal_subscription_post_save(sender, instance, created, **kwargs):
    if created:
        instance.subscriber.subscription = instance
        instance.subscriber.save()

Leandro · Answer 2 · 2013-07-23T12:27:39.553

I think you model are something like:

KIND = (("p", "platinum"), ("g","gold"), ("s","silver"),)

class RegisteredUser(models.Model):
    # Fields....

class Subscription(models.Model):
    kind = models.CharField(choices=KIND, max_len=2)
    user = models.ForeignKey(RegisteredUser, related_name="subscriptions")

Now, you can do something like that:

gold_users = RegisteredUser.objects.filter(subscriptions_kind="g")
silver_users = RegisteredUser.objects.filter(subscriptions_kind="s")
platinum_users = RegisteredUser.objects.filter(subscriptions_kind="p")

Adapt it to your models

Hope helps

EDIT

Now, With your models, I think you want something like:

content_of_golden_users = Content.objects.filter(author__subscriptions_set__membership__slug="golden")

content_of_silver_users = Content.objects.filter(author__subscriptions_set__membership__slug="silver")

content_of_platinum_users = Content.objects.filter(author__subscriptions_set__membership__slug="platinum")

or `gold_silver_platinum = RegisteredUser.objects.filter(subscriptions_kind__in=['g', 's', 'p'])` — Henrik Andersson, Jul 23 '13 at 04:41
This isn't what I need, the problem is far more complex than this trivial scenario. You can have a look at my updated question, which now contains the code. Cheers! — Caumons, Jul 23 '13 at 10:14

Efficient alternative at Django filter by property

2 Answers2