Determine page from post permalink in paginated forum

Question

The posts in my forum all have an individual permalink.

The posts are paginated and the users can sort them by date and rating.

When a permalink is accessed, the page the post resides on needs to be calculated due to the pagination.

The posts are stored with an auto incrementing id in a mysql innodb table.

At the moment I use the following for the calculation when the posts are sorted by their score (rating):

<?php

    // These variables originate from the corresponding uri segments
    // For example: http://domain.tld/topics/[ID]/[SLUG]/[POST_ID]
    $post_id  = $uri->segment(4);
    $topic_id = $uri->segment(2);

    $post_per_page = 10;

    $query = $db->query('SELECT id FROM topic_posts WHERE topic_id = ' . $topic_id . ' ORDER BY score desc');

    foreach ($query as $key => $post)
    {
        if ($post->id == $post_id)
        {
            $postOnPage = ceil(($key + 1) / $post_per_page);
            break;
        }
    }

However, the amount of posts will keep increasing and fetching all posts seems awkward.

For the date sorting I use the following query, but it's not working for the rating sorting as the post id's are then not in incrementing order:

SELECT CEIL((COUNT(*) + 1) / $posts_per_page) FROM topic_posts WHERE topic_id = $topic_id AND id < $post_id;

So... How can I avoid the php foreach loop and achieve the same with the db query?

I believe you should rethink your problem. Is it *that* important to have permalinks to posts using both order modes? I'd consider an option to simplify things and always use order-by-date when dealing with permalinks. — Leo Nyx, Oct 09 '13 at 17:23
Forcing the sort order to `date` was one thing I thought of to solve the problem. But, the replies are underneath each parent post containing a _permalink_ to the original post. So the sorting needs to be kept when such a permalink is visited. StackOverflow has the same functionality with their sorting _tabs_. But thanks for your suggestion anyway! — Markus Hofmann, Oct 09 '13 at 17:43

score 1 · Answer 1 · answered Oct 08 '13 at 20:46

It is possible to generate an artificial "row number" within the query, which could then be used to calculate the page the post would appear on. However, depending on your schema and table size this query might become very costly, so be sure to check the performance.

I think it is best described with a demonstration:

First the table structure and some test data:

mysql> CREATE TABLE foo (a VARCHAR(10));
mysql> INSERT INTO foo VALUES ("foo"), ("bar"), ("baz");

A query that returns all results in some order with their row numbers attached:

mysql> SELECT f.*, @rownum := @rownum+1 AS rank FROM foo f, (SELECT @rownum := 0) r ORDER BY f.a;
+------+------+
| a    | rank |
+------+------+
| bar  |    1 |
| baz  |    2 |
| foo  |    3 |
+------+------+
3 rows in set (0.00 sec)

You can then use this to select only a particular row:

mysql> SELECT * FROM (SELECT f.*, @rownum := @rownum+1 AS rank FROM foo f, (SELECT @rownum := 0) r ORDER BY f.a) t WHERE a = "foo";
+------+------+
| a    | rank |
+------+------+
| foo  |    3 |
+------+------+
1 row in set (0.00 sec)

Essentially, you are wrapping the inner numbering query within an outer query that only selects the wanted result row. As you can see the rank is still the same as in the "all results" case, so can now use this row number to calculate your result page.

If you want to paginate over only a subset of all records in the table (for example all posts in a particular forum), that corresponding WHERE clause goes into the inner SELECT where the ORDER BY is:

mysql> SELECT * FROM (SELECT f.*, @rownum := @rownum+1 AS rank FROM foo f, (SELECT @rownum := 0) r WHERE a != "baz" ORDER BY f.a) t WHERE a = "foo";
+------+------+
| a    | rank |
+------+------+
| foo  |    2 |
+------+------+
1 row in set (0.00 sec)

The downside is that it actually has to iterate over all records (well, all records that match your inner WHERE clause), so it becomes very slow with large tables.

**Thank you for your detailed answer, `+1`.** This is actually answering my question of how to move the php foreach loop into a mysql query. However, the main thing I'm looking for is performance with large amounts of database data. You're saying yourself that this solution is resource intensive with large tables. Can I somehow change my concept of storing or fetching the posts data to reduce the heavy / repeated processing thereby increasing the overall performance? *BTW:* I've two tables for the forum posts - 1) topics for all topic meta acting as pivot 2) topic_posts, containing all posts. — Markus Hofmann, Oct 09 '13 at 11:22

score 1 · Accepted Answer · answered Oct 09 '13 at 15:27

I just had an idea for a completely different approach, so I add it as a second answer instead of editing the first.

For this to work predictably, you always have to include at least one unique column into your sorting as a tie breaker. I assume that many of your posts will actually have the same rating, so maybe you should for example ORDER BY score DESC, id DESC to additionally order posts with the same rating as latest first (I think this might make sense for a forum anyway).

Then for the sort order mentioned above, you can get the number of posts that sort BEFORE the post in question with the following query:

SELECT COUNT(1) FROM topic_posts
  WHERE topic_id = $topic_id
        AND ((score > $post_score) OR (score = $post_score AND id > $post_id));

This is a query that you can optimize using indizes etc. as needed.

I like your enthusiasm :-) This seems to bring the solution forward. I'll have a look at it and tell you if it helped. — Markus Hofmann, Oct 09 '13 at 17:44
Great! This works as expected. BTW: `COUNT(1)` is actually the same as `COUNT(*)` - More details here http://stackoverflow.com/q/1221559/2493918. — Markus Hofmann, Oct 10 '13 at 08:31

Determine page from post permalink in paginated forum

2 Answers2