Porting my blog for the second time, render posts part 4

This is post #26 of my series about how I port this blog from Blogengine.NET 2.5 ASPX on a Windows Server 2003 to a Linux Ubuntu server, Apache2, MySQL and PHP. A so called LAMP. The introduction to this project can be found in this blog post /post/Porting-my-blog-for-the-second-time-Project-can-start.

I am just about to try making my query for getting a blog post. MySQL gets a soft starter:

mysql≻ SELECT c.ID AS CurrentID FROM Post c WHERE c.Slug = 'Girl-with-big-eyes';
+-----------+
| CurrentID |
+-----------+
|       418 |
+-----------+
1 row in set (0.01 sec)

When this all works the slug is the identifying part of the URL by which the page is requested. I see that some slugs got extra undesirable characters in them. I will need to clean up those later or change my Perl program to eliminate them.

mysql≻ SELECT c.ID AS CurrentID, p.ID AS PrevID FROM Post c, Post p WHERE c.Slug = 'Girl-with-big-eyes' and c.ID = p.NextID;

+-----------+--------+
| CurrentID | PrevID |
+-----------+--------+
|       418 |     58 |
+-----------+--------+
1 row in set (0.00 sec)

It is a bit brain juggling to realize that the previous post of the current post is that one post having the current as the next post. Is it not ironic that this happens to me who also needs to rewrite the rewrite instructions to be able to get rewrite to work. Then we do the reverse for the next post.

mysql≻ SELECT c.ID AS CurrentID, p.ID AS PrevID, n.ID AS NextID FROM Post c, Post p, Post n WHERE c.Slug = 'Girl-with-big-eyes' and c.ID = p.NextID and c.ID = n.PrevID;
+-----------+--------+--------+
| CurrentID | PrevID | NextID |
+-----------+--------+--------+
|       418 |     58 |    144 |
+-----------+--------+--------+
1 row in set (0.00 sec)

And now we just need to verify that this is correct. How do we do that?

mysql≻ select ID, PublishedOn, Slug from Post order by PublishedOn desc;
+-----+---------------------+--------------------+
| ID  | PublishedOn         | Slug               |
+-----+---------------------+--------------------+
| 144 | 2011-06-28 13:42:00 | Fruits             |
| 418 | 2011-06-28 10:59:00 | Girl-with-big-eyes |
|  58 | 2011-06-27 11:03:00 | Towel              |
+-----+---------------------+--------------------+

It works for the situation that there exists a previous and a next post.

These two items are the first two blog posts of the blog:

+-----+--------+--------+---------------------+--------------------------+
| ID  | NextId | PrevID | PublishedOn         | Slug                     |
+-----+--------+--------+---------------------+--------------------------+
| 173 |    358 |    252 | 2011-06-10 16:10:00 | Jasmine-Tea              |
| 252 |    173 |   NULL | 2011-06-10 16:00:00 | My-first-blog-post-ever! |
+-----+--------+--------+---------------------+--------------------------+

Here is the result of running the query on "My-first-blog-post-ever!":

mysql≻ SELECT c.ID AS CurrentID, p.ID AS PrevID, n.ID AS NextID FROM Post c, Post p, Post n WHERE c.Slug = ’My-first-blog-post-ever!’ and c.ID = p.NextID and c.ID = n.PrevID;
Empty set (0.00 sec)

One solution could be that I made the blog into a ring. The first entry points to the last entry and vice versa but I don’t like that option. It is a simplistic way of doing it but I don’t like it. It is just a question of taste. The first post has no previous and the last post got no next. That feels more right.

So when my query produce an empty set then there are no records that fulfills the condition. So how can we correct this? It is a very strict situation we ask for here. It is too strict. Two comparisons need to be fulfilled for the database to provide a result, namely

c.ID = p.NextID

and

c.ID = n.PrevID

but if we are providing a query for the first blog then there is no previous. Either the previous post points to the current post or the current post has no link to a previous post. Like this:

c.ID = p.NextID OR c.PrevID IS NULL

The other situation is when we have the absolutely first post when the next post points to the current post or the current post is not pointing to a next post.

c.ID = n.PrevID OR c.NextID IS NULL

This is the entire query with this new construction when requesting the Girl with the big eyes post:

SELECT c.ID AS CurrentID, p.ID AS PrevID, n.ID AS NextID FROM Post c, Post p, Post n WHERE c.Slug = ’Girl-with-big-eyes’ and (c.ID = p.NextID OR c.PrevID IS NULL) and (c.ID = n.PrevID OR c.NextID IS NULL);
+-----------+--------+--------+
| CurrentID | PrevID | NextID |
+-----------+--------+--------+
|       418 |     58 |    144 |
+-----------+--------+--------+
1 row in set (0.00 sec)

So what happens when I request the first post ever with the same query?

mysql≻ SELECT c.ID AS CurrentID, p.ID AS PrevID, n.ID AS NextID FROM Post c, Post p, Post n WHERE c.Slug = ’My-first-blog-post-ever!’ and (c.ID = p.NextID OR c.PrevID IS NULL) and (c.ID = n.PrevID OR c.NextID IS NULL);
+-----------+--------+--------+
| CurrentID | PrevID | NextID |
+-----------+--------+--------+
|       252 |    319 |    173 |
|       252 |    318 |    173 |
|       252 |    230 |    173 |
...
|       252 |    250 |    173 |
|       252 |    123 |    173 |
|       252 |    165 |    173 |
+-----------+--------+--------+
433 rows in set (0.00 sec)

At first this looks so strange! But we get what we ask for here because next and current they are uniquely specified while the previous is unclear so the database return all records it can find that match the previous. The FROM part of the query includes Post p so we will get it returned to us. Can we exclude p? If we exclude it in the FROM part of the query we cannot have it in any other part either. How can we know what to ask for before we asked for it? There not so much to do about the list behind the FROM part of the query.

There is another way. We can adjust what we select based on how the current post looks like. For example when the current post has no link to a previous post then we select nothing otherwise we select the ID of the previous post. That looks like this:

CASE WHEN c.PrevID IS NULL THEN NULL ELSE p.ID END AS PrevID

Similarly when the current post has no link to a next post then we select nothing otherwise we select the ID of the next post. That looks like this:

CASE WHEN c.NextID IS NULL THEN NULL ELSE n.ID END AS NextID

This is the result when requesting the First-blog-post-ever!:

mysql≻ SELECT c.ID AS CurrentID, CASE WHEN c.PrevID IS NULL THEN NULL ELSE p.ID END AS PrevID, CASE WHEN c.NextID IS NULL THEN NULL ELSE n.ID END AS NextID FROM Post c, Post p, Post n WHERE c.Slug = ’My-first-blog-post-ever!’ and (c.ID = p.NextID OR c.PrevID IS NULL) and (c.ID = n.PrevID OR c.NextID IS NULL);
+-----------+--------+--------+
| CurrentID | PrevID | NextID |
+-----------+--------+--------+
|       252 |   NULL |    173 |
|       252 |   NULL |    173 |
|       252 |   NULL |    173 |
...
|       252 |   NULL |    173 |
|       252 |   NULL |    173 |
|       252 |   NULL |    173 |
|       252 |   NULL |    173 |
+-----------+--------+--------+
433 rows in set (0.00 sec)

When we run the query now we can still see that Post p is asked for in the FROM section of the query but the actual values are hidden. From here we can introduce the DISTINCT qualification so that all extra rows are reduced to one row.

≺pre≻mysql≻ SELECT DISTINCT c.ID AS CurrentID, CASE WHEN c.PrevID IS NULL THEN NULL ELSE p.ID END AS PrevID, CASE WHEN c.NextID IS NULL THEN NULL ELSE n.ID END AS NextID FROM Post c, Post p, Post n WHERE c.Slug = ’My-first-blog-post-ever!’ and (c.ID = p.NextID OR c.PrevID IS NULL) and (c.ID = n.PrevID OR c.NextID IS NULL);
+-----------+--------+--------+
| CurrentID | PrevID | NextID |
+-----------+--------+--------+
|       252 |   NULL |    173 |
+-----------+--------+--------+
1 row in set (0.00 sec)

Is that an awesome beautiful query? Here is the result of the last blog post:

mysql≻ SELECT DISTINCT c.ID AS CurrentID, CASE WHEN c.PrevID IS NULL THEN NULL ELSE p.ID END AS PrevID, CASE WHEN c.NextID IS NULL THEN NULL ELSE n.ID END AS NextID FROM Post c, Post p, Post n WHERE c.Slug = ’Here-is-how-to-sew-a-pouch-for-a-Samsung-Galaxy-S4-with-the-Otterbox’ and (c.ID = p.NextID OR c.PrevID IS NULL) and (c.ID = n.PrevID OR c.NextID IS NULL);
+-----------+--------+--------+
| CurrentID | PrevID | NextID |
+-----------+--------+--------+
|       319 |    158 |   NULL |
+-----------+--------+--------+
1 row in set (0.00 sec)

So what do I have now? I have a query that works but I have the feeling that the database will bring up every record in the database just to find out they give exactly the same result and then return that. Will that be efficient? Normally I have the opinion that just let’s do it and then we have to run a profiler to find out if it is efficient enough. I am still rather new to this MySQL thing but I think there are ways to go with indexes etc to make things efficient. I could also use LIMIT instead of DISTINCT. That would probably be more efficient.

Or I can go another way? Maybe you recall the idea with the ring situation that I mentioned. Suppose all the posts where indeed arranged in a ring. In that layout the last posts next ID would point to the first post and the first posts previous ID would link to the first post. With this construction the simpler form of query would always work. Earlier I said that I don’t like that and I still don’t. But that could be solved in the user interface by not showing the next link for the most recent post. To do that I would need position type field in a Post record telling if the Post is the first or last.

So... this is what I will do. I add a PositionType to the Post. It is 0 for in-between posts. It is 1 for the first post and it is 2 for the last post.

The Perl program needs to set the values correctly. Here is the output after I changed it:

i: 0    Current:319,    Next: 252,      Prev:158,       On:2015-04-06 20:57:00, PositionType: 1
i: 1    Current:158,    Next: 319,      Prev:278,       On:2015-02-23 14:11:00, PositionType: 0
i: 2    Current:278,    Next: 158,      Prev:269,       On:2015-01-29 00:20:00, PositionType: 0
...
i: 430  Current:358,    Next: 240,      Prev:173,       On:2011-06-10 23:13:00, PositionType: 0
i: 431  Current:173,    Next: 358,      Prev:252,       On:2011-06-10 16:10:00, PositionType: 0
i: 432  Current:252,    Next: 173,      Prev:319,       On:2011-06-10 16:00:00, PositionType: 2

Then later on when I make it possible to add new posts the routines for doing that need to alter these values, otherwise it will go wrong.

# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-render-posts-part-4.aspx
# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-render-posts-part-3.aspx
my $sth = $dbh -≻ prepare(’SELECT ID, PublishedOn FROM Post ORDER BY PublishedOn DESC’);
$sth-≻execute( );
my @row;
my @arrayIDs = ();
my @arrayPublishedOn = ();
my $strPrevID = "";
while (@row = $sth-≻fetchrow_array)
{
	push(@arrayIDs, $row[0]);
	push(@arrayPublishedOn, $row[1]);
}

for (my $i = 0; $i ≺ scalar(@arrayIDs); $i++)
{
	my $idNext = -1;
	my $idPrev = -1;
	my $iPositionType = 0;
	
	if ($i == 0)
	{
		$idNext = $arrayIDs[-1 + scalar(@arrayIDs)];
		$iPositionType = 1;
	}
	if ($i ≻ 0)
	{
		$idNext = $arrayIDs[$i-1];
	}
	if (($i + 1) ≺ scalar(@arrayIDs))
	{
		$idPrev = $arrayIDs[$i+1];
	}
	if ($i == -1 + scalar(@arrayIDs))
	{
		$idPrev = $arrayIDs[0];
		$iPositionType = 2;
	}

	print "i: " . $i . "	Current:" . $arrayIDs[$i] . ",	Next: " . $idNext . ", 	Prev:" . $idPrev . ",	On:" . $arrayPublishedOn[$i] . ", PositionType: " . $iPositionType ."
";
	$dbh-≻do(’UPDATE Post SET NextID = ?, PrevID = ?, PositionType = ? WHERE ID = ?’ , undef, $idNext, $idPrev , $iPositionType, $arrayIDs[$i]);
}

Here above is the updated perl routine updating the PrevID and NextID and the PositionType.

And now this simple query will give the correct result:

mysql≻ SELECT c.ID AS CurrentID, c.PositionType, p.ID AS PrevID, n.ID AS NextID FROM Post c, Post p, Post n WHERE c.Slug = ’My-first-blog-post-ever!’ and c.ID = p.NextID and c.ID = n.PrevID;
+-----------+--------------+--------+--------+
| CurrentID | PositionType | PrevID | NextID |
+-----------+--------------+--------+--------+
|       252 |            2 |    319 |    173 |
+-----------+--------------+--------+--------+
1 row in set (0.00 sec)

I can agree on that the earlier more complex query is divine. This here is just a simple query, an efficient boring query that just hoist up the correct answer. A programmer wants to make divine constructions noone understands. But a good programmer wants to make constructions that others can understand. So this will work.

Now I can go on and use this query from the PHP to present the page. But that is for the next time.