I have a ManyToMany field in my database that I want to convert to a ForeignKey relationship. The relationships are already one-to-many, so there will be no pigeonholing.
The closest question I can find on stackoverflow is this more complicated situation in a different framework/language
My simplified django models are shown below. The fields in question already exist in the database, and we just need to populate the DbLocation.pattern field.
class DbPattern(models.Model):
locations = models.ManyToMany(DbLocation) #trying to remove this
...
class DbLocation(models.Model)
pattern = models.ForeignKey(DbPattern) #and replace it with this
...
My naive solution is a nested for-loop. It works, but looks like it will take days to handle several million records:
patterns = DbPattern.objects.all()
for p in patterns:
locs = p.locations # there ary many locations
for l in locs:
l.pattern = p # each location has exactly 1 pattern.
Is there an easy way to implement this in either Python/Django or PostreSQL that will run fast? I imagine there is a way to do this via queries. I only need to do it once.
Thanks in advance for your help!
I managed to get several orders of magnitude speedup with a few simple tweaks. More speedup could probably be achieved, but this is sufficient for my purposes.
Naive Code (1000 samples take 793 seconds)
patterns = DbPattern.objects.all()
for p in patterns:
locs = p.locations # there ary many locations
for l in locs:
l.pattern = p # each location has exactly 1 pattern.
l.save()
First improvement (1000 sample takes 11 seconds)
patterns = DbPattern.objects.all()
for p in patterns:
locs = p.locations
locs.update(pattern=p)
Second improvement (1000 sample takes 2.5 seconds)
patterns = DbPattern.objects.all()
[p.locations.all().update(pattern=pattern) for p in patterns]