FtheBuilder FtheBuilder - 1 year ago 67
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

Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download