Issue: get_or_create with 'self' ForeignKeyField : fails due to ambiguous column name
Author: 1bpm
State: closed
Created: 2016-04-25 16:55:31

When using a ForeignKeyField referring to 'self', get_or_create fails with 'ambiguous column name', as it produces a statement with two "t1" aliases.

Peewee 2.8.0 using Postgresql, tested with Sqlite and example here using Sqlite.

``` python
from peewee import *

db = SqliteDatabase("sqlite.db")

class TestModel(Model):
    somefield = TextField()
    parent = ForeignKeyField("self", null = True)
    class Meta:
        database = db

db.create_tables([TestModel], safe = True)
```

Initial record creation ok:

``` python
parent, new = TestModel.get_or_create(somefield = "parent")
```

`('SELECT "t1"."id", "t1"."somefield", "t1"."parent_id" FROM "testmodel" AS t1 WHERE ("t1"."somefield" = ?) LIMIT 1', [u'parent'])
`

Dependent record creation fails:

``` python
child, new = TestModel.get_or_create(somefield = "child", parent = parent)
```

`('SELECT "t1"."id", "t1"."somefield", "t1"."parent_id" FROM "testmodel" AS t1 INNER JOIN "testmodel" AS t1 ON ("t1"."parent_id" = "t1"."id") WHERE (("t1"."parent_id" = ?) AND ("t1"."somefield" = ?)) LIMIT 1', [1, u'child'])`
OperationalError: ambiguous column name: t1.id

Trace:

``` python
/usr/local/lib/python2.7/dist-packages/peewee.pyc in get_or_create(cls, **kwargs)
   4510         sq = cls.select().filter(**kwargs)
   4511         try:
-> 4512             return sq.get(), False
   4513         except cls.DoesNotExist:
   4514             try:

/usr/local/lib/python2.7/dist-packages/peewee.pyc in get(self)
   2883         clone = self.paginate(1, 1)
   2884         try:
-> 2885             return next(clone.execute())
   2886         except StopIteration:
   2887             raise self.model_class.DoesNotExist(

/usr/local/lib/python2.7/dist-packages/peewee.pyc in execute(self)
   2930             query_meta = self.get_query_meta()
   2931             ResultWrapper = self._get_result_wrapper()
-> 2932             self._qr = ResultWrapper(model_class, self._execute(), query_meta)
   2933             self._dirty = False
   2934             return self._qr

/usr/local/lib/python2.7/dist-packages/peewee.pyc in _execute(self)
   2626     def _execute(self):
   2627         sql, params = self.sql()
-> 2628         return self.database.execute_sql(sql, params, self.require_commit)
   2629
   2630     def execute(self):

/usr/local/lib/python2.7/dist-packages/peewee.pyc in execute_sql(self, sql, params, require_commit)
   3459             else:
   3460                 if require_commit and self.get_autocommit():
-> 3461                     self.commit()
   3462         return cursor
   3463

/usr/local/lib/python2.7/dist-packages/peewee.pyc in __exit__(self, exc_type, exc_value, traceback)
   3283             else:
   3284                 exc_args = exc_value.args
-> 3285             reraise(new_type, new_type(*exc_args), traceback)
   3286
   3287 class _BaseConnectionLocal(object):

/usr/local/lib/python2.7/dist-packages/peewee.pyc in execute_sql(self, sql, params, require_commit)
   3452             cursor = self.get_cursor()
   3453             try:
-> 3454                 cursor.execute(sql, params or ())
   3455             except Exception:
   3456                 if self.get_autocommit() and self.autorollback:
```

Separating the get/create just to check doing it 'manually' works:

``` python
parent, new = TestModel.get_or_create(somefield = "parent")

test = TestModel.select().where((TestModel.somefield == "child") & (TestModel.parent == parent))
child = test[0] if test.exists() else TestModel.create(somefield = "child", parent = parent)
```

`('SELECT 1 FROM "testmodel" AS t1 WHERE (("t1"."somefield" = ?) AND ("t1"."parent_id" = ?)) LIMIT 1', [u'child', 1])
('INSERT INTO "testmodel" ("somefield", "parent_id") VALUES (?, ?)', [u'child', 1])
`


This is working in master:

``` python
>>> parent, new = TM.get_or_create(data='parent')
('SELECT "t1"."id", "t1"."data", "t1"."parent_id" FROM "tm" AS t1 WHERE ("t1"."data" = ?) LIMIT 1 OFFSET 0', [u'parent'])
('BEGIN DEFERRED', None)
('INSERT INTO "tm" ("data") VALUES (?)', [u'parent'])

>>> child, new = TM.get_or_create(data='child', parent=parent)
('SELECT "t1"."id", "t1"."data", "t1"."parent_id" FROM "tm" AS t1 WHERE (("t1"."data" = ?) AND ("t1"."parent_id" = ?)) LIMIT 1 OFFSET 0', [u'child', 4])
('BEGIN DEFERRED', None)
('INSERT INTO "tm" ("data", "parent_id") VALUES (?, ?)', [u'child', 4])

>>> print child.data, child.parent.data, new
child parent True

>>> print TM.get_or_create(data='child', parent=parent)
('SELECT "t1"."id", "t1"."data", "t1"."parent_id" FROM "tm" AS t1 WHERE (("t1"."data" = ?) AND ("t1"."parent_id" = ?)) LIMIT 1 OFFSET 0', [u'child', 4])

(<__main__.TM at 0x7f6bdaa95710>, False)
```

Looks like I may need to push a new release! I've been meaning to for some time so apologies for that. If you'd like to checkout the master branch and give your code a try, I believe you'll find that it works.


Ah, yes it does. Thank you!