1

I have inherited a weird table structure:

class Customer(models.Model):
    account_number = models.CharField()

class Subscription(models.Model):
    account_number = models.CharField()

So the Customer and Subscription models are linked by their account numbers. Each customer can have multiple subscriptions, and each subscription can have multiple customers, but there is no intermediary table; there is no "Account" table. How do I handle this sort of thing? If I have a Subscription queryset, how do I go about getting the corresponding Customer queryset without doing a horribly long query like

customers = Customer.objects.filter(account_number__in=list(subscriptions.values_list('account_number', flat=True)))

I'm trying to avoid this because it would generate a massive query that would take a very long time to execute.

kloddant
  • 1,026
  • 12
  • 19

1 Answers1

1

Because it doesn't have a m2m table, that is probably the best way to do it. However, you can optimize the query a bit to leverage Django to use subquery instead of a list of account numbers in the where statement.

subs = subscriptions.only('account_number').all()
customers = Customer.objects.filter(account_number__in=subs) 

# if you print out the query you should see a subquery in the query where statement
print(customers.query)
Du D.
  • 5,062
  • 2
  • 29
  • 34
  • 1
    Thanks! The subquery that that results in is actually the wrong one though - it selects the subscription id instead of the account id. But the .only('account_number') bit saves some time, so thank you; I had forgotten that that existed. – kloddant Oct 14 '21 at 18:25