Default behaviour of assocSelect in conjunction with has?

[saurabhnanda]

Just playing around with pg-orm for the first time. Created the typical user-post-comments data model, and was surprised with the following:

userPosts :: Association User Post
userPosts = has

postUser :: Association Post User
postUser = belongsTo

main = do
  -- snip
  putStrLn "Finding user with posts..."
  r <- dbSelect conn $ assocSelect userPosts
  putStrLn $ show r

  -- OUTPUT: []

It seems that assocSelect uses an inner-join, which means that if a user doesn't have any posts, the entire row is not returned. Is this intended?

What's the easiest way to select a user, along with posts, but still return the user's row irrespective of whether he/she has posts, or not?

[alevy]

High level answer is, without fetching the users and posts separately, I'm not sure --- let's workshop it!

I don't think there is an implemented combinator for doing that.

The common way we (in the MemCachier code base that is so far the primary user :) ) end up doing these sorts of things is a two step:

1. Get users in question
2. Fetch associations for each user

Though, if you wanted, say, to display all users and a list of top posts for each user (if they have posts) on a single page, this pattern clearly kind of sucks.

One tricky part is it's not clear what the right type would be for a collection returned from an outer join.

assocSelect currently returns a (DBSelect (a :. b)) (effectively a list of pairs (a, b)). I think what you want would return something closer to (DBSelect (a :. [b]))(a list of pairs(a, [b])) where [b]may be empty. I'm not sure how to build that list ofbfrom the rows returned by postgresql-simple. It _feels_ possible, maybe with an underlyingDBSelect (a :. Maybe b)` or something, but we'd need to try it out.