Support LATERAL joins

[ashleyw]

http://www.postgresql.org/docs/9.3/static/queries-table-expressions.html#QUERIES-LATERAL
http://blog.heapanalytics.com/postgresqls-powerful-new-join-type-lateral/

It would be immensely valuable when combined with subquery, for instance getting the most recent comments on a post:

post = Repo.one(
  from p in Post,
  where: p.id == 123,
  lateral_join: cs in subquery(
    from c in Comment,
    order_by: [desc: c.inserted_at],
    where: c.post_id == p.id,
    limit: 5
  ),
  preload: [comments: cs]
)

It doesn't appear that using INNER JOIN LATERAL is possible even with a fragment, since join wraps the fragment in additional parentheses (i.e. INNER JOIN (LATERAL (SELECT …))), which isn't valid AFAIK.

[josevalim]

If you would like to send a PR, it would be appreciated. :)

[ashleyw]

I dived in and took a look, but I don't think I'm capable of implementing this. I've only been using Elixir for a few months. :)

[josevalim]

@ashleyw no problem, we will tackle this after 2.0 is out, unless someone decides to handle it first. :)

[aphillipo]

I started having a look into this yesterday and I came up with two ways to do it:

1. Change the Join Qualifier (qual) to include the join type (INNER), the JOIN keyword and optionally LATERAL, which would make the atom something like: :inner_join_lateral :left_join_lateral - we would need to change the adaptors to support this, removing " JOIN " which makes the code less explicit.

2. Change the %JoinExpr to add a :join_qual_suffix part of the struct, this would support other possible queries than LATERAL in the <join_qualifier> JOIN <:join_qual_suffix> should they exists in the future. However implementing this seems like there are a hell of a lot of moving parts to change; this would turn all of the join building macros from /7 to /8 and I started getting lost in the changes.

Obviously it's a classic trade off:

1. is simpler but feels very inflexible and if there is anything that adds things it means more entries for each supported join type

2. gives extra possibilities for the future but if I'm honest seems difficult!

Let me know if you want me to push through with one of these and maybe I can ask questions if I get stuck! Thanks!

EDIT: 3) Looking at the definition of LATERAL it seems to only apply to subqueries so maybe the simplest way would be to change the params of a subquery( , lateral: true) which would be even simpler and not touch any of the join code. I think that is the one!

[ashleyw]

Totally out of my depth here.. but am I right in thinking subquery needs adapting to allow passing in the parent variable? That's what I struggled with when I attempted to implement this.

[josevalim]

@ashleyw that would be very tricky to implement indeed. Subqueries today cannot interact with elements outside of the query. Although you can use fragments when writing your lateral joins (and that should work fine).

[bernardoamc]

Forgot to reference this issue in the PR.

[ukrbublik]

Is there any workaround to use subquery with latter joins?
Fragments for latter joins makes not much sense :(

Why I can't use binding p inside subquery as in example in first post?

post = Repo.one(
  from p in Post,
  where: p.id == 123,
  lateral_join: cs in subquery(
    from c in Comment,
    order_by: [desc: c.inserted_at],
    where: c.post_id == p.id,
  )
)