4

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?

Varun Rajput
  • 235
  • 1
  • 7

2 Answers2

4

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.

Marc Scholten
  • 1,351
  • 3
  • 5
  • Is it possible to combine this with pagination? E.g. if I want to order by number of comments, but have the posts paginated. – unhammer Mar 31 '23 at 07:26
  • 1
    Like this? ```haskell (postsQuery, pagination) <- query @Post |> orderBy #commentsCount |> paginate posts <- postsQuery |> fetch >>= pure . map (modify #comments (orderBy #createdAt)) >>= collectionFetchRelated #comments ``` – Marc Scholten Apr 01 '23 at 09:00
  • 1
    Aha, nice! I ended up with a handy helper `onRelated getter modifier = pure . map (modify getter modifier)` for this, so it looks like `fetch >>= onRelated #comments (orderBy #createdAt) >>= collectionFetchRelated #comments` – unhammer May 14 '23 at 09:39
  • This looks really nice, we should add this to IHP as a standard helper IMO :) – Marc Scholten May 15 '23 at 15:48
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.

Zachary Wood
  • 116
  • 3