Subtle changes, bugs and feedback

It's pretty neat... I calculate a whole set of results and the quantity of results, and then use a windowing function to reduce the data to just that which I'm going to show on the page, and then only calculate the unread/new status for the items I'm then going to show.

What this means is that the query has a predictable speed even if I'm asking for 25 items or 20,000.

Explained:

Here's the query that fetches updates:
https://github.com/microcosm-cc/microcosm/blob/master/models/updates.go#L329-L501

Here's the bit of interest:
https://github.com/microcosm-cc/microcosm/blob/master/models/updates.go#L349-L370

The inner select is dealing with all updates (in my case 179 pages of 25 items per page = 4,475 conversations/mentions/huddles).

The inner select has a COUNT(*) OVER() AS total to find the number of items.

Then the outer select is linked to:
https://github.com/microcosm-cc/microcosm/blob/master/models/updates.go#L498-L500

Which windows the results to whichever 25 items you are viewing on a page... and the SELECT is then calling the has_unread that flags the unread/new status.

has_unread looks like this:
https://github.com/microcosm-cc/microcosm/blob/master/db/migrations/20151112231934_is_unread.sql#L6-L348

And if you read through that you'll see it's a row level function... can't be JOIN'd to a table to get new things, it's a question you ask per row.

The reason for that is how unread status is stored... it's space efficient and item query efficient:
https://github.com/microcosm-cc/microcosm/blob/master/db/migrations/20150930224827_initial.sql#L4584-L4596

I store the last time you have read something, but it's an implicit hierarchy.

If you mark a forum as read, then any rows in that table for the items you have read are then deleted as storing just the forum and that implicitly meaning "all items in the forum are read" is more efficient.

To make "following" filterable I'd need to find a way to make the has_unread function a SQL view so that I could JOIN against it.