Issue: Child Table's FK is set to None only if Joining to Parent and Selecting Column from Parent
Author: mkmoisen
State: closed
Created: 2022-02-15 17:42:32

A child table's foreign key is set to None when you do both of the following:

1) join it to the parent table 
2) and when you select a non-pk column from the parent table (as opposed to the entire parent table or selecting just the pk column)

If you join to the parent table but select no column from the parent table, the child model's foreign key is set to the correct value.

Here is the setup:

```

from peewee import PostgresqlDatabase, Model, CharField, ForeignKeyField
import logging

logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)

db = PostgresqlDatabase('test', user='test', password='test')

class BaseModel(Model):
    class Meta:
        database = db

class Foo(BaseModel):
    name = CharField(max_length=32, null=False)


class Bar(BaseModel):
    name = CharField(max_length=32, null=False)
    foo = ForeignKeyField(Foo, null=False)

db.create_tables([Foo, Bar])

foo = Foo.create(name='a')
bar = Bar.create(name='b', foo=foo)

```

Here is the apparent bug:

```
bar = list(
    Bar.select(
        Bar.foo_id,
        Foo.name,  # select just 1 column from Foo
    ).join(
        Foo, on=(Bar.foo_id == Foo.id)
    )
)[0]

assert bar.foo_id is None
```

bar.foo_id is None. 

It is only None if you add the `Foo.name` to the select list. If you remove `Foo.name` from the select list, the proper non-None value is returned. Edit: If you add `Foo.id` or just `Foo` to the select list, then bar.foo_id is correctly populated. Moreover if you don't add `Bar.foo_id` and just include `Foo.id` into the select, then the resulting bar.foo_id is also correctly populated

The query that is being generated correctly selects `foo_id` from foo, however it is not being added to the object:

    ('SELECT "t1"."foo_id", "t2"."name" FROM "bar" AS "t1" INNER JOIN "foo" AS "t2" ON ("t1"."foo_id" = "t2"."id")', [])

All of these other queries work as expected. The bar.foo_id is populated and not null

```

bar = list(
    Bar.select(
        Bar.foo_id
    )
)[0]

assert bar.foo_id == foo.id

bar = list(
    Bar.select(
        Bar.foo_id,
    ).join(
        Foo, on=(Bar.foo_id == Foo.id)
    )
)[0]


```

Edit: I noticed that if I select `Foo.id` or the entire `Foo` instead of `Foo.name`, it works as expected:


bar = list(
    Bar.select(
        Bar.foo_id,
        Foo  # selecting the entire Foo works
    ).join(
        Foo, on=(Bar.foo_id == Foo.id)
    )
)[0]

assert bar.foo_id == foo.id

bar = list(
    Bar.select(
        Bar.foo_id,
        Foo.id  # selecting just the Foo.id also works
    ).join(
        Foo, on=(Bar.foo_id == Foo.id)
    )
)[0]

assert bar.foo_id == foo.id

```

It even works if I just select Foo.id, not Bar.foo_id:

bar = list(
    Bar.select(
        Foo.id
    ).join(
        Foo, on=(Bar.foo_id == Foo.id)
    )
)[0]

assert bar.foo_id == foo.id

Also, the `.dicts()` and `.objects()` work fine and return the correct non-None value:

.objects() is fine:

```
bar = list(
    Bar.select(
        Bar.foo_id,
        Foo.name,
    ).join(
        Foo, on=(Bar.foo_id == Foo.id)
    ).objects()
)[0]

assert bar.foo_id == foo.id
```

.dicts() is also fine:

```
bar = list(
    Bar.select(
        Bar.foo_id,
        Foo.name,
    ).join(
        Foo, on=(Bar.foo_id == Foo.id)
    ).dicts()
)[0]

assert bar['foo'] == foo.id
```


It seems you have missed something very fundamental, which is explained in the relationships docs: http://docs.peewee-orm.com/en/latest/peewee/relationships.html 

Specifically: http://docs.peewee-orm.com/en/latest/peewee/relationships.html#selecting-from-multiple-sources

Also please do not use "foo" and "bar" as these have no context. Let's use "User" (for foo) and "Tweet" (for bar):

 ```python

# Here is what you are trying to do:
u = User.create(name='user')
Tweet.create(user=u, content='tweet-1')
Tweet.create(user=u, content='tweet-2')

query = (Tweet
         .select(Tweet, User)
         .join(User))
```

Here is where you went wrong:

```python
for tweet in query:
    # Note that the related user-data is stored in tweet.user!
    print(tweet.user.name, tweet.content)
```

The other thing is your foreign-key is named "foo" (or "user" in my example) but you are selecting `foo_id` -- no need to do that, nor should you be joining on it. Just use the fk name:

```python
query = (Tweet
         .select(Tweet.content, User.id, User.name)
         .join(User, on=(Tweet.user == User.id)))

for tweet in query:
    print(tweet.user.id, tweet.user.name, tweet.content)
```

Hi @coleifer ,

Apologies if I am misinterpreting something, but my understanding is that you are suggesting to always join to the parent User table in order to access the foreign key. This is not optimal in the case when you only need to return the foreign key from the Tweet child table, because there is no need to do a relatively expensive database join in this case. 

In peewee, if I don't join to the parent User table, I must access the foreign key via `tweet.user_id`. The alternative, `tweet.user.id` will result in a N+1 query.

If I do join to the parent table, I must access the foreign key via `tweet.user.id` (this will not result in N+1). The alternative, `tweet.user_id` will return None. So this `tweet.user_id` is inconsistent depending on if we join or not.

-----

```
u = User.create(username='user')  # I'm using username here which is the same in the documentation
Tweet.create(user=u, content='tweet-1')
Tweet.create(user=u, content='tweet-2')
```

Let's say I have a `GET /tweets/` REST API, and I simply want to select all tweets and return the FK.

Please assume the following:

* My Tweet model has a lot of extra columns, and I only want to return the id, content, and user_id columns from the Tweet table.
* I have no need to join to the User table because I just want to return the FK column. Joining is not necessary here since I am returning no other columns from the User table.

```
tweets = list(
    Tweet.select(
        Tweet.id,
        Tweet.content, 
        Tweet.user,
    )
)
```

Here, if I enter `tweet.user.id`, it will generate a new select, one for each tweet, which I do not want:

```
>>>response = [{'id': tweet.id, 'content': tweet.content, 'user_id': tweet.user.id} for tweet in tweets]

('SELECT "t1"."id", "t1"."username" FROM "user" AS "t1" WHERE ("t1"."id" = %s) LIMIT %s OFFSET %s', [1, 1, 0])
('SELECT "t1"."id", "t1"."username" FROM "user" AS "t1" WHERE ("t1"."id" = %s) LIMIT %s OFFSET %s', [1, 1, 0])
```

If instead I enter `tweet.user_id`, it behaves as expected; no N+1 problem and I get the correct value.

```
>>> response = [{'id': tweet.id, 'content': tweet.content, 'user_id': tweet.user_id} for tweet in tweets]
>>> # No N+1 problem
```

If I understand you correctly, your advice is to join to the User table and then access it via `tweet.user.id`. However, this is not ideal since I have no need to do a join. It would be more optimal to skip the join and access via `tweet.user_id`.

-----

Now let's say my requirement has changed, and in my REST API I need to return the User's username. Now it is OK for me to join to the User table, whereas previously there was no need to do so.

However, if I change my code to join to the User table, I have to remember to change the select column to `User.id` from `Tweet.user` or else the `tweet.user` (and `tweet.user_id`) will both return None.


In your initial example you were using a JOIN, but yes if all you need is the user ID then you can access it from `tweet.user_id` without incurring any penalty.

```python

# No problem, 1 query.
query = Tweet.select(Tweet.id, Tweet.user, Tweet.content)
print([(row.id, row.user_id, row.content) for row in query])

# No problem, still 1 query.
query = (Tweet
         .select(Tweet.id, Tweet.user, Tweet.content, User.id, User.username)
         .join(User))
print([(row.id, row.user_id, row.content) for row in query])

# No problem, still 1 query.
query = (Tweet
         .select(Tweet.id, Tweet.user, Tweet.content, User.id, User.username)
         .join(User))
print([(row.id, row.user_id, row.content, row.user.username) for row in query])
```

Alternatively, if you want all attrs just patched onto the Tweet object (relationships not traversed):

```python
# No problem, still 1 query.
query = (Tweet
         .select(Tweet.id, Tweet.user, Tweet.content, User.username)
         .join(User)
         .objects())  # Add .objects()
print([(row.id, row.user_id, row.content, row.username) for row in query])
```

If we don't join, we can include `Tweet.user` and can then access `row.user_id`.

If we do join, we must include `User.id` in order to access `row.user_id`. Failing to include `User.id` will result in `row.user_id` being set to None. 

It works when you don't join but doesn't when you join. It is inconsistent, right?

-----

Case 1: No Join, include Tweet.user:  `row.user_id` is not None as expected:

```
query = Tweet.select(Tweet.id, Tweet.user, Tweet.content)
print([(row.id, row.user_id, row.content) for row in query])
```

Case 2: Join, do NOT include User.id: `tweet.user_id` is now None. It is inconsistent with Case 1.

```
query = (Tweet
         .select(Tweet.id, Tweet.user, Tweet.content, User.username)
         .join(User))
print([(row.id, row.user_id, row.content) for row in query])
# row.user_id is None here - not consistent with Case 1
```

Case 3: Join, must include `User.id` in order for `tweet.user_id` to not be None:

```
query = (Tweet
         .select(Tweet.id, Tweet.user, Tweet.content, User.id, User.username)
         .join(User))
print([(row.id, row.user_id, row.content) for row in query])
```

Does it make sense?

This has to do with the way that Peewee rebuilds the model graph. I may take a look at changing it but I don't really consider it a big deal as the workarounds are quite simple.