Peewee: Joining two tables without subquery. Is it possible?

Post date: Jul 15, 2014 3:58:38 PM

I just got a very simple question, how to rewrite this statement, so that it is using only join, without separate subquery. Should be easy? But I'm not enough experienced with peewee, so that I could get it done trivially. I've tried multiple times, and I just doesn't seem to figure out how it should be done. That's why I want to know if it's possible at all. Should be?

read = Status_t.select( Status_t.thread ).where( Status_t.usr == current_user )

query = Thread_t.select( Thread_t, Status_t )

      .order_by( Thread_t.time.desc() )

      .join( Status_t, JOIN_LEFT_OUTER )

      .where( ( Status_t.thread == None ) |

              ~( Status_t.thread << read ) |

              ( ( Status_t.last_read != Thread_t.comment ) &

                ( Status_t.uid == current_user )

              )

            )

Should be trivial right? Somehow I just doesn't seem to quickly figure out how it's done. Actually creating that horrible statement took quite a while. Without peewee ORM this would have been of course trivial, but I wanted to use ORM in the first place.

G+ post for comments.

KW: Python, sql, database, databases, tables, table, query, queries, join, joining, joins.