Peewee: Peewee multiple join with same table

[serkandaglioglu]

class Test2(BaseModel):

    test2_id = PrimaryKeyField()
    test2_name = CharField(null=True)

    class Meta:
        db_table = 'test2'
        order_by = ('test2_id',)


class Test(BaseModel):

    test_id = PrimaryKeyField()
    test_name = CharField(null=True)
    a = ForeignKeyField(Test2,db_column="a_id",to_field="test2_id",related_name="x")
    b = ForeignKeyField(Test2,db_column="b_id",to_field="test2_id", related_name="y")

    class Meta:
        db_table = 'test'
        order_by = ('test_id',)




A = Test2.alias()
B = Test2.alias()
t = (Test
    .select(Test,A,B)
    .join(A, on=(Test.a == A.test2_id) )
    .join(B, on=(Test.b == B.test2_id) )
    .where( Test.test_id==1 )
    .get()
    )

print t.test_name
print t.a.test2_name
print t.b.test2_name

This is the log:

('SELECT `t1`.`test_id`, `t1`.`test_name`, `t1`.`a_id`, `t1`.`b_id`, `t2`.`test2_id`, `t2`.`test2_name`, `t3`.`test2_id`, `t3`.`test2_name` FROM `test` AS t1 INNER JOIN `test2` AS t2 ON (`t1`.`a_id` = `t2`.`test2_id`) INNER JOIN `test2` AS t3 ON (`t1`.`b_id` = `t3`.`test2_id`) WHERE (`t1`.`test_id` = %s) ORDER BY `t1`.`test_id` ASC LIMIT 1', [1])
Test 1
Test2-1
('SELECT `t1`.`test2_id`, `t1`.`test2_name` FROM `test2` AS t1 WHERE (`t1`.`test2_id` = %s) ORDER BY `t1`.`test2_id` ASC LIMIT 1', [2])
Test2-2

In query I already selected A,B but peewee run another query for getting "Test2-2". I have same situation in my application and it set A and B same values. I checked A and B on database they have different values.

---

[coleifer]

Try this:

t = (Test
    .select(Test,A,B)
    .join(A, on=(Test.a == A.test2_id) )
    .switch(Test) # set query ctx back to test 
    .join(B, on=(Test.b == B.test2_id) )
    .where( Test.test_id==1 )
    t.get()
    )

If you are still having trouble try explicitly aliasing the join predicate:

t = (Test
    .select(Test,A,B)
    .join(A, on=(Test.a == A.test2_id).alias('a') )
    .switch(Test) # set query ctx back to test 
    .join(B, on=(Test.b == B.test2_id).alias('b') )
    .where( Test.test_id==1 )
    t.get()
    )

[serkandaglioglu]

@coleifer I tried both of your code, They print same output.

Printing code :

print t.test_name
print t.a.test2_name
print t.b.test2_name

The output :

Test 1
Test2-2
Test2-2

The query log :

('SELECT `t1`.`test_id`, `t1`.`test_name`, `t1`.`a_id`, `t1`.`b_id`, `t2`.`test2_id`, `t2`.`test2_name`, `t3`.`test2_id`, `t3`.`test2_name` FROM `test` AS t1 INNER JOIN `test2` AS t2 ON (`t1`.`a_id` = `t2`.`test2_id`) INNER JOIN `test2` AS t3 ON (`t1`.`b_id` = `t3`.`test2_id`) WHERE (`t1`.`test_id` = %s) ORDER BY `t1`.`test_id` ASC LIMIT 1', [1])

('SELECT `t1`.`test2_id`, `t1`.`test2_name` FROM `test2` AS t1 WHERE (`t1`.`test2_id` = %s) ORDER BY `t1`.`test2_id` ASC LIMIT 1', [2])

The query is correct but result is not correct. t.a.test2_name and t.b.test2_name have same values. I runned query on mysql there is no problem.

Query result on mysql console :

| test_id | test_name | a_id | b_id | test2_id | test2_name | test2_id(2) | test2_name(2) |
+---------+-----------+------+------+----------+------------+-------------+---------------+
| 1       | Test 1    | 1    | 2    | 1        | Test2-1    | 2           | Test2-2       |
+---------+-----------+------+------+----------+------------+-------------+---------------+

[coleifer]

When I tested the 2nd query against master, it only issued one query.

query = (Test
         .select(Test, A, B)
         .join(A, on=(Test.a == A.id).alias('a'))
         .switch(Test)
         .join(B, on=(Test.b == B.id).alias('b'))
         .where(Test.name == 't1'))

obj = query.get()
print obj.name
print obj.a.t2_name
print obj.b.t2_name

Console output:

('SELECT "t1"."id", "t1"."name", "t1"."a_id", "t1"."b_id", "t2"."id", "t2"."t2_name", "t3"."id", "t3"."t2_name" FROM "test" AS t1 INNER JOIN "test2" AS t2 ON ("t1"."a_id" = "t2"."id") INNER JOIN "test2" AS t3 ON ("t1"."b_id" = "t3"."id") WHERE ("t1"."name" = ?) LIMIT 1', [u't1'])
t1
t1a
t1b

[serkandaglioglu]

After I use .alias('a') and .alias('b'), The problem is solved.
Thanks.