IHP 1.3 is out now!

Single-query replacement for collectionFetchRelated?

peterbuckley17 PRO

My understanding of the collectionFetchRelated method is that if I query for 100 posts, they will be returned in one query, and then if I want their comments the >>= collectionFetchRelated will then run 100 more queries to return each of their associated comments.

My project is at the point where there is enough of this type of activity that things are becoming slow because of this -- in some cases, I'm fetching collections off collections, resulting in thousands of sequential queries.

I can construct a query with raw SQL that returns all of the data I want - the problem is then converting this into an appropriate type.

I could create a custom function each time I need to construct a new query, manually creating a post-and-comment type that is a FromRow instance, and then manually decoding each of those into something like a (Post, Comment) tuple - or further, a (Post, Comment, ThreadComment) tuple, etc.


Post {id, userId, , title, body}, Comment {id, userId, body} -->

PostAndComment {postId, postUserId, postTitle, postBody, commentId, commentUserId, commentBody} -->

let thisPost = Post {(get #postId postAndComment), (get #postUserId postAndComment), .... } let thisComment = Comment {(get #commentId postAndComment)... }

(thisPost, thisComment)

It seems like there should be an easier way to do this. Is there a baked-in way to do this with IHP, or does anyone know of a pre-existing Haskell library I might be able to use?


marc PRO

Here's another possible workaround for the problem that doesn't require custom data structures and has a constant count of queries:

posts  <- query @Post |> fetch
comments <- query @Comment
    |> filterWhereIn (#postId, ids posts)
    |> fetch
let postsWithComments :: [Include "comments" Post] = posts |> map (assignComments comments)

and we can define the assignComments helper function like this:

assignComments :: [Comment] -> Post -> Include "comments" Post
assignComments comments post = post |> updateField #comments postComments
        postComments = comments |> filter (\comment -> get #postId comment == get #id post)