IHP - Best way to combine and sort data from two tables into a single view?

Question

I have two tables:

Table: Posts
Columns: id, title, body, author_id, created_at

Table: Comments
Columns: id, post_id, body, author_id, created_at

I have the following action:

action MyAction:
 posts <- query @Post |> fetch
 comments <- query @Comment |> fetch

I want to fetch all the posts and comments together, combine and order them by created_at in a single view. What's the best way to do it in IHP inherently?

Answer 1

You can use collectionFetchRelated for that:

posts <-
    query @Post
        |> fetch
        >>= pure . map (modify #comments (orderBy #createdAt))
        >>= collectionFetchRelated #comments

You can find this code in the IHP documentation here.

Answer 2

EDIT: See Marc's answer: collectionFetchRelated is the correct function here

For a has many relationship such as this, the comments for a post can be queried using fetchRelated #comments. To do this for each post we can use mapM as follows.

postsWithComments <- query @Post 
           |> fetch
           >>= mapM (fetchRelated #comments)

To order by created_at, use the orderByDesc function for the QueryBuilder. We can apply this to the top level query directly, then modify the internal #comments query building using modify.

postsWithComments <- query @Post 
           |> orderByDesc #createdAt
           |> fetch
           >>= mapM (fetchRelated #comments . modify #comments (orderByDesc #createdAt))

See the IHP "relationships" docs for more info.