Issue: Backref of ManyToManyField does not respect through_model foreign keys
Author: albireox
State: closed
Created: 2018-12-07 19:22:54

I have a database with two tables, `GaiaDR2Source` and `TwoMassPsc`, and a cross-match/relation table, `GaiaDR2TmassBestNeighbour`. In `GaiaDR2Source` I create a many-to-many relation using `GaiaDR2TmassBestNeighbour` as through model. The match happens using `GaiaDR2Source.source_id` (which is the pk) and `TwoMassPsc.designation` (which is *not* the pk). My models look like it (I have removed everything that is not relevant):

```python
class TwoMassPsc(SDSS5dbModel):

    designation = CharField(null=True, unique=True)

    class Meta:
        table_name = 'twomass_psc'
        schema = 'catalogdb'


GaiaDR2TmassBestNeighbourDeferred = DeferredThroughModel()


class GaiaDR2Source(SDSS5dbModel):

    source_id = BigAutoField(primary_key=True)


    tmass_best_sources = ManyToManyField(TwoMassPsc,
                                         through_model=GaiaDR2TmassBestNeighbourDeferred,
                                         backref='gaia_best_sources')

    class Meta:
        table_name = 'gaia_dr2_source'
        schema = 'catalogdb'


class GaiaDR2TmassBestNeighbour(SDSS5dbModel):

    source_id = BigAutoField(primary_key=True)
    original_ext_source_id = CharField(null=True)

    gaia_source = ForeignKeyField(GaiaDR2Source,
                                  column_name='source_id',
                                  backref='tmass_best_neighbour')
    tmass_source = ForeignKeyField(TwoMassPsc,
                                   column_name='original_ext_source_id',
                                   field='designation',
                                   backref='gaia_best_neighbour')

    class Meta:
        table_name = 'gaiadr2_tmass_best_neighbour'
        schema = 'catalogdb'


GaiaDR2TmassBestNeighbourDeferred.set_model(GaiaDR2TmassBestNeighbour)
```

The foreign keys from `GaiaDR2TmassBestNeighbour` to either other table work fine, as it does going from `GaiaDR2Source` to `TwoMassPsc`, e.g.:

```python
>>> gg = GaiaDR2Source.get()
>>> gg.tmass_best_sources[0]
<TwoMassPsc: 264047591>
```

but the backers fails

```python
>>> tt = TwoMassPsc.get()
>>> tt.gaia_best_sources[0]
IndexError: list index out of range
```

Looking at the raw SQL that it tries to run I see that it's trying to use join the through model using the primary key of `TwoMassPsc` instead of the `designation` field, as instructed in the foreign key.

Not sure if it's the correct solution but it seems that making

```python
                return (ManyToManyQuery(instance, self, self.rel_model)
                        .join(self.through_model)
                        .join(self.model)
                        .where(self.src_fk == getattr(instance, self.src_fk.rel_field.name)))
```

in `ManyToManyFieldAccessor` fixes the problem.

As you noted, the issue is that the field "designation" on `TwoMassPsc` is not the primary key:

```python
    tmass_source = ForeignKeyField(TwoMassPsc,
                                   column_name='original_ext_source_id',
                                   field='designation',
                                   backref='gaia_best_neighbour')
```

I'll repeat the strong admonition from the documentation here, just in case someone comes across this in the future:

> Peewee provides a field for representing many-to-many relationships, much like Django does. This feature was added due to many requests from users, but I strongly advocate against using it, since it conflates the idea of a field with a junction table and *hidden joins*. It’s just a nasty hack to provide convenient accessors.

The emphasis (added) is that for all but the simplest use-cases, you're going to have a better time just using a junction model.

Again, I do think that this is a legit bug and I will patch it, but I'd suggest that unless you're doing very basic/plain many-to-many queries, you should always prefer the explicit junction table.