Django ManyToManyField ordering using through

Question

Here is a snippet of how my models are setup:

class Profile(models.Model):     
    name = models.CharField(max_length=32)

    accout = models.ManyToManyField(
        'project.Account',
        through='project.ProfileAccount'
    )

    def __unicode__(self)
        return self.name

class Accounts(models.Model):
    name = models.CharField(max_length=32)
    type = models.CharField(max_length=32)

    class Meta:
        ordering = ('name',)

    def __unicode__(self)
        return self.name

class ProfileAccounts(models.Model):
    profile = models.ForeignKey('project.Profile')
    account = models.ForeignKey('project.Accounts')

    number = models.PositiveIntegerField()

    class Meta:
        ordering = ('number',)

Then when I access the Accounts, how can I sort by 'number' in the intermediary ProfileAccounts model, rather than the default 'name' in the Accounts Model?:

for acct_number in self.profile.accounts.all():
    pass

This does not work, but is the gist of how I want it to access this data:

for scanline_field in self.scanline_profile.fields.all().order_by('number'):
    pass

Werewolf

Answer 1

I just came through this.

class Profile(models.Model):     
    accounts = models.ManyToManyField('project.Account',
                                      through='project.ProfileAccount')

    def get_accounts(self):
        return self.accounts.order_by('link_to_profile')


class Account(models.Model):
    name = models.CharField(max_length=32)


class ProfileAccount(models.Model):
    profile = models.ForeignKey('project.Profile')
    account = models.ForeignKey('project.Account', related_name='link_to_profile')
    number = models.PositiveIntegerField()

    class Meta:
        ordering = ('number',)

I removed the fields which were off-topic except of Account.name. That's the shortest solution I've found, no idea if it was possible to use in 2010, but it certainly is now.

Manifestation

In the case of OP's question, he would simply rewrite for acct_number in self.profile.accounts.all() as for acct_number in self.profile.accounts.all().order_by('link_to_profile'). In case that was not clear. You don't have to add a get_accounts method to the Profile model. Django always creates an outer join on the many-to-many table, so specifying a order_by based on the field just works. – Albemarle

How the hell does this work? Isn't account.link_to_profile a related manager here that could have a number of different ProfillAccount instances tied to it? – Hideout

Answer 2

That worked for me:

Profile.objects.account.order_by('profileaccounts')

Quartas

Elegant. Exactly what I needed. Thanks. – Revive

Answer 3

a ManyToManyField manager allows you to select/filter data from the related model directly, without using any model connection of the through model on your django level...

Likewise,

if you try:

pr = Profile.objects.get(pk=1)
pr.account.all()

returns you all account related to that profile. As you see, there exists no direct relation to the through model ProfileAccount, so you can not use the M2M relation at this point... You must use a reverse relation to the through model and filter the results...

pr = Profile.objects.get(pk=1)
pr.profileaccount_set.order_by('number')

will give you an ordered queryset, but, in this case, what you have in queryset is profileaccount objects, not account objects... So you have to use another django level relation to go to each related account with:

pr = Profile.objects.get(pk=1)
for pacc in pr.profileaccount_set.order_by('number'):
    pacc.account

Does

Answer 4

Add the related name to ProfileAccounts and then change the ordering in Accounts with that 'related_name__number'. Note two underscores between related_name and number. See below:

class Accounts(models.Model):
    .
    .
    .
    class Meta:
        ordering = ('profile_accounts__number',)


class ProfileAccounts(models.Model):
    .
    .
    .
    account = models.ForeignKey('project.Accounts', related_name='profile_accounts')

    number = models.PositiveIntegerField()

    class Meta:
        ordering = ('number',)

Kolyma

won't putting a relationship spanning order clause in Accounts require that every query on Account to also fetch the related profile accounts? This is probably not what you want to do. – Cytologist

Could just set ordering=('profileaccounts_set__number') if you don't want to use a related_name. Personal preference? This solution worked the best for my situation. YMMV. – Hailee

Answer 5

There's a typo in the Profile (it's "accout" when I think you mean "account"), but more importantly you have your singular/plural forms mixed up in the model.

In Django the practice is generally to name your classes singular, and your ManyToManyField names plural. So:

class Profile(models.Model):     
    name = models.CharField(max_length=32)

    accounts = models.ManyToManyField(
        'Account',
        through='ProfileAccount'
    )

    def __unicode__(self)
        return self.name

class Account(models.Model):
    name = models.CharField(max_length=32)
    type = models.CharField(max_length=32)

    class Meta:
        ordering = ('name',)

    def __unicode__(self)
        return self.name

class ProfileAccount(models.Model):
    profile = models.ForeignKey(Profile)
    account = models.ForeignKey(Account)

    number = models.PositiveIntegerField()

    class Meta:
        ordering = ('number',)

I'm a little confused at what you're trying to do with this model, but if you make those changes, then for acct_number in self.profile.accounts.all().order_by('number'): should work. Assuming no other issues.

Elysia

self.profile.accounts.all().order_by('number') does not work since 'number' is not a field on Account. – Yen

Yes, that wouldn't work, would it? If I understand the question correctly (I'm looking at it again now after one and a half years away, so I may be confused), the easiest way to do it would be to iterate over the ProfileAccount objects associated with Profile, instead of the Account objects, and then just take the Account associated with each ProfileAccount object. But alternately, I think there is syntax to sort through a related model -- maybe order_by('profileaccount__number')? – Elysia

I'm no expert, but I couldn't get any order_by to work there. The best I could come up with is using self.profile.profileaccount_set.order_by() but that isn't very pretty and it leaves you with a bunch of ProfileAccounts, not Account instances. – Yen

Answer 6

The easiest solution for this particular problem seems to be

for acct in self.profile.accounts.order_by('profileaccounts'):
    pass

Consume

Answer 7

I have this on a number of my models, but in my opinion (and unlike all the other answers) you shouldn't need to specify the order_by again because, well, it's already specified in the through model. Specifying it again breaks the DRY (don't repeat yourself) principle.

I would use:

qs = profile.profileaccounts_set.all()

This gives the set of ProfileAccounts associated with a profile using your configured ordering. Then:

for pa in qs:
    print(pa.account.name)

For bonus points, you can also speed up the overall process by using select_related in the query.

Fume

If someone is reading this after all this time, this is wrong ManyToMany field with through model doesn't have a "stable" ordering – Khamsin