Issue: `_id` field required for some transactions
Author: rmskinsa2
State: closed
Created: 2022-09-23 19:09:27

This has happened twice now and I'm trying to figure out if we're implementing things correctly.

So I have a model with several ManyToMany fields like so:
```
class Bob(db.Model):
...
    joes = ManyToManyField(Joe, backref='bobs')
    sues = ManyToManyField(Sue, backref='bobs')
    pams = ManyToManyField(Pam, backref='bobs')
...

class Joe(db.Model):
    a = IntegerField(null=True)
    
class Sue(db.Model):
    ...
    house = ForeignKey(House, null=True, backref='sues')
    
class Pam(db.Model):
    ...
    car = ForeignKey(Car, null=True, backref='pams')
```

Note that the ForeignKey fields were added via Playhouse migration some time after Sue and Pam tables were first created. Using `add_column`.
```
                migrator.add_column(
                    'pam',
                    'car',
                    Pam.car
                ),
```

When I instantiate an instance of Bob and call something like `self.sues` or `bob1.pams`, I get an error lilke:
```
"Unknown column 't1.house_id' in 'field list'"
```
or
```
"Unknown column 't1.car_id' in 'field list'"
```

To fix this, I create a column called `sue.house_id` and copy the data from `sue.house` into it. And with Pam I create a column called `pam.car_id` and copy data from `pam.car` into it.

It seems that the query of `bob.sues` or `bob.pams` tries to return the foreign key field, but expects an `_id` to be appended to that column name in the db. Playhouse does not create this `_id` column when we add the ForeignKey via migration. I read the following in the docs:
```
Sometimes you only need the associated primary key value from the foreign key column. In this case, Peewee follows the convention established by Django, of allowing you to access the raw foreign key value by appending "_id" to the foreign key field’s name
```
But it doesn't indicate that some queries _require_ or at least _default to_ an `_id` column for the ForeignKey, or that Playhouse doesn't add that column when we use the `add_column` feature. Are we implementing incorrectly or is this a bug?

Is this just occurring while you're doing things in a schema change? Can you post a minimal reproduction of the problem, as I'm having a hard time making sense of the issue as-is.

The actual problem occurs after doing a schema migration. In several places where our code is executing and an instance of Bob tries to access a ManyToMany field, which points to a model with a ForeignKey field we get this error. So like if we did:
```
car = Car.create(name='Jeep')
sue = Sue.create(car=car.id)
bob = Bob.create()
bob.sues.add(sue)
bob.save()

cars = [s.car.name for s in bob.sues] <-- error occurs here when calling bob.sues
```

I still am not clear what your schema is or exactly what **minimal** steps to follow to reproduce.

Going to close for now as I think this is probably some error in the various steps you may have followed, but if you can put together a minimal set of steps I can follow to reproduce I will happily reinvestigate.

I don't understand what else you need to reproduce? I recreated the minimum necessary data model here, pasted the migration that was done, as well as provided the minimum code needed to throw the error. Also please give me a chance to respond instead of closing over the weekend. I'll summarize everything here again in the hopes that this issue gets a serious look:

Start with this data model:
```
class Bob(db.Model):
    sues = ManyToManyField(Sue, backref='bobs')

class Sue(db.Model):
    age = IntegerField(null=True)
```

Call this code:
```
sue = Sue.create(age=45)
bob = Bob.create()
bob.sues.add(sue)
bob.save()

ages = [s.age for s in bob.sues]  # <-- Iterating over bob.sues works just fine
```

Now add a Car data model, update the Sue model to point to it via ForeignKeyField, and run a migration using Playhouse:
```
class Bob(db.Model):  # <-- NO change here
    sues = ManyToManyField(Sue, backref='bobs')

class Car(db.Model):  # <-- Create this
    make = CharField(null=True)

class Sue(db.Model):  # <-- add the car field to this
    age = IntegerField(null=True)
    car = ForeignKeyField(Car, null=True, backref='sues')
```
Migration (this is a very simplified version that doesn't contain all our usual error catching / rollback / logging code. I've stripped it down to the column add):
```
    # created reference to db and imported what's needed above ...
    migrator = get_migrator(db)
    with db.atomic() as transaction:
            # Add new Sue.car column
            migrate(
                migrator.add_column('sue', 'car', Sue.car)
            )
```

Now try this code:
```
car = Car.create(name='Jeep')
sue = Sue.create(car=car.id)
bob = Bob.create()
bob.sues.add(sue)
bob.save()

cars = [s.car.name for s in bob.sues] # <-- error occurs here when calling bob.sues
```

Get this error: `"Unknown column 't1.car_id' in 'field list'"`

Now do this migration:
```
    # created reference to db and imported what's needed above ...
    migrator = get_migrator(db)
    with db.atomic() as transaction:
            # Add Sue.car_id column
            migrate(
                migrator.add_column('sue', 'car_id', IntegerField(null=True))
            )


            sql = """
                UPDATE
                    db_name.sue
                SET
                    car_id = car;
            """
            db.execute_sql(sql)
```

Now run this code again:
```
car = Car.create(name='Jeep')
sue = Sue.create(car=car.id)
bob = Bob.create()
bob.sues.add(sue)
bob.save()

cars = [s.car.name for s in bob.sues]
```

It now works without throwing any errors.

Does querying object B through another object A's ManyToMany field assume any ForeignKey fields on object B have an `_id` appended to their name? Does Playhouse know this when it is adding a ForeignKey column to a table? Does the documentation talk about this somewhere that I missed?

Oh I see the issue, thank you for the example. Your migration that adds the foreign-key needs to use the column name and not the field name. The column name in this case is `car_id`:

```python
    migrator = get_migrator(db)
    with db.atomic() as transaction:
            # Add new Sue.car column
            migrate(
                migrator.add_column('sue', 'car_id', Sue.car)
            )
```

http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#SchemaMigrator.add_column

I guess my issue is that I don't see docs saying that we need to append `_id` onto the end of a foreign key column name. I was under the impression we could name a foreign key whatever we wanted to. The source code seems to expect this `_id` to be appended when making queries, but nowhere do I see where there are naming requirements for foreign key columns.

I agree that the docs could be improved. Hopefully the latest addition clarifies things: http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#supported-operations

That does clarify things, thank you.