Using date_part on an aliased field generates new alias

[CorverDevelopment]

When using the date_part functionality (and others, e.g. contains_any in ArrayField), the field is passed as self. This works beautifully if you are using the model it is defined in, but, when using it on an aliased table, it generates a new alias.

Below is a very crude example from how I found this "problem". This first query shows that the part of the query that will be constructed works when called with the aliased field: fn.DATE_PART('year', Client.birth) > 1980).

Calling Client.birth.year is what is generating the problem here, as it will refer to the Person model instead of the Client alias, generating the fourth alias. Would I not alias Owner, but use the Person model, it would construct the where part based on that alias, instead of the Client.

from peewee import *
import logging


database = SqliteDatabase(':memory:')

logger = logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())


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


class Person(Base):
    name = CharField()
    birth = DateField()


class Traject(Base):
    owner = ForeignKeyField(Person, related_name='trajects_owned')
    client = ForeignKeyField(Person, related_name='trajects')


Person.create_table()
Traject.create_table()

john = Person.create(name='John', birth='1979-1-1')
suzi = Person.create(name='Suzi', birth='1982-1-1')
louise = Person.create(name='Louise', birth='1970-1-1')
cody = Person.create(name='Cody', birth='1990-1-1')

Traject.create(owner=john, client=suzi)
Traject.create(owner=john, client=louise)
Traject.create(owner=john, client=cody)

Client = Person.alias()
Owner = Person.alias()

trajects = (Traject.select()
            .join(Client, on=(Traject.client == Client.id))
            .join(Owner, on=(Traject.owner == Owner.id))
            .where(fn.DATE_PART('year', Client.birth) > 1980))

assert trajects.count() == 2
# good so far

trajects = (Traject.select()
            .join(Client, on=(Traject.client == Client.id))
            .join(Person, on=(Traject.owner == Person.id))
            .where(Client.birth.year > 1980))

assert trajects.count() == 0
# this should be two, but the where is based on the owner

trajects = (Traject.select()
            .join(Client, on=(Traject.client == Client.id))
            .join(Owner, on=(Traject.owner == Owner.id))
            .where(Client.birth.year > 1980))

assert trajects.count() == 2
# this will generate an exception

Here are the two generated queries by peewee.

SELECT Count(*)
FROM "traject" AS t1
INNER JOIN "person" AS t2 ON ("t1"."client_id" = "t2"."id")
INNER JOIN "person" AS t3 ON ("t1"."owner_id" = "t3"."id")
WHERE (DATE_PART(?, "t2"."birth") > ?)
-- parameters: ['year', 1980]

SELECT Count(*)
FROM "traject" AS t1
INNER JOIN "person" AS t2 ON ("t1"."client_id" = "t2"."id")
INNER JOIN "person" AS t3 ON ("t1"."owner_id" = "t3"."id")
WHERE (date_part(?, "t3"."birth") > ?)
-- parameters: ['year', 1980]

SELECT Count(*) FROM "traject" AS t1
INNER JOIN "person" AS t2 ON ("t1"."client_id" = "t2"."id")
INNER JOIN "person" AS t3 ON ("t1"."owner_id" = "t3"."id")
WHERE (date_part(?, "t4"."birth") > ?)
-- parameters: ['year', 1980])

The exception that was caused by the third query:

Traceback (most recent call last):
  File "app.py", line 61, in <module>
    assert trajects.count() == 2
  File "$/site-packages/peewee.py", line 2865, in count
    return self.aggregate(convert=False) or 0
  File "$/site-packages/peewee.py", line 2858, in aggregate
    return self._aggregate(aggregation).scalar(convert=convert)
  File "$/site-packages/peewee.py", line 2637, in scalar
    row = self._execute().fetchone()
  File "$/site-packages/peewee.py", line 2628, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "$/site-packages/peewee.py", line 3461, in execute_sql
    self.commit()
  File "$/site-packages/peewee.py", line 3285, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "$/site-packages/peewee.py", line 3454, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: no such column: t4.birth

[coleifer]

I'm confused...what's different in the 2nd and 3rd queries? I can see that in the first it uses t3 for the alias, but don't understand why it uses t4 in the last query.

[coleifer]

Oops, I see: it's using Person in the second example query. I swear I looked at them for a minute and didn't see that. Ok, makes sense.

[coleifer]

d7720d9

This is a stab at an implementation. What do you think?

[CorverDevelopment]

I just tested the example and tested it in my project and I can now remove a lot of work-arounds! Keep up the awesome work!

[coleifer]

This is fixed in 3.0a, commit: f81a30b