I have two tables, topics and posts. Each post belongs to only one topic. There are 30 posts per page on a topic (paginated).
I am developing a search feature for my custom-programmed forum and I’ve tweaked the search query to return valuable results. I’m pleased with it. However, what’s difficult is linking to the post itself with the correct pagination (i.e. /forums/topic/1/60, where 1 is the topic ID and 60 is the pagination variable for that topic). Just because the search returns a post ID 12 that’s located in topic ID 10 doesn’t necessarily mean that post will be found on /forums/topic/10… it might be found on a paginated page like /forums/topic/10/90.
What I am wondering is… how can I efficiently calculate each individual post’s “sequence number” in its respective topic (sorted by time ASC) in my search query? If I know a post’s sequence number in the topic, I can certainly find what paginated page it’ll be located on. Basically, my search results need to return where a post is actually located in a topic (on what paginated page).
All of my attempts to efficiently do this have failed thus far. Any thoughts? Thanks in advanced!