Convert joins into nested tuples

[saurabhnanda]

# Returns (User, [Post])
User.includes(:posts).find(1) 

# Returns [(User, [Post])]
User.includes(:posts).where("created_at > something")

# Returns [(User, [(Post, [Comment])])]
User.includes(:posts => :comments)

# Returns [(User, [(Post, [Comment], User)])]
User.includes(:posts => [:comments, :moderated_by])

# Returns [(User, [(Post, [(Comment, [Vote])], User)])]
User.includes(:posts => [{:comments => :votes}, :moderated_by])

# Returns [(User, User, [(Post, [(Comment, [Vote])], User)])]
User.includes([:referred_by, :posts => [{:comments => :votes}, :moderated_by]])

[saurabhnanda]

How do I edit the description? Here's what I wanted to say:

While Esqueleto adds type-safe joins on top of Persistent, and they seem to be working beautifully, I still end up having to write a lot of boilerplate code to convert the results into something usable in my app.

For example, I spent half the day today trying to do this following:

Download has-many Files File has-many URLs

Download leftOuterJoin File leftOuterJoin URL ==> [(Entity Download, Entity File, Entity URL)]

However I needed a data structure that mirrored my DB relationships ==> [(Entity Download, [(Entity File, [Entity URL])])]

Spent half a day fiddling with maps, traversables, folds, and whatnot.

Doesn't anyone else feel the need for this? Am I doing something wrong?

[tomjaguarpaw]

Do you want this? I suppose there are ways of making it neater but it should be a start.

import Lens.Micro
import Lens.Micro.Extras
import Data.Map hiding (foldl')
import Data.List (foldl')

data Entity a = Entity deriving (Ord, Eq)
data Download
data File
data URL

groupBy :: Ord b => (a -> b) -> (a -> c) -> [a] -> [(b, [c])]
groupBy f g = toList . foldl' (\m kv -> insertWith (++) (f kv) [g kv] m) empty

nest :: [(Entity Download, Entity File, Entity URL)]
     -> [(Entity Download, [(Entity File, [Entity URL])])]
nest = over (traversed._2) (groupBy (view _1) snd)
       . groupBy (view _1) (\(_, y, z) -> (y, z))

[saurabhnanda]

Thanks. I'll need to grab my editor to completely understand your code (on my phone right now). It's too short to be true!

I ended up using strict maps for grouping and got stuck after Map (Entity Download) [Map (Entity File) (Entity URL)]

Any way to generalise this code so that it can work for any combination of 1:1, 1:many, and many:many relationships mentioned in the issue description? For example:

nestedSelect :: [(Entity User, Entity Referrer)]
nestedSelect :: [(Entity User, [Entity Post])]
nestedSelect :: [(Entity User, Entity Referrer, [(Entity Post, [Entity Comment])])]

Can the same function be polymorphic in its return type as the examples given above? Or is it better to pass in the expected "shape of associations" as an argument to the function?

The bigger question in my head is, how come this kind of functionality is not already there? Is there a way to write apps without hitting this problem? Am I using and anti-pattern?

[tomjaguarpaw]

There's no sane way of making it polymorphic in the return type. Different combinations of groupBy will do what you want though.

[tomjaguarpaw]

Here's a suggestion. You can probably make this look even nicer somehow.

import Lens.Micro
import Lens.Micro.Extras
import Data.Map
import Data.List hiding (groupBy)

data Entity a = Entity deriving (Ord, Eq)
data Download
data File
data URL
data Referrer
data Comment  
data User
data Post

groupBy :: Ord b => (a -> b) -> (a -> c) -> ([c] -> d) -> [a] -> [(b, d)]
groupBy f g h = toList
                . Data.Map.map h
                . Data.List.foldl' (\m kv -> insertWith (++) (f kv) [g kv] m) empty

nest :: [(Entity Download, Entity File, Entity URL)]
     -> [(Entity Download, [(Entity File, [Entity URL])])]
nest = groupBy (view _1) (\(_, y, z) -> (y, z))
         (groupBy (view _1) snd id)

nestedSelect1 :: [(Entity User, Entity Referrer, Entity Post, Entity Comment)]
              -> [((Entity User, Entity Referrer), [()])]
nestedSelect1 = groupBy (\(u, r, _, _) -> (u, r)) (const ()) id

nestedSelect2 :: [(Entity User, Entity Referrer, Entity Post, Entity Comment)]
              -> [(Entity User, [Entity Post])]
nestedSelect2 = groupBy (\(u, _, _, _) -> u) (\(_, _, p, _) -> p) id

nestedSelect3 :: [(Entity User, Entity Referrer, Entity Post, Entity Comment)]
              -> [((Entity User, Entity Referrer), [(Entity Post, [Entity Comment])])]
nestedSelect3 = groupBy (\(u, r, _, _) -> (u, r)) (\(_, _, p, c) -> (p, c))
                  (groupBy (\(p, _) -> p) (\(_, c) -> c) id)

[saurabhnanda]

@tomjaguarpaw I've finally to managed to grok your first groupBy function and am wondering why I couldn't think of it. Do you know all these generic functions by heart? I didn't even know that insertWith existed! I know how to write this in a mutable language, but if you avoid mutations, you probably need an insertWith to be able to write this as a quick one-liner.

Also, if I've understood how groupBy is working, do we really need to defined the family of nestedSelect functions as suggested by you? Can't we do that via recursion and make it work for any level of nesting? As long as it follows this pattern, of course.

Also, does Map.toList preserve the key-insertion order? Else any orderBy clauses will lose their effect.

PS: Thank you for taking out your time to respond on the Esqueleto issue tracker, even though you have nothing to do with the project :)

[saurabhnanda]

And I'm beginning to understand why experience Haskeller's wouldn't be bothering with this stuff. It can be implemented as a one-liner. However, I still think it stumps newbies like me.

[saurabhnanda]

So, is the second groupBy an alternative approach to recursion? Which is why the third transformation function h? Or probably I didn't understand the second approach at all.

[tomjaguarpaw]

I've been doing database stuff for quite a long time now and I've written something like this before.

I wouldn't call what it happening here "recursion". The transformation function h just allows you to chain another groupBy statement to process the groups that are produced by the first one. There's no way to do this for any of level of nesting with just one function, but nesting the groupBy calls should get you where you need to be.

[tomjaguarpaw]

@saurabhnanda Let's continue this here: https://github.com/tomjaguarpaw/haskell-opaleye/issues/189