Multiple joins to same table

[Sergey Kozlov]

Hi, i am trying to implement this model for forum:

class User(CustomModel):

username = CharField()

class Topic(CustomModel):

author = ForeignKeyField(User)

last_author = ForeignKeyField(User)

name = CharField()

        # some more definitions

class Post(CustomModel):

topic = ForeignKeyField(Topic)

author = ForeignKeyField(User)

datetime = DateTimeField()

post = TextField()

So, i need to get details of author and last author in topic:

topics = Topic.select(Topic, Student.username).annotate(Post).join(Student, on = Topic.last_author).switch(Topic).join(Student, on = Topic.author)

But i am getting this error:

ProgrammingError: table name "t3" specified more than once

Code generated by peewee is:

'SELECT t1."id", t1."author_id", t1."last_author_id", t1."name", t1."ident", t1."datetime", t3."username", Count(t2."id") AS count FROM "topic" AS t1 INNER JOIN "post" AS t2 ON t1."id" = t2."topic_id" INNER JOIN "student" AS t3 ON t1."last_author_id" = t3."id" INNER JOIN "student" AS t3 ON t1."author_id" = t3."id" GROUP BY t1."id", t1."author_id", t1."last_author_id", t1."name", t1."ident", t1."datetime", t3."username" ORDER BY t1."datetime" DESC'

Is there any way to get data with joining twice from one table using peewee? If not, is there any way to get username of user who made last post in topic?

[Charles Leifer]

This is actually a feature I added yesterday!  I plugged in your code and noticed a few small things that were a bit off, so I've pushed another commit to try and remedy them.  If you can, please try the code below and let me know how it works:

I rewrote your code:

class Student(CustomModel):

username = CharField()

class Topic(CustomModel):

author = ForeignKeyField(Student)

last_author = ForeignKeyField(Student)

name = CharField()

        # some more definitions

class Post(CustomModel):

topic = ForeignKeyField(Topic)

author = ForeignKeyField(Student)

datetime = DateTimeField()

post = TextField()

# create an alias to student table

LastAuthor = Student.alias()

topics = Topic.select(

    Topic, Student.username, LastAuthor.username.alias('last_username')

).annotate(Post).join(

    Student, on = Topic.author

).switch(Topic).join(

    LastAuthor, on =(Topic.last_author==LastAuthor.id)

)

--

-- generates the following SQL --

--

SELECT t1."id", t1."author_id", t1."last_author_id", t1."name", t3."username", t4."username" AS last_username, Count(t2."id") AS count 

FROM "topic" AS t1 

INNER JOIN "post" AS t2 ON (t1."id" = t2."topic_id") 

INNER JOIN "student" AS t3 ON (t1."author_id" = t3."id") 

INNER JOIN "student" AS t4 ON (t1."last_author_id" = t4."id") 

GROUP BY t1."id", t1."author_id", t1."last_author_id", t1."name", t3."username", t4."username

You might experiment with this...i'm not 100% sure w/o testing whether you will need or want that ".alias('last_username')" part or not.

--
You received this message because you are subscribed to the Google Groups "peewee-orm" group.
To unsubscribe from this group and stop receiving emails from it, send an email to peewee-orm+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Sergey Kozlov]

Wow, that was fast :)

I've pulled code from github and tried the code. Everything works fine, and looks like the part with ".alias('last_username')" is not required.

Thanks a lot :)

[Kenji Wellman]

Hello,

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

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...

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?

Thanks,

Kenji

[Charles Leifer]

I've created issue https://github.com/coleifer/peewee/issues/692 to look into it, thanks for letting me know!

For more options, visit https://groups.google.com/d/optout.

[Charles Leifer]

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

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.

[Kenji Wellman]

Thank you! That works well.