Issue: Join.metadata doesn't seem to respect Join.on enough
Author: maaaks
State: closed
Created: 2017-11-01 10:16:05

Here's what I found in the `Join` code while debugging:

```python
fk_field, is_backref = self.get_foreign_key(src, dest, self.on)
if fk_field is None and self.on is not None:
    fk_field, is_backref = self.get_foreign_key(src, dest)
```

I don't understand why Peewee here decides to choose __any__ foreign key pointing to `dest`, even if I clearly asked it to use a certain `self.on` for it. It feels like a very non-obvious and, I'd say, useless fallback behavior which not so much fixes things as breaks them.

Here's my situation, simplified. I have a `Rat` object which can remember which `Cat` caught this rat though `Rat.cat`. Either `Rat` and `Cat` object (or both) can have an owner: `Rat.owner`, `Cat.owner`. After a rat is being caught by a cat, the cat's owner becomes indirectly the rat's owner, too.

So, I want to list all the rats with their owners, either direct (`rat.owner`) or indirect (`rat.cat.owner`). I do this using join expression with alias `_real_owner`:

```python
Rat.select() \
.join(Cat) \
.switch(Rat) \
.join(Person, JOIN_LEFT_OUTER,
    on=(fn.coalesce(Rat.owner, Cat.owner) == Person.id).alias('_real_owner'))
```

But when it comes to the piece of code I showed in the beginning, Peewee seems to look at it this way:

— So, do we have a foreign key Rat→Person with name `_real_owner`?
— No, we don't.
— Ok, nevermind, do we have **any** foreign key Rat→Person?
— Yes, we do: there is a foreign key named `owner`. It isn't what we were asked for, but it's Rat→Person.
— Let's use it.

As a result of this, `Join.metadata.foreign_key.name` is now `'owner'`, and later it causes `ModelQueryResultWrapper.follow_joins()` to try and get `inst._data['owner']` (which is `None`) and quit trying to fill `_real_owner` in the resulting row:

```python
has_fk = True
if check_null:
    if fk_present:
        has_fk = inst._data.get(metadata.foreign_key.name)
    elif pk_present:
        has_fk = joined_inst._data.get(metadata.primary_key.name)

if not has_fk:
    continue

...
setattr(inst, metadata.attr, joined_inst)
```

I believe that either the first or the last piece of code does something wrong. Existence of `Rat.owner` should not affect ability to make custom joins of `Person` in the query, **or**, even if it is useful to have `metadata.foreign_key` for some other cases, its name should not affect the decision about whether or not to fill `Rat._real_owner` (because `metadata.attr == '_real_owner'`).

Before I dig into this specific issue, do you mind giving things a try with 3.0a and letting me know if it resolves the issue?

This appears to be working correctly in the 3.0a branch:

```python
from peewee import *

db = SqliteDatabase(':memory:')

class BaseModel(Model):
    class Meta:
        database = db

class Owner(BaseModel):
    name = CharField()

class Cat(BaseModel):
    owner = ForeignKeyField(Owner)
    name = CharField()

class Rat(BaseModel):
    owner = ForeignKeyField(Owner, null=True)
    caught_by = ForeignKeyField(Cat, null=True)
    name = CharField()

db.create_tables([Owner, Cat, Rat])

o1 = Owner.create(name='o1')
o2 = Owner.create(name='o2')

c11 = Cat.create(name='c1-1', owner=o1)
c12 = Cat.create(name='c1-2', owner=o1)
c21 = Cat.create(name='c2-1', owner=o2)

r11 = Rat.create(name='r1-1', caught_by=c11)
r12 = Rat.create(name='r1-2', caught_by=c12)
r1x = Rat.create(name='r1-x', owner=o2)

r21 = Rat.create(name='r2-1', caught_by=c21, owner=o2)
r22 = Rat.create(name='r2-2', caught_by=c21)

query = (Rat
         .select(Rat, Owner)
         .join(Cat, JOIN.LEFT_OUTER)
         .switch(Rat)
         .join(Owner, JOIN.LEFT_OUTER, on=(
             fn.COALESCE(Rat.owner, Cat.owner) == Owner.id).alias('_real_owner')))
for row in query:
    print row.name, row._real_owner.__data__
```

PRINTS:

```
r1-1 {'id': 1, 'name': u'o1'}
r1-2 {'id': 1, 'name': u'o1'}
r1-x {'id': 2, 'name': u'o2'}
r2-1 {'id': 2, 'name': u'o2'}
r2-2 {'id': 2, 'name': u'o2'}
```