LATERAL constant string doesn't have JOIN inside

[lephuongbg]

JOIN.LATERAL constant string currently only contains 'LATERAL', which causes syntax error with Postgresql. It should be either 'INNER JOIN LATERAL' or simply 'JOIN LATERAL'

JOIN.LEFT_LATERAL is correct though ('LEFT JOIN LATERAL').

[coleifer]

I'm following the examples provided here in pg's doc: https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL

I suppose we probably also need to support an inner lateral join, too, though I'm not sure how that would be used.

[coleifer]

The way I'm currently going to address this is to add the lateral() method to the select query class.

This gives you the opportunity to use joins more naturally, and signifies only that the subquery being joined should be interpreted as a lateral join, which I think is more "correct" anyways.

Example:

# Here we use a subquery to get the most-recent two tweets by each user.
subq = (Tweet
        .select(Tweet.content, Tweet.timestamp)
        .where(Tweet.user == User.id)
        .order_by(Tweet.timestamp.desc())
        .limit(2)
        .lateral())

# Here we do an inner join on the (lateral) subquery of tweets.
query = (User
         .select(User, subq.c.content)
         .join(subq, on=True)
         .order_by(subq.c.timestamp))

[lephuongbg]

The example that you referred to with only LATERAL is actually multiple FROM clause, which needs a comma between them.

Above clause is equivalent to INNER JOIN LATERAL or just JOIN LATERAL.

I do like the current API of LATERAL though (.join(sq, join_type=JOIN.LATERAL)), and I noticed there is also CROSS JOIN LATERAL too.