Issue: "ambiguous column name: t1.id" on weird SQL query
Author: franciscod
State: closed
Created: 2017-09-28 02:52:09

```
('SELECT 
    "t1"."id", 
    "t1"."finished", 
    "t1"."timestamp", 
    "t1"."previous_self_id", 
    "t1"."car_id", 
    "t1"."best_hotlap_id", 
    "t1"."laps_total" 
  FROM 
    "caraggregate" AS t1 
  INNER JOIN 
    "car" AS t2 
    ON ( "t1"."car_id" = "t2"."id") 
  INNER JOIN "caraggregate" AS t1 
    ON ( "t1"."previous_self_id" = "t1"."id")
    WHERE (( "t1"."car_id" = ?) AND ("t1"."previous_self_id" = ?)) LIMIT 1', [1, None])
```
Is this query worth of investigating a bug or I'm just using something wrong? It seems weird to me that "t1" is bound twice to "caraggregate"...

You probably need to use a model alias for the self-join on `caraggregate`.

```python
CarAggregateAlias = CarAggregate.alias()
...
.join(CarAggregateAlias, on=(CarAggregate.previous_self == CarAggregateAlias.id))
```

Hey Charles, thanks for helping :)

I'm using `get_or_create` and don't see where I should use the model alias.

My reduced repro code looks like this:

models.py:
```py

import datetime

from peewee import *

db = SqliteDatabase('db_weird_query.sqlite')


class BaseModel(Model):
    class Meta:
        database = db


class Car(BaseModel):
    name = CharField()


class CarAggregate(BaseModel):
    finished = BooleanField(default=False)
    timestamp = DateTimeField(default=datetime.datetime.now)
    previous_self = ForeignKeyField('self', null=True, related_name='newer_self')
    car = ForeignKeyField(Car, related_name='aggregates')


db.create_tables([
    Car, CarAggregate,
], safe=True)
```

```py
for car in Car.select():
    previous_aggregate = None
    for previous_aggregate in (
        CarAggregate.select().where(CarAggregate.car==car, CarAggregate.finished==True)
                    .order_by(CarAggregate.timestamp.desc())):
        break

    # This fails
    aggr, cr = CarAggregate.get_or_create(
        previous_self=previous_aggregate,
        car=car,
    )

    print("get or c", aggr, cr)
```

A workaround is to split the `get_or_create` into this...

```py
try:
    aggr = CarAggregate.select().where(
        CarAggregate.previous_self==previous_aggregate,
        CarAggregate.car==car,
        CarAggregate.finished==True,
    ).order_by(CarAggregate.timestamp.desc()).get()
except CarAggregate.DoesNotExist:
    aggr = CarAggregate.create(
        car=car,
        previous_self=previous_aggregate,
    )
```

...but I'm curious on why the `get_or_create` is generating that query and, again, is this a bug or a PEBKAC? :)

Weird, it seems like a bug to me.

I tested this in the 3.0a branch and it seems to be working as expected. I haven't tested this exactly in the latest 2.x yet, but first -- Is the code you shared in the get_or_create example exactly the code that triggered the error?

Yep. I'll make a .py that reproduces this on 2.10.1

... silly me. Couldn't reproduce and then I realized I have peewee installed both inside and outside the virtualenv. Inside it, with 2.10.1, it works as expected. It just happens on 2.6.4.

aghhhhhh sorry >.<

This is the full code. I'll go and bisect 2.6.4 ~ 2.10.1 and see where it got fixed :)

```py

import datetime

from peewee import *

db = SqliteDatabase('db_weird_query.sqlite')


class BaseModel(Model):
    class Meta:
        database = db


class Car(BaseModel):
    name = CharField()


class CarAggregate(BaseModel):
    finished = BooleanField(default=False)
    timestamp = DateTimeField(default=datetime.datetime.now)
    previous_self = ForeignKeyField('self', null=True, related_name='newer_self')
    car = ForeignKeyField(Car, related_name='aggregates')


db.create_tables([
    Car, CarAggregate,
], safe=True)

def main():
    for name in ['foo', 'bar', 'baz']:
        car, _ = Car.get_or_create(
            name=name,
        )

    for car in Car.select():
        previous_aggregate = None
        for previous_aggregate in (CarAggregate.select().where(
                    CarAggregate.car==car,
                    CarAggregate.finished==True
                ).order_by(CarAggregate.timestamp.desc())):
            break

        aggr, cr = CarAggregate.get_or_create(
            previous_self=previous_aggregate,
            car=car,
        )

if __name__ == '__main__':
    main()

```

```
$ git bisect run python peeweebug.py
running python peeweebug.py
<__main__.CarAggregate object at 0x7fd7f098cda0> False
Bisecting: 145 revisions left to test after this (roughly 7 steps)
[b65da77087abd86271fad78755d8580c124c0c26] Sqlite c extension cleanups.
running python peeweebug.py
ambiguous column name: t1.id
Bisecting: 72 revisions left to test after this (roughly 6 steps)
[f2f8a7381ba5aa4b9b9fad2349622d6bb9cbe2c7] Merge pull request #855 from lez/master
running python peeweebug.py
<__main__.CarAggregate object at 0x7f57934139e8> False
Bisecting: 36 revisions left to test after this (roughly 5 steps)
[1d0edf9177a0fd502cbd627c930e56ad0837013b] Fix travis.yml
running python peeweebug.py
ambiguous column name: t1.id
Bisecting: 18 revisions left to test after this (roughly 4 steps)
[0480fcec5db77c0b161853d9ab49241f89cd23c3] Ensure get_or_create() works with new GFK API, refs #313
running python peeweebug.py
<__main__.CarAggregate object at 0x7fd90bbbc9b0> False
Bisecting: 8 revisions left to test after this (roughly 3 steps)
[8473056690c1afdcf5bc8599c87a9f390ad9edc9] Fuck python 3.
running python peeweebug.py
ambiguous column name: t1.id
Bisecting: 4 revisions left to test after this (roughly 2 steps)
[35304d289391ae7fdab0f752ffada7faf2fc83dc] Warn if Cython is old, fixes #821.
running python peeweebug.py
ambiguous column name: t1.id
Bisecting: 2 revisions left to test after this (roughly 1 step)
[fa454e075de9befbfa281cc0e33f7a8c093e34fe] Fix typo.
running python peeweebug.py
ambiguous column name: t1.id
Bisecting: 0 revisions left to test after this (roughly 1 step)
[b42a86cc0343ab91b203625e67f7191f0ffcaaf3] Support equality / inequality with GFK in queries, fixes #313
running python peeweebug.py
ambiguous column name: t1.id
0480fcec5db77c0b161853d9ab49241f89cd23c3 is the first bad commit
commit 0480fcec5db77c0b161853d9ab49241f89cd23c3
Author: Charles Leifer <coleifer@gmail.com>
Date:   Wed Jan 20 12:21:22 2016 -0600

    Ensure get_or_create() works with new GFK API, refs #313

:100644 100644 e45b89db35e2dea6a468847c21b2ac826e60fd94 af1457f69d9a0f28cbda4458a7ce122f684c4d06 M	peewee.py
:040000 040000 64dc2b7aacce84f6dcdf78cf1225dfca6f11e38a 6ae72966a75ef4a6df4df87c981acf4e1d86288c M	playhouse
bisect run success
```
Working fine since Jan 2016 :)

https://github.com/coleifer/peewee/commit/0480fcec5db77c0b161853d9ab49241f89cd23c3

whaaaaat I'm on the thread referred by that commit
https://github.com/coleifer/peewee/issues/313

tripping twice on the same stone I guess :)

LOL! Glad you're all set.