What is the best means to achieve efficient many-to-many scanning with Django models?

Question

I have a many to many relationship in Django similar as this:

class Account ( models.Model ):
  name = models.CharField( max_length = 30 )
  user = models.ForeignKey( User, null = True, blank = True )

class Publisher ( models.Model ):
  publisherID = models.BigIntegerField( )
  name = models.CharField( max_length = 30 )
  lastRequestedId = models.BigIntegerField( )

class Subscription ( models.Model ):
  user = models.ForeignKey( Account )
  twitterPublisher = models.ForeignKey( Publisher )

A list of publisherIDs for an account is a subscription list. Every so often I would like to scan through every account's subscriptions with such a list of publisherIDs and update subscriptions, adding new subscriptions for publisherIDs not found in the accont's subscriptions and removing subscriptions not found in the list of the accounts publisherIDs. This list is obtained elsewhere.

What is the best way to do this? Subscription.objects.all( ) and then loop through each (could be lots!) and keep track of subscriptions and users, eventually updating once I understand the current state of the database vs the new information? Or do I do loop through accounts and use filter( ) on the Subscription object? Will that lead to many mysql queries?

The list of publisherIDs I get look like this:

[15446531, 15503880, 4909151, 105263800, 21457289, 14293310, 807095, 14511951,
20032100, 20214495, 15234407, 5988062, 16120265, 50393960, 33933259, 10059852, 652193, 
88992966, 43166813, 65682198, 14581921, 12044602, 752673, 14414230, 18994120, 17180567, 
17633960, 1468401, 71876190, 23969777, 63490206, 2425151, 14456474, 18396070, 62205298, 
11348282, 62581962, 15234886, 30313925, 15166438, 17525171, 15007149, 10760422, 22677790,
14874480, 22093112, 24741685, 10454572, 19015586, 14572071, 37492156, 6253282, 37996645, 
18725061, 15983724, 8294212, 24431892, 14589771, 773287, 20772763, 22636391, 816653, 
19394188, 49793, 1688, 15813140, 20536157, 202003, 685513, 1000591, 17220934, 972651, 
5668942, 1692901, 15913]

CLARIFYING: I have a list of ids per account. I also want to remove subscriptions if the user has a subscription if it's not in the list of publisherids for that user.

Answer 1

First of all, for convenience, i would add a django's M2M field with intermediary table or without it, depends on if you need to have some additional data on subscription:

class Account ( models.Model ):
    name = models.CharField( max_length = 30 )
    user = models.ForeignKey( User, null = True, blank = True )
    publishers = models.ManyToManyField(Publisher, through='Subscription')

Then you can use filter to exclude accounts that have all the publisherIDs

 Account.objects.exclude(publishers__publisherID__in=[1,2])

In the end all you have to do is attach new publishers to filtered accounts.

Answer 2

dmishe got me in the right direction but maybe what I need to do is what is being done in this answer:

http://stackoverflow.com/questions/1388137/querying-django-manytomany

Publication.objects.filter(subscription__group=account_instance)

Maybe?