Peewee: Foreign keys that do not end with "_id"

[karlb]

There seems to be no way to declare a foreign_key which does not end with "_id", since the expected column name is attribute_name + '_id'. Or did I miss way to declare such a key?

[coleifer]

Right now the column name is closely tied to the field name -- will see about disentangling this.

[prggmr]

To add some to this,

If you setup a ForeignKey without a "_id" it is created in the table with an "_id" appended to the name, e.g. field name blog will have a column name of "blog_id" in the table ( at least for MySQL ).

class Comment(peewee.Model):
    id = PrimaryKeyField()
    blog = ForeignKeyField(Blog)

If I then attempt to create a new record

peewee.InsertQuery(Comment, blog = a_blog_object)

You recieve the following

AttributeError: Field name blog not found.

Running the same with id appended

peewee.InsertQuery(Comment, blog_id = a_blog_object)

Works as expected.

Also creating a field with "_id" in the name results in the column name doubling the "_id".

E.g.

blog_id results in blog_id_id

I'm using version 0.8.

[coleifer]

OK...wow, this one was a bummer to fix. I had started with the assumption that field names and columns were interchangeable, but there was a weird special case where foreign key fields would get an "_id" tacked onto them. The actual id would be stored on the model instance in the "_id" field, and the object at the fields name would be a descriptor that would lookup/cache the related object. There was a lot of disentangling to do to separate out places where we're accessing and querying fields by name versus by column, but i'm pretty confident that i've got this one fixed.

The default behavior has not changed -- in other words, "_id" is still added on -- but if you want you can manually specify the underlying column for any field:

    class Blog(Model):
        name = CharField(db_column='mySweetPhpName')

    class Entry(Model):
        name = CharField()
        blog = ForeignKeyField(db_column='blog') # <-- actually stores it in "blog" in the database

I think i've got good test coverage for this, but if you run into any bugs with it let meknow!

[karlb]

Works great. Thanks a lot!

[dstruck]

Might it be possible to adapt 'pwiz.py' to add the ',db_column=' argument for every foreign key column?

[coleifer]

@dstruck -- Any reason this is preferable?

[dstruck]

Expected that pwiz would use the column names from the existing tables to generate the models. As I tend to use "id_user", "id_center" instead of "user_id" or "center_id", this clashed with the default behaviour of peewee to append "_id" to foreign key columns.

This is an example to show the "issue":

'''
-- create two test tables in postgresql

CREATE TABLE list_user(
id          SERIAL PRIMARY KEY
,name           TEXT
);

INSERT INTO list_user (name) VALUES ('test');

CREATE TABLE sample(
id          SERIAL PRIMARY KEY
,id_user        INT REFERENCES list_user
,data           TEXT
);
'''

################################################################################
# classes generated by pwiz.py
# pwiz.py -e postgresql test > model.txt
from peewee import *

database = PostgresqlDatabase('test', **{})

class UnknownFieldType(object):
    pass

class BaseModel(Model):
    class Meta:
        database = database

class List_User(BaseModel):
    name = TextField(null=True)

    class Meta:
        db_table = 'list_user'

class Sample(BaseModel):
    data = TextField(null=True)
    id_user = ForeignKeyField(null=True, rel_model=List_User)

    class Meta:
        db_table = 'sample'
################################################################################

# test
entry = Sample(id_user=1,data="test123")
entry.save()

'''
error message produced by this test:

psycopg2.ProgrammingError: column "id_user_id" of relation "sample" does not exist
LINE 1: INSERT INTO "sample" ("data", "id_user_id") VALUES ('test123...
'''

[coleifer]

I will try and write some tests for this code as it is sometimes a bit buggy and difficult to verify.

[dstruck]

Thanks for the fix. Will test it on our databases and report back if I find any other issue.

ps: never was a fan of orm's, always worked directly with databases, but peewee might change my opinion ;-)

[dcrystalj]

would be nice to add db_column in api docs

[coleifer]

It is: http://docs.peewee-orm.com/en/latest/peewee/api.html#fields

And: http://docs.peewee-orm.com/en/latest/peewee/models.html#field-initialization-arguments