Issue: Alias doesn't work if it's not the first join
Author: Pirheas
State: closed
Created: 2016-05-04 17:47:56

Hello,

It seems that aliases only works if they are used on the first join.
Well, a small example will explain better:

SQL database creation:

``` sql
CREATE TABLE Student(
    id_student INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE Teacher(
    id_teacher INTEGER PRIMARY KEY,
    name              TEXT NOT NULL
);


CREATE TABLE Course(
    id_course INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    id_teacher INTEGER NOT NULL,
    FOREIGN KEY (id_teacher) REFERENCES Teacher(id_teacher) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE StudentCourse(
    id_student_course SERIAL PRIMARY KEY,
    id_student INTEGER NOT NULL,
    id_course INTEGER NOT NULL,
    FOREIGN KEY (id_student) REFERENCES Student(id_student) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (id_course) REFERENCES Course(id_course) ON DELETE CASCADE ON UPDATE CASCADE 
);
CREATE UNIQUE INDEX unique_student_course ON StudentCourse(id_student, id_course);

INSERT INTO Student(id_student, name) VALUES (1, 'Pirheas'), (2, 'Mike'), (3, 'Clara');
INSERT INTO Teacher(id_teacher, name) VALUES (1, 'Guido Van Rossum'), (2, 'Coleifer');
INSERT INTO Course(id_course, name, id_teacher) VALUES (1, 'Python', 1), (2, 'Peewee', 2);
INSERT INTO StudentCourse(id_student, id_course) VALUES (1,1), (1,2), (2,1), (3,2);
```

Tests:

``` python
#!/usr/bin/env python3
from peewee import *

database = PostgresqlDatabase('peewee_test', **{'user': 'postgres', 'host': '127.0.0.1', 'port': 8888, 'password': '<#redacted>'})

class UnknownField(object):
    pass

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

class Teacher(BaseModel):
    id_teacher = PrimaryKeyField()
    name = TextField()

    class Meta:
        db_table = 'teacher'

class Course(BaseModel):
    id_course = PrimaryKeyField()
    id_teacher = ForeignKeyField(db_column='id_teacher', rel_model=Teacher, to_field='id_teacher')
    name = TextField()

    class Meta:
        db_table = 'course'

class Student(BaseModel):
    id_student = PrimaryKeyField()
    name = TextField()

    class Meta:
        db_table = 'student'

class Studentcourse(BaseModel):
    id_course = ForeignKeyField(db_column='id_course', rel_model=Course, to_field='id_course')
    id_student = ForeignKeyField(db_column='id_student', rel_model=Student, to_field='id_student')
    id_student_course = PrimaryKeyField()

    class Meta:
        db_table = 'studentcourse'
        indexes = (
            (('id_student', 'id_course'), True),
        )

########################################################################################################

def test1():
    # This works perfectly
    data = (Student.select(Student, Teacher)
                  .join(Teacher, on=(Teacher.id_teacher == Student.id_student).alias('teacher'))) # Just for testing
    for d in data:
        print(d.name, '|', d.teacher.name)

def test2():
    #It doesn't work. "teacher" alias is not present in data element
    data = (Student.select(Student, Teacher)
                  .join(Studentcourse)
                  .join(Course)
                  .join(Teacher, on=(Teacher.id_teacher == Course.id_teacher).alias('teacher')))
    for d in data:
        print(d.name, '|', d.teacher.name)


def test3():
    data = (Student.select(Student, Studentcourse, Course)
                  .join(Studentcourse, on=(Student.id_student == Studentcourse.id_student).alias('sc'))
                  .join(Course, on=(Course.id_course == Studentcourse.id_course).alias('course')))

    for d in data: # It works
        print(d.name, ' -> ', d.sc.id_course.name)

    for d in data: # It doesn't
        print(d.name, '|', d.course.name)

if __name__ == '__main__':
    print(' == Test 1 ==')
    test1()
    print('\n == Test 2 ==')
    try:
        test2()
    except Exception as e:
        print('Error: ', str(e))
    print('\n == Test 3 ==')
    try:
        test3()
    except Exception as e:
        print('Error: ', str(e))
```

Thanks


Dude, I'm not going to read all that crap. Can you distill this into a short model or two and a query that fails?


Well, yeah, only the tests are important, the "crap" was only there to make it easier to reproduce.

``` python
def test2():
    # It doesn't work. "teacher" alias is not present in data element
    data = (Student.select(Student, Teacher)
                  .join(Studentcourse)
                  .join(Course)
                  .join(Teacher, 
                          on=(Teacher.id_teacher == Course.id_teacher).alias('teacher')))
    for d in data:
        print(d.name, '|', d.teacher.name)


def test3():
    data = (Student.select(Student, Studentcourse, Course)
                  .join(Studentcourse, 
                          on=(Student.id_student == Studentcourse.id_student).alias('sc'))
                  .join(Course, 
                          on=(Course.id_course == Studentcourse.id_course).alias('course')))

    for d in data: # It works
        print(d.name, ' -> ', d.sc.id_course.name)

    for d in data: # It doesn't
        print(d.name, '|', d.course.name)
```


Well, I think you've just misunderstood the way the models are re-constructed by the query result wrapper.

In `test3`, you will be able to access the course like this:

``` python
for d in data:
    print d.name, d.sc.course.name
```

Your comment that "it works" is actually wrong because that `sc.id_course` lookup is resolving the foreign key, costing you _n_ extra queries.

In `test2` a similar thing is happening, though it's less apparent why. You've selected Student and Teacher but none of the intermediary models needed to reconstruct the join graph. To fix this you can do this:

``` python
data = (Student.select(Student, Studentcourse, Course, Teacher).join(...)
for d in data:
    print d.studentcourse.id_course.teacher.name
```

Or you can just use `naive` and do:

``` python
data = Student.select(Student, Teacher.name.alias('teacher_name')).join(...)
```

Other things to consider would be renaming your foreign key fields. Why use `id_xxx` when Peewee provides the `db_column` attribute so you can avoid mangling up your python code?