I started with SQL Server because that’s what I know, but I knew that wouldn’t be my final solution. Like many of you I’m sure, my blog runs on WordPress and MySQL. To make matters worse, my host is stuck on MySQL 5.7, which is missing some major conveniences I’ve been spoiled with in SQL Server, and that have been added to MySQL in the meantime – most notably, common table expressions (CTEs) and ROW_NUMBER().
In this post, I’ll show how I worked around that. But let’s start from the beginning, by starting from the end.
The End Goal
Think about the page (or sidebar) where your blog’s monthly archives (or any monthly breakdown of events, really) are typically represented in this simple way:
A while back, I made a change to that page on my site so that each month was represented by a visual calendar. But it was static CSS; every month looked the same, with the only changes being the month name and the clickable number (representing number of posts in that month):
But my vision was for the month to look accurate, with the first day starting on the right weekday, along with clickable days when blog posts were published (including special highlighting for days with multiple posts), clickable months to take you to an index for the month, and clickable years to take you to an index for that year.
My initial 2-minute sketch looked like this:
I fine-tuned it in HTML and CSS like this:
The HTML is easy enough to generate by hand, if you have a limited calendar and number of events:
But since my blog anthology spans more than 16 years and, presumably, you don’t want to muck with that HTML every month and for every new post you publish, we’ll need some programmatic help to make this automatic.
You’ll notice those aren’t standard HTML elements like <span> and <div> – I made my own because it was much easier to build and debug without extra noise, and also so I wouldn’t have to override any styling applied by templates, plug-ins, and WordPress itself. The <year> element is a grid, set up this way:
year
{
display: grid;
grid-template-columns: repeat(auto-fill, 210px);
}
<month> is just a 210px × 156px, fixed-dimension wrapper around <days>, that fills <year> in a responsive way, thanks to the auto-fill grid. The dimensions are arbitrary, but were meant for the calendar to fill months 6, 4, or 3 wide, depending on browser window size.
month
{
width: 210px;
height: 156px;
}
<days> becomes a grid-within-a-grid, which allows 30-pixel wide <day> elements to flow 7 columns wide and up to 6 rows high (since some months that start on a Friday or Saturday actually have 5 Sundays), within their <month> container:
days
{
display: grid;
grid-template-columns: repeat(7, 30px);
grid-template-rows: repeat(6, 21px);
}
Finally, as I described in an earlier post, starting the weekday accurately (e.g. in the right grid column) relies on this ingenious CSS from Zell:
/* for each <days class=d{X}> make {X} the first column */
days.d1 day:first-child { grid-column: 1; }
days.d2 day:first-child { grid-column: 2; }
days.d3 day:first-child { grid-column: 3; }
days.d4 day:first-child { grid-column: 4; }
days.d5 day:first-child { grid-column: 5; }
days.d6 day:first-child { grid-column: 6; }
days.d7 day:first-child { grid-column: 7; }
No, this isn’t the most responsive design I’ve ever created, because I rely on explicit pixel sizes, but if you view the site on a mobile device, you’ll see these dimensions are adjusted slightly to fill the screen appropriately.
How to Get There
My plan seemed simple: just port the same T-SQL queries I already wrote to MySQL, pull them from a PHP page, and call it a day. But, as hinted earlier, limitations in MySQL 5.7 made me step back. I thought about the things I needed to help me automate HTML generation, and how some would be complicated by the lack of modern language features, and by me being more comfortable writing MySQL queries than PHP code:
- A calendar table representing all of the dates I would ever need to display:
- Start from the first day of the first month of my very first post (December 2006)
- End at the last day of some month in the future
- For each month, whether it is the first or last month in a (partial!) year
- For each day, attributes that indicate:
- Whether it is the first or last day of the month
- For the first of each month, the weekday it falls on
- Data from
wp_postsfor every published post:- The date of the post
- The number of posts published that day
- Aggregated data for each month
- Aggregated data for each year
I started with the built-in calendar table that will serve many other purposes down the road. Following at least some of WordPress’ existing naming conventions, I used this syntax to generate 8,432 rows, covering December 2006 (the month of my first post) through the end of 2029:
SET @start = DATE('20061201');
SET @end = DATE('20291231');
CREATE TABLE `wp_calendar`
SELECT d AS TheDate,
YEAR (d) AS TheYear,
MONTH(d) AS TheMonth,
DAY (d) AS TheDay,
# which day of week does the month start on?
CASE WHEN DAY(d) = 1
THEN DAYOFWEEK(d) ELSE NULL END
AS WeekdayOfFirst,
# is the current date the last day of the month?
CASE WHEN LAST_DAY(d) = d
THEN 1 ELSE 0 END
AS IsLastOfMonth,
# do we need a closing </year> tag because it is
# either the last day of January OR the last day of
# the current month?
CASE WHEN (MONTH(d) = 1 AND LAST_DAY(d) = d)
OR (LAST_DAY(@start) = d AND MONTH(d) > 1)
THEN 1 ELSE 0 END
AS NeedsClosingYear
FROM
(
# add a day to every day in the range
SELECT DATE(DATE_ADD(@start, INTERVAL n-1 DAY)) AS d
FROM
(
# loop variable to simulate ROW_NUMBER()
SELECT (@rn := @rn + 1) AS n
FROM INFORMATION_SCHEMA.COLUMNS AS c1
CROSS JOIN INFORMATION_SCHEMA.COLUMNS AS c2
CROSS JOIN (SELECT @rn := 0) AS rn
LIMIT 10980 # more than 30 years!
) AS Numbers
) AS Days WHERE d <= @end;
You can experiment with the contents of such a table in this db<>fiddle, but keep in mind this may generate a forward-compatibility warning in some versions.
I rely on INFORMATION_SCHEMA having enough columns in a WordPress installation, and this gets me well beyond the needed 8K+ rows. If you don’t have enough rows where you’re implementing this, you can cross join the view again. In MySQL 8.0, I probably would have used a recursive CTE to generate a series of days (though I would have to manage cte_max_recursion_depth to get more than 1,000 rows). This would avoid cross joins, identifying the number of days required, and pesky loop variables that might produce unwanted warnings.
WITH RECURSIVE Days(d)
AS
(
SELECT @start
UNION ALL
SELECT DATE_ADD(d, INTERVAL 1 DAY)
FROM Days WHERE d < @end
)
...
…but, I don’t have that luxury on my site today.
I moved on to creating multiple stacked views to aggregate valid post data by day, then by month, and then by year. I did it this way to prevent the approach from becoming overly complicated in one giant query and to avoid repeating expressions multiple times at the same level. And also because I know I will be able to find other uses for some of these individual views.
First, a view to aggregate posts by day (and filter out drafts and other post types from all subsequent views):
CREATE VIEW v_post_days
AS
SELECT DATE(post_date) AS TheDate,
COUNT(*) AS DayCount,
CONCAT('/', YEAR(post_date),
'/', MONTH(post_date),
'/', DAY(post_date), '/') AS Url
FROM `wp_posts`
WHERE post_type = 'post'
AND post_status = 'publish'
GROUP BY DATE(post_date);
Then we can use that view to aggregate by month:
CREATE VIEW v_post_months
AS
# first of month, even for leap years:
SELECT m + INTERVAL 1 DAY - INTERVAL 1 MONTH
AS FirstOfMonth,
m AS LastOfMonth,
YEAR(m) AS TheYear,
MONTH(m) AS TheMonth,
MonthCount,
CONCAT('/', YEAR(m), '/', MONTH(m), '/') AS Url
FROM
(
SELECT LAST_DAY(TheDate) AS m,
SUM(DayCount) AS MonthCount
FROM v_post_days
GROUP BY LAST_DAY(TheDate)
) AS x;
And finally by year:
CREATE VIEW v_post_years
AS
SELECT TheYear,
SUM(MonthCount) AS YearCount,
CONCAT('/', TheYear, '/') AS Url
FROM v_post_months
GROUP BY TheYear;
Putting it All Together
To combine our aggregated post data with the overall calendar table, we can select from the latter and outer join to the views. I expose a bunch of additional columns here because many will be used to either inject directly into the output HTML or help with conditional logic to decide what CSS classes to add and whether to end an HTML element:
ALTER VIEW v_post_calendar
AS
SELECT cal.TheDate,
# for partial years, we need <year>
CASE WHEN y.TheYear IS NOT NULL THEN 1
ELSE 0 END AS NeedsOpeningYear,
# year info
y.Url AS YearUrl,
cal.TheYear,
y.YearCount,
# month info
m.Url AS MonthUrl,
MONTHNAME(cal.TheDate) AS MonthName,
cal.TheMonth,
m.MonthCount,
# day info
cal.WeekdayOfFirst,
d.Url AS DayUrl,
d.DayCount,
cal.TheDay,
cal.IsLastOfMonth, # need a closing </month>?
cal.NeedsClosingYear # need a closing </year>?
FROM `wp_calendar` AS cal
LEFT OUTER JOIN v_post_days AS d
ON cal.TheDate = d.TheDate
LEFT OUTER JOIN v_post_months AS m
ON cal.TheDate IN (m.FirstOfMonth, m.LastOfMonth)
# need to know if it's the last month in the
# current year, especially in the case where
# there are no posts yet for the current month
LEFT OUTER JOIN v_post_years AS y
ON cal.TheDay = 1
AND cal.TheYear = y.TheYear
AND
(
cal.TheMonth = 12
OR LAST_DAY(cal.TheDate) = LAST_DAY(CURDATE())
)
# need to stop on the last day of *this* month:
WHERE cal.TheDate <= LAST_DAY(CURDATE());
The complicated part, of course, is building the final HTML output, which can be a very iterative process to get right – not just when there is a lot of data, but also when there are several edge cases. And when you want the source more legible and easier to troubleshoot, it pays to have additional whitespace peppered in there (like \r, \n, and \t).
CREATE VIEW v_post_calendar_html
AS
SELECT TheYear,
TheMonth,
TheDay,
CONCAT(OpenYear, OpenMonth, Day, CloseMonth, CloseYear) AS html
FROM
(
SELECT TheYear,
TheMonth,
TheDay,
# OpenYear (need to add <year><yearname> etc.):
CASE WHEN NeedsOpeningYear = 1
THEN CONCAT
(
'\r\n<yearname>',
CASE WHEN YearCount > 0 THEN
# if any posts in that year, make it a link
# and display the number of posts:
CONCAT
(
'<a href="', YearUrl, '">',
TheYear, '</a> (', YearCount, ')'
)
ELSE TheYear END,
'</yearname>\r\n<year>'
) ELSE '' END
AS OpenYear,
# OpenMonth (need to add <month><monthname> etc.):
CASE WHEN TheDay = 1
THEN CONCAT
(
'\r\n\t<month>\r\n\t\t<monthname>',
CASE WHEN MonthCount > 0 THEN
# if any posts in that month, make it a link
# and display the number of posts:
CONCAT
(
'<a href="', MonthUrl, '"',
' title="', MonthName, ' ', TheYear, '">',
MonthName, '</a> (', MonthCount, ')'
)
ELSE CONCAT('<b>',MonthName,'</b>') END,
'</monthname>',
# set the right grid column for the first day:
'\r\n\t\t<days class="d', WeekdayOfFirst, '">\r\n'
) ELSE '' END
AS OpenMonth,
# Day (need one of these for every day in the range)
CONCAT
(
'\t\t\t<day',
# need styling if the day has posts or is in the future
CASE WHEN DayCount > 0 OR TheDate > CURDATE()
THEN CONCAT
(
' class="',
CASE WHEN DayCount > 0 THEN ' on' ELSE '' END,
CASE WHEN DayCount > 1 THEN ' multi' ELSE '' END,
CASE WHEN TheDate > CURDATE() THEN ' future' ELSE '' END,
'"'
) ELSE '' END,
'>',
CASE WHEN DayCount > 0 THEN
# make the day clickable and title with nicely formatted date
CONCAT
(
'<a href="', DayUrl, '" title="',
DATE_FORMAT(TheDate, '%M %D, %Y'),
'\r\n', DayCount, ' post',
CASE WHEN DayCount > 1 THEN 's' ELSE '' END,
'">', TheDay, '</a>'
)
ELSE TheDay END,
'</day>'
) AS Day,
# CloseMonth (every last of the month needs </month>)
CASE WHEN IsLastOfMonth = 1
THEN '\r\n\t\t</days>\r\n\t</month>'
ELSE '' END AS CloseMonth,
# CloseYear (last of each year needs </year>)
CASE WHEN NeedsClosingYear = 1
THEN '\r\n</year>'
ELSE '' END AS CloseYear
FROM v_post_calendar
) AS x;
I added a few niceties here, like prettier title attributes for clickable days, and no pluralization bugs for days with one or multiple posts:
Again, some of this was much more useful to me while developing than it is for the final output.
Anyway, I’m probably breaking all kinds of tribal coding conventions and best practices when working with MySQL, but I’m okay with that, since this is about the first thing I’ve ever published that wasn’t written in Transact-SQL.
Finally, the PHP that queries the view and renders the HTML is rather simple, because we’ve done all the work in the query. Though it can’t be called from a regular page; you need to do this from a custom page template and have your regular archives page inherit from it:
<?php
global $wpdb;
$query = "SELECT html
FROM v_post_calendar_html
ORDER BY TheYear DESC, TheMonth DESC, TheDay;";
$rs = $wpdb->get_results($query);
foreach ($rs as $row):
echo $row->html.PHP_EOL;
endforeach;
?>
And now, because the views automatically return data up to and including the current month, you don’t have to touch this again as months and years roll over, outside of the case where your blog is still running in 2030. Or, maybe, if you unpublish posts from the oldest month and want to stop that month from showing up empty. With my previous solution I had hard-coded the list of years and months, and only pulled the number of posts dynamically from the database, so this is already reducing my workload by a little bit every month.
You can see this in action on sqlblog.org, and view the source to confirm the output is as expected. You can also view the CSS, which I left in the HTML source instead of squirreling it away into a separate CSS file, and didn’t minify it so you could grok it easily. Just right-click the page, choose View Page Source, and search for:
/* Archives page CSS */
Further Enhancements
A couple of enhancements I’ve contemplated but not published, and may cover in a future post:
- List the post titles in the title tag – currently I just show how many posts are on a given day, but the title(s) could be surfaced by the underlying views in order to display them as well (granted, a title tag isn’t a great place to put a lot of information).
- Make single-post days redirect right to the post – the archive-by-date-parts page in WordPress defaults to showing a post listing, even in cases where there’s only one post. Seems then having to click on that post is an unnecessary step, and either the direct URL could be put into the calendar in that case, or I could dig into the WordPress code or the theme template and have the destination page check the count and decide what to do. This could work for months and years, too, since any of those slices could contain just a single post.
- Provide more granular calendars – the underlying views could also group by tag or category, allowing an archives page to present a calendar just for that subset of posts. Showing a calendar for my Bad Habits series, for example, would really highlight when I was very active on Stack Overflow and coming across a lot of terrible stuff there. In multi-author blogs (like this one!), you could make it so users see a visual calendar just for the selected author.
Conclusion
How you might implement this in your own blog will differ, depending on where you’re hosting, what version of MySQL you’re using, and the theme you’ve implemented. But the core concept is the same: build a calendar table, outer join to your events table, and then build the HTML. No more boring, never-ending lists of month names!
The post Build a Better WordPress Archives Page Using MySQL appeared first on Simple Talk.
from Simple Talk https://ift.tt/PJONoBz
via
No comments:
Post a Comment