Issue: Multiple joins to same table
Author: coleifer
State: closed
Created: 2015-08-26 04:21:24

Hello,
Following the example you've given, I constructed a query for my models, which are like the models below:

``` python
class User(db.Model):
    username = CharField()

class GameResult(db.Model):
    user = ForeignKeyField(User)
    opponent = ForeignKeyField(User, related_name='opponent_gameresult_set', null=True)
```

And the query...

``` python
OpponentUser = User.alias()

result = GameResult.select(GameResult, User, OpponentUser)\                                    

                .switch(GameResult).join(User, on=GameResult.user)\                                    

                .switch(GameResult).join(OpponentUser, JOIN.LEFT_OUTER,                         

                                  on=(GameResult.opponent==OpponentUser.id))\                   

                .where(GameResult.id==id).get()
```

The problem is that result.user and result.opponent are always the same (but they shouldn't be). It seems like result.user get overwritten by whatever the value is for result.opponent. Even when result.opponent is None, result.user will contain a model instance but all the fields (except for the id) are set to None. Is this a bug or is there something wrong with my query?


If you add an alias to the second join specifying which attribute to use, peewee will do the right thing:

``` python
Opponent = User.alias()
query = (GameResult
         .select(GameResult, User, Opponent)
         .join(User, on=(GameResult.user == User.id))
         .switch(GameResult)
         .join(Opponent, on=(GameResult.opponent == Opponent.id).alias('opponent'))
         .order_by(Game.id))
```

I think peewee has enough info here, though, to perhaps determine that the `opponent` field is what we want. I will look into a fix.


I've added some changes to the join logic and this should now be a bit smarter about assigning the joined instance to the correct attribute without requiring an explicit alias.


Hey Charles, I'm just trying this out with the latest copy of master and doesn't seem to be working for me, have you pushed the commit or am I doing something wrong?

```
class User(Model):
    uuid = UUIDField(primary_key=True)
    name = CharField()

class Brief(Model):
    uuid = UUIDField(primary_key=True)
    user = ForeignKeyField(User, null=False)
    assignee = ForeignKeyField(User, null=True)
    ...

Assignee = User.alias()
query = Brief.select(Brief, User, Assignee)
    .join(User, on=(User.uuid == Brief.user))
    .switch(Brief)
    .join(Assignee, on=(Assignee.uuid == Brief.assignee).alias("assignee"))
    .order_by(...)
```

It's not applying the node alias properly by the looks of it because I get:

```
SELECT
    "t1"."uuid", "t1"."user_id", "t1"."state", "t1"."sent", "t1"."assignee_id",
    "t2"."uuid", "t2"."name",
    "t3"."uuid", "t3"."name"

FROM "brief" AS t1
    INNER JOIN "user" AS t2 ON ("t2"."uuid" = "t1"."user_id")
    INNER JOIN "user" AS t2 ON ("t3"."uuid" = "t1"."assignee_id")

ORDER BY "t1"."sent", "t1"."created"
```

It correctly applies it inside the node but not to the table alias, I therefore get.

```
ProgrammingError: table name "t2" specified more than once
```

Sorry for reopening a closed issue, let me know if you want a separate issue.


Using your code I get the right SQL:

``` python
from peewee import *

db = SqliteDatabase(':memory:')

class User(Model):
    uuid = CharField(primary_key=True)
    name = CharField()

    class Meta:
        database = db

class Brief(Model):
    uuid = CharField(primary_key=True)
    user = ForeignKeyField(User)
    assignee = ForeignKeyField(User, null=True, related_name='_xxx')

    class Meta:
        database = db

db.create_tables([User, Brief])

Assignee = User.alias()
query = (Brief
         .select(Brief, User, Assignee)
         .join(User, on=(User.uuid == Brief.user))
         .switch(Brief)
         .join(Assignee, on=(Assignee.uuid == Brief.assignee).alias('assignee'))
         .order_by(User.name))
print query
```

Result:

``` sql
SELECT "t1"."uuid", "t1"."user_id", "t1"."assignee_id", "t2"."uuid", "t2"."name", "t3"."uuid", "t3"."name" 
FROM "brief" AS t1 
INNER JOIN "user" AS t2 ON ("t2"."uuid" = "t1"."user_id") 
INNER JOIN "user" AS t3 ON ("t3"."uuid" = "t1"."assignee_id") 
ORDER BY "t2"."name"
```

What am I missing?


I'm experiencing a similar issue. (also sorry for posting in old issue).

I've written a script to demonstrate. If not a bug , hopefully can demonstrate

EDIT - updating to remove a join

```
from __future__ import division
from peewee import Model, JOIN_LEFT_OUTER
from playhouse.shortcuts import model_to_dict
from peewee import fn, SQL, JOIN
from peewee import CharField, DateTimeField, BigIntegerField, BooleanField, IntegerField, ForeignKeyField, Proxy, TextField, PrimaryKeyField,DoubleField
from playhouse.postgres_ext import PostgresqlExtDatabase
from playhouse.postgres_ext import BinaryJSONField, ArrayField, TSVectorField


def dbfactory(dbname="hai"):
    # https://github.com/coleifer/peewee/issues/410
    host = "/tmp/"
    user = "hendler"
    # user = "root"
    password = "rootroot"
    # max_connections = 350
    # stale_timeout = 3000
    # connection = PooledMySQLDatabase(dbname, host=host, user=user, passwd=password, charset='utf8', use_unicode=True, max_connections=max_connections, stale_timeout=stale_timeout )
    connection = PostgresqlExtDatabase(dbname, host=host, user=user, password=password, autocommit=True, autorollback=True)
    # connection.connect()
    return connection


class BaseModel(Model):
    """Simple table with prevs and an integer."""

    class Meta:
        """Standard meta field."""

        database = dbfactory()


# https://github.com/coleifer/peewee/issues/692


class User(BaseModel):
    id = PrimaryKeyField()


class Question(BaseModel):
    id = PrimaryKeyField()


class Answer(BaseModel):
    id = PrimaryKeyField()
    user = ForeignKeyField(User, related_name='answerusers')
    question = ForeignKeyField(Question, related_name='answerquestions')


class Page(BaseModel):
    id = PrimaryKeyField()
    user = ForeignKeyField(User, related_name='pageusers')
    question = ForeignKeyField(Question, related_name='pagequestions')


class Comment(BaseModel):
    id = PrimaryKeyField()
    user = ForeignKeyField(User, related_name='commentusers')
    question = ForeignKeyField(Question, related_name='commentquestions')


def setup():
    print "creating table"
    print "\tuser -> 1:n -> pages -> 1:1 -> questions -> 1:n -> answers"
    print "\tuser -> 1:n -> comments -> 1:1 -> questions -> 1:n -> answers"
    # drop tables every time
    if Commment.table_exists():
        Commment.drop_table()
    if Page.table_exists():
        Page.drop_table()
    if Answer.table_exists():
        Answer.drop_table()
    if Question.table_exists():
        Question.drop_table()
    if User.table_exists():
        User.drop_table()

    User.create_table()
    Question.create_table()
    Answer.create_table()
    Page.create_table()
    Commment.create_table()

    user1 = User.create()
    user2 = User.create()

    question1 = Question.create()
    question2 = Question.create()
    question3 = Question.create()

    answer1 = Answer.create(question=question1, user=user1)
    answer2 = Answer.create(question=question2, user=user1)
    answer3 = Answer.create(question=question3, user=user1)
    answer4 = Answer.create(question=question3, user=user2)
    answer5 = Answer.create(question=question3, user=user2)

    page1 = Page.create(user=user1, question=question1)
    page2 = Page.create(user=user2, question=question3)
    comment1 = Comment.create(user=user2, question=question3)
    comment2 = Comment.create(user=user1, question=question2)

def runtest1():
    results = User.select(User, Page, Comment, Question, Answer)
    results = results.join(Page).join(Answer, on=(Answer.question_id == Page.question_id))
    results = results.switch(User).join(Comment).join(Answer, on=(Answer.question_id == Comment.question_id))
    print results.sql()
    for result in results:
        print result

def runtest2():
    # , on=(ActionTemplate.id == Action.actiontemplate)).alias('at')
    results = User.select(User, Page, Commment, Question, Answer)
    results = results.join(Page, on=(Page.user_id == User.id))
    results = results.join(Question, on=(Question.id ==Page.question_id).alias("page_question"))
    results = results.join(Answer, on=(Answer.question_id == Page.question_id))
    results = results.switch(User)
    results = results.join(Comment, on=(Comment.user_id == User.id))
    results = results.join(Question, on=(Question.id == Comment.question_id).alias("comment_question"))
    results = results.join(Answer, on=(Answer.question_id == Comment.question_id))

    print results.sql()
    for result in results:
        print result

if __name__ == "__main__":
    import traceback
    setup()
    try:
        runtest1()
    except Exception as e:
        print e.message
        # print traceback.format_exc()
    try:
        runtest2()
    except Exception as e:
        print e.message
        # print traceback.format_exc()



```

   The output

```
creating table
    user -> 1:n -> pages -> 1:1 -> questions -> 1:n -> answers
    user -> 1:n -> comments -> 1:1 -> questions -> 1:n -> answers
Missing required join predicate.
('SELECT "t1"."id", "t4"."id", "t4"."user_id", "t4"."question_id", "t5"."id", "t5"."user_id", "t5"."question_id", "t2"."id", "t3"."id", "t3"."user_id", "t3"."question_id" FROM "user" AS t1 INNER JOIN "page" AS t4 ON ("t1"."id" IS %s) INNER JOIN "comment" AS t5 ON ("t1"."id" IS %s) INNER JOIN "question" AS t2 ON ("t2"."id" IS %s) INNER JOIN "answer" AS t3 ON ("t2"."id" = "t3"."question_id") INNER JOIN "answer" AS t3 ON ("t2"."id" = "t3"."question_id") INNER JOIN "question" AS t2 ON ("t2"."id" IS %s)', [None, None, None, None])
table name "t3" specified more than once
```


I don't get the error with aliasing, AND enforcing left outer joins. BUT the params values look off 
`[None, None, None, None]`
Without the left outer, the joins don't work.  

```
    AnswerAlias = Answer.alias()
    QuestionAlias = Question.alias()

    results = User.select(User, Page, Comment, Question, Answer, QuestionAlias, AnswerAlias)
    results = results.join(Page, JOIN.LEFT_OUTER, on=(Page.user_id == User.id))
    results = results.join(Question, JOIN.LEFT_OUTER, on=(Question.id == Page.question_id))
    results = results.join(Answer, JOIN.LEFT_OUTER, on=(Answer.question_id == Page.question_id))
    results = results.switch(User)
    results = results.join(Comment, JOIN.LEFT_OUTER, on=(Comment.user_id == User.id))
    results = results.join(QuestionAlias, JOIN.LEFT_OUTER, on=(QuestionAlias.id == Comment.question_id) )
    results = results.join(AnswerAlias, JOIN.LEFT_OUTER, on=(AnswerAlias.question_id == Comment.question_id))

    print results.sql()
    print results.count()

```

`('SELECT "t1"."id", "t2"."id", "t2"."user_id", "t2"."question_id", "t3"."id", "t3"."user_id", "t3"."question_id", "t6"."id", "t7"."id", "t7"."user_id", "t7"."question_id", "t4"."id", "t5"."id", "t5"."user_id", "t5"."question_id" FROM "user" AS t1 LEFT OUTER JOIN "page" AS t2 ON ("t1"."id" IS %s) LEFT OUTER JOIN "comment" AS t3 ON ("t1"."id" IS %s) LEFT OUTER JOIN "question" AS t4 ON ("t4"."id" IS %s) LEFT OUTER JOIN "answer" AS t5 ON ("t4"."id" = "t5"."question_id") LEFT OUTER JOIN "question" AS t6 ON ("t6"."id" IS %s) LEFT OUTER JOIN "answer" AS t7 ON ("t6"."id" = "t7"."question_id")', [None, None, None, None])`


For fuck's sake did you have to put "Bug" in front of every class?