5

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
Michał Perłakowski
  • 88,409
  • 26
  • 156
  • 177
  • 1
    What have you tried and why didn't it work? Are you just looking for [this function](https://hackage.haskell.org/package/esqueleto-2.5.3/docs/Database-Esqueleto.html#v:sub_select)? – user2407038 Nov 07 '17 at 17:37
  • I didn't immediately realise this, but in addition to `sub_select`, there's also `subList_select`. – Jezen Thomas Jan 11 '18 at 09:16
  • @user2407038 That returns an `SqlExpr`, and is limited to a single row value at that. It wouldn't work. – JoL Oct 09 '18 at 16:11
  • @JezenThomas `subList_select` also returns an SqlExpr. Also wouldn't work. – JoL Oct 09 '18 at 16:11

1 Answers1

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.

JoL
  • 1,017
  • 10
  • 15
  • it's probably best to raise this [here](https://github.com/bitemyapp/esqueleto/issues). – Jezen Thomas Oct 10 '18 at 08:10
  • @JezenThomas I figured they'd already be aware of such a big gap in functionality. On checking your link, I found [issue #32](https://github.com/bitemyapp/esqueleto/issues/32) and [issue #70](https://github.com/bitemyapp/esqueleto/issues/70). That covers the needs raised here. – JoL Oct 10 '18 at 17:26