FtheBuilder FtheBuilder - 4 months ago 15
SQL Question

Esqueleto count inside select

I have the following

Entities
:

Group
name Text

GroupUser
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
Esqueleto
?

Answer

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)
Comments