Nested Join
What?! This is some next-level shit!

Can you explain what, semantically, you're doing in the above query? Like...use tables with familiar names/concepts, e.g.

```sql
SELECT *
FROM users AS t1
LEFT OUTER JOIN notes AS t2
INNER JOIN note_tags AS t3
    ON (t2.id = t3.note_id) ON (t1.id = t2.user_id)
```

I'm not even sure if the above makes sense...can you explain? https://stackoverflow.com/questions/10334608/nested-inner-join-vs-inner-join-vs-where-correctness-performance-clarity-for

and

http://michaeljswart.com/2012/09/when-i-use-nested-joins/ The key idea is that peewee can do either 2 left joins on table **t1** or left join and inner join on table **t1**. The second variant is incorrect, because some rows are truncated (null values).
The first one is bad because 2 left joins cost much.
Doing nested join performs inner join on t2 -> t3 and then left join with the result (less rows for nested loop operation is done faster)
Finally i resolved this issue with doing a nested select query (despite it looks ugly).
Thanks for your ORM, it works great. Not every sql feature can be simply done with query builders, it's okay. Thanks for the explanation, that makes sense to me...interesting. I hadn't heard of doing such a thing, but yeah, makes sense I guess. I'm surprised that Postgres' query planner doesn't automatically optimize the naive LEFT/LEFT query, unless there's some semantic difference I'm not aware of...I suppose that there could be more result rows with the LEFT/LEFT than with the LEFT->INNER, so they're not exactly equivalent? One approach that may work using the existing APIs is to use a subquery, e.g.

```python
class Employee(Model):
    name = TextField()

class OrgChart(Model):
    employee = ForeignKeyField(Employee, backref='bosses')
    boss = ForeignKeyField(Employee, backref='reports')
    class Meta:
        primary_key = CompositeKey('employee', 'boss')

# Get employees and their boss.
Boss = Employee.alias('boss')
subq = (OrgChart
        .select(Boss.name.alias('boss'), OrgChart.employee.alias('emp'))
        .join(Boss, on=(OrgChart.boss == Boss.id))
        .alias('subq'))
query = (Employee
         .select(Employee.name, subq.c.boss)
         .join(subq, JOIN.LEFT_OUTER,
               on=(Employee.id == subq.c.emp))
         .order_by(Employee.name))

for empname, bossname in query.tuples():
    print empname, '=>', bossname
``` Given that it is possible to accomplish the same with existing APIs (albeit using a different construction) I think I'll pass on trying to implement this... I'm not sure of what a good API would even be.
Cannot find a way to do nested join, like:

SELECT *
FROM t1
    LEFT OUTER JOIN t2
         INNER JOIN t3 ON (t2.x_id = t3.id)
    ON (t1.y_id = t2.id)

This query is much faster than two left joins for my particular task (PostgreSQL). Subquery looks ugly.