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

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

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?

Solution

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.