CODEWITHSUNDEEP
django
bozdoz
bozdoz

Reputation: 12860

How to retrieve values from Django ForeignKey -> ManyToMany fields?

I have a model (Realtor) with a ForeignKey field (BillingTier), which has a ManyToManyField (BillingPlan). For each logged in realtor, I want to check if they have a billing plan that offers automatic feedback on their listings. Here's what the models look like, briefly:

class Realtor(models.Model):
  user = models.OneToOneField(User)
  billing_tier = models.ForeignKey(BillingTier, blank=True, null=True, default=None)

class BillingTier(models.Model):
  plans = models.ManyToManyField(BillingPlan)

class BillingPlan(models.Model):
  automatic_feedback = models.BooleanField(default=False)

I have a permissions helper that checks the user permissions on each page load, and denies access to certain pages. I want to deny the feedback page if they don't have the automatic feedback feature in their billing plan. However, I'm not really sure the best way to get this information. Here's what I've researched and found so far, but it seems inefficient to be querying on each page load:

def isPermitted(user, url):
  premium = [t[0] for t in user.realtor.billing_tier.plans.values_list('automatic_feedback') if t[0]]

I saw some solutions which involved using filter (ManyToMany field values from queryset), but I'm equally unsure of using the query for each page load. I would have to get the billing tier id from the realtor: bt_id = user.realtor.billing_tier.id and then query the model like so: 

BillingTier.objects.filter(id = bt_id).filter(plans__automatic_feedback=True).distinct()

I think the second option reads nicer, but I think the first would perform better because I wouldn't have to import and query the BillingTier model.

Is there a better option, or are these two the best I can hope for? Also, which would be more efficient for every page load?

Upvotes: 1

Views: 1572

Answers (1)

class stacker
class stacker

Reputation: 5347

As per the OP's invitation, here's an answer.

The core question is how to define an efficient permission check based on a highly relational data model.

The first variant involves building a Python list from evaluating a Django query set. The suspicion must certainly be that it imposes unnecessary computations on the Python interpreter. Although it's not clear whether that's tolerable if at the same time it allows for a less complex database query (a tradeoff which is hard to assess), the underlying DB query is not exactly simple.

The second approach involves fetching additional 1:1 data through relational lookups and then checking if there is any record fulfilling access criteria in a different, 1:n relation.

Let's have a look at them.

  • bt_id = user.realtor.billing_tier.id: This is required to get the hook for the following 1:n query. It is indeed highly inefficient in itself. It can be optimized in two ways.
    • As per Django: Access Foreign Keys Directly, it can be written as bt_id = user.realtor.billing_tier_id because the id is of course present in billing_tier and needs not be found via a relational operation.
    • Assuming that the page in itself would only load a user object, Django can be told to fetch and cache relational data along with that via select_related. So if the page does not only fetch the user object but the required billing_tier_id as well, we have saved one additional DB hit.
  • BillingTier.objects.filter(id = bt_id).filter(plans__automatic_feedback=True).distinct() can be optimized using Django's exists because that will redurce efforts both in the database and regarding data traffic between the database and Python.
  • Maybe even Django's prefetch_related can be used to combine the 1:1 and 1:n queries into a single query, but it's much more difficult to judge whether that pays. Could be worth a try.

In any case, it's worth installing a gem called Django Debug Toolbar which will allow you to analyze how much time your implementation spends on database queries.

Upvotes: 2

Related Questions