"Aggregate by" Haskell monoids (mondoial functors?)

[2piix]

I've found a common pattern in my programming with Esqueleto, and I'm curious if it would be possible to fold generic solution into Esqueleto. I would be willing to do this, with some guidance (I'm not sure if it's even possible, hence this rfc)

Consider a multi-to-multi Persistent model defined by:

Person
  name Text

Committee
  name Text

PersonCommittee
  personId PersonId
  committeeId CommitteeId

I might also have a "view" layer, like so:

data PersonView = PersonView { person :: Person, committees :: [Committees] }

One common task in my day-to-day work is to populate such views. A query might look something like:

\pid -> (select $ from $ \(person `InnerJoin` pxc `InnerJoin` committee) -> do
  on (committee ^. CommitteId ==. pxc ^. PersonCommitteeCommitteeId)
  on (pxc ^. PersonCommitteePersonId ==. person ^. PersonId)
  where_ (person .^ PersonId ==. val pid))
  return (person, committee)

In order to populate a PersonView, I have to "manually" perform a group by on the Haskell side -- pull the head out of the result set and fmap to aggregate the committee field into a list. It occurs to me that I will be doing this a lot, and that this could generalize to other monoidal functors.

Comments? Am I just approaching this the wrong way?

[meteficha]

I don't think you're approaching the problem the wrong way. Having to do these groupings manually is how SQL works, and esqueleto is giving you the same.

This is a non-trivial problem to solve in a general fashion. I mean, we could of course provide some function of type [(Entity a, b)] -> [(Entity a, [b])], but you'd still need to manually apply it. Also, it would become uglier if you needed to do in three or more levels. For example, if you had:

Continent
  name Text

Country
  name Text
  at ContinentId

State
  name Text
  at CountryId

City 
  name text
  at StateId

Street
  name Text
  at CityId

and needed to have something of type:

[(Entity Continent, [(Entity Country, [(Entity State, [(Entity City, [Entity Street])])])])]

Another approach would be having something inside esqueleto's DSL (e.g., a GroupedInnerJoin, or some sort of function you could apply on the SqlExprs beforing returning). It may be easier to have something on this level as a user of esqueleto, but i imagine it would be harder as a developer of esqueleto.

In sum, this is an interesting problem but it would require some good thought to solve it!

PS: I know you've just posted an example, but given your PersonView type I'd expect to see outer joins in order to fetch people who aren't in any comittees, too.

[2piix]

Okay! I'm going to put some hours into this in a few weeks. For now, I'll do it the totally manual way. But it's time to get my product out to market. :)