FtheBuilder FtheBuilder - 3 months ago 10x
SQL Question

Esqueleto count inside select

I have the following


name Text

user UserId
group GroupId

and I would like to do a query like this:

select g.* /* Everything from g */
, count(gu.id) groupUsersCount
from Group g
left outer join GroupUser gu on gu.groupId = g.id
group by g.id

Can it be done with


The esqueleto docs for groupBy contain good examples of how to use it.

Moreover, by reading through the Getting Started section, you'll see several example of queries including the equivalent of table.*:

do people <- select $
             from $ \person -> do
             return person

Putting the two together means something like this should work:

select $ from \(g `LeftOuterJoin` gh) -> do
  on (gu ^. GroupId ==. g ^. Id)
  groupBy (g ^. Id)
  return (g, countRows)