How to get count of related collection

[amitaibu]

I need something that could probably be called collectionFetchRelatedCount. Taking the Post and Comments example, let's say I just need to show Item title and comments count in a listing page. I wouldn't want to load all comments just for their number.

I think it might be general enough to be added to IHP - but not sure how to approach it 😸

[mpscholten]

One good way to solve this is by adding a comments_count : Int field to the posts table. Then whenever a new comment is created, update the comments_count of the related field.

Then you can use get #commentsCount post to access it from the view.

This also has the advantage that it keeps your application response times fast. Without having a comments_count field, the COUNT() queries would get slower over time as the comments in the database are growing. With the comments_count field the runtime is independent of the amount of comments that exist.

[amitaibu]

Right, that's indeed a valid option. I don't always love it though, as it could (due to software bugs) get out of sync, and there's the need of having code to maintain it.

But I guess for such cases I could issue a separate count query.

[amitaibu]

For posterity, here's how I've solved this. I've used an Item -> Bids relationship, but you can mentally replace it with Post -> Comments.

-- Controller/Item.hs

action ItemsAction = do
    (itemsQ, pagination) <- query @Item
            |> orderByDesc #createdAt
            |> paginateWithOptions (defaultPaginationOptions |> set #maxItems 10)

    items <- itemsQ
            |> fetch
            >>= collectionFetchRelated #bids

    -- Get the Item Ids.
    let itemIds = map (get #id) items

    -- Get the Count just for those Items.
    bidsCount :: [(Id Item, Int)] <- sqlQuery "SELECT item_id, count(*) FROM bids WHERE item_id IN ? GROUP BY item_id" (Only (In itemIds))

    render IndexView { .. }

-- View/Items/Index.hs

data IndexView = IndexView
    { items :: [Item]
    , bidsCount :: [(Id Item, Int)]
    }

showBidsCount bidsCount item =
    case bidsCount |> filter (\(itemId, _) -> itemId == get #id item) of
        ((_, count) : xs) -> [hsx| Item has {count} Bid(s). |]
        [] -> [hsx| Item has no Bids. |]

[mpscholten]

Looks good 👍

Quick note:

let itemIds = map (get #id) items

can be written short with the ids function:

let itemIds = ids items