Select from subquery and join on subquery in Esqueleto

Question

How can I do select ... from (select ...) join (select ...) in Esqueleto?

I'm aware that I can use rawSql from Persistent, but I'd like to avoid that.

For the record, here is the full query:

select q.uuid, q.upvotes, q.downvotes, count(a.parent_uuid), max(a.isAccepted) as hasAccepted
from
  (select post.uuid, post.title, sum(case when (vote.type = 2) then 1 else 0 end) as upvotes, sum(case when (vote.type = 3) then 1 else 0 end) as downvotes
    from post left outer join vote on post.uuid = vote.post_id
    where post.parent_uuid is null
    group by post.uuid
    order by post.created_on desc
  ) q
left outer join
  (select post.parent_uuid, max(case when (vote.type = 1) then 1 else 0 end) as isAccepted
    from post left outer join vote on post.uuid = vote.post_id
    where post.parent_uuid is not null
    group by post.id
  ) a
on a.parent_uuid = q.uuid
group by q.uuid
limit 10

Answer 1

I got here because I had the same question. I imagine the thing we want would be something like:

fromSelect
  :: ( Database.Esqueleto.Internal.Language.From query expr backend a
     , Database.Esqueleto.Internal.Language.From query expr backend b
     )
  => (a -> query b)
  -> (b -> query c)
  -> query c

Unfortunately, from looking at Database.Esqueleto.Internal.Sql.FromClause:

-- | A part of a @FROM@ clause.
data FromClause =
    FromStart Ident EntityDef
  | FromJoin FromClause JoinKind FromClause (Maybe (SqlExpr (Value Bool)))
  | OnClause (SqlExpr (Value Bool))

I don't think there's any support for this in Esqueleto. It only seems to support simple table names and joins with on-clauses that have a boolean expression. I imagine the hardest part of adding support for this is handling table and column name aliases (as sql clause), since ^. expects an expr (Entity val) and an EntityField val typ. Simplest way is to change that to using String or Text for both operands, but that's not very type-safe. I'm not sure what the best option would be implementation-wise to make that type safe.

EDIT: Probably best to forget ^. and have fromSelect generate the aliases when providing the returned values of its first parameter as the arguments of its second parameter. Types would probably have to be altered to make room for these aliases. This is only contemplating from subqueries, not joins. That's another problem.