So in the time I've had after work last night and tonight I've started making the front end to wordpress but using my framework. There were a few motivations for this:
- Wordpress has a POS template engine--or lack thereof. This is an arguable point, of course, because PHP is a template language.
- I have no idea how many SQL queries wordpress + k2 perform on the main page, but I had a feeling I could do less.
- I wanted more control over the front-facing side of my blog.
General
In the few hours that I have dedicated to this mini project, I have made quite a lot of progress! You can go see monitor my current progress if you would like to. Otherwise, I've made some general observations about wordpress.
First of all, I get the feeling that the wordpress creators have no idea what they are doing with databases. They need to settle on a single naming scheme for their database table columns. I can imagine that their excuse for the terrible column names was a result of backwards compatibility or some other meaningless argument. There's no excuse for this type of thing. If it were an issue of backward compatibility then when they were programming it then they should have stuck with the original naming scheme and left it at that. If it was a problem of different styles for its different developers then they need to learn how to coordinate their efforts.
It's total guess work about what column you want--especially for table primary keys and foreign keys! Some places have, for example: ID, cat_ID, comment_ID, post_id, category_id, etc. Last time I checked most (if not all) major databases supported ALTER syntax. And if they don't, then they probably support TEMPORARY tables in which case one can use as an intermediate for the data if a table's columns need to be renamed by dropping said table.
Second, their code is a total mess. I get the feeling that this is partly due to the need to be backward compatible to some point, hence their dependence on global variables.
Third, it's a wonder that everything works as well as it seems to. I'm astonished that its developers can maintain it.
Otherwise, since I haven't gone and looked too much at the wordpress internals, I thought I'd show how I solved a few simple problems, namely: archives list and post categories list.
Archives
In my framework I have made these great things called components. Components are controllers that can be accessed through templates using a simple XML tag. If you look at this blog, each box represents a component: menu, blogroll, archives, categories, etc.
My goal was that no component would execute more than one query, so getting the archives was interesting. You might think that getting the number of posts would be the tricky part, but it actually turns out to be how to get the months themselves. Here's the SQL query I came up with:
SELECT MONTH(post_date) as month_num, YEAR(post_date) AS year, COUNT(ID) AS num_posts
FROM wp_posts
WHERE post_status = 'publish'
GROUP BY YEAR(post_date), MONTH(post_date)
ORDER BY YEAR(post_date) DESC, MONTH(post_date) DESC
This is quite simple, I wouldn't be surprised if something similar is somewhere in wordpress.
Categories
Another requirement that I set for myself was not to do an extra query to get a posts categories. There was a rather simple solution to this problem. The categories component which lists out all of the categories would have to use the same result set as the categories that are listed under each post name. This means that the categories component does (ghast!) two queries and then every post listed on the front page takes information from those results and figures out which categories it belongs to (without any extra queries). Another requirement was that I not break any existing wordpress urls, that meant that the PHP category needed to have the /development/ parent category in its url.
You might be wondering why I needed two queries for the categories component. This was because I decided that to handle sub-categories, I would only allow for the display of one sub-level and I would use PHP to figure out which categories belonged to which parents. This was a rather simple thing to accomplish.
Regardless, I used a GROUP_CONCAT to get the category ids that a post belongs to along with the normal post data. Here's a super function I made for finding visible posts:
public function findAllVisible($page = 1, $limit = 10, array $filter = array()) {
// build the base of the query
$query = new VK_SelectQuery("p.*, u.user_nicename AS author_name, u.ID as author_id", "wp_posts p, wp_users u", "p.post_author=u.ID");
// visible
$query->addWhere("p.post_status='publish'");
// pagination
$page = $page < = 1 ? 0 : $page-1;
$query->addLimit($page * $limit) // start
->addLimit($limit); // offset
// ordering
$query->addOrderBy('p.ID', 'DESC');
// default type filter
if(!isset($filter['type'])) {
$filter['type'] = 'post';
}
// filter the results further
foreach($filter as $key => $val) {
switch($key) {
// by a specific cateogry
case 'category':
$query->addJoin("wp_post2cat pc", "pc.post_id=p.ID", 'INNER')
->addWhere("pc.category_id=". (int)$val);
break;
// by a date
case 'year':
case 'month':
case 'day':
$query->addWhere(strtoupper($key) ."(p.post_date)=". (int)$val);
break;
// by an author
case 'author':
$query->addWhere("p.post_author=". (int)$val);
break;
// by type
case 'type':
$query->addWhere("p.post_type='{$val}'");
}
}
// get the categoriy ids in a comma-separated form
$query->addFrom("wp_post2cat ptc")
->addSelect("GROUP_CONCAT(ptc.category_id SEPARATOR ',') AS category_ids")
->addWhere("ptc.post_id=p.ID")
->addGroupBy('p.ID');
// get the posts
return $this->findAllByQuery($query);
}
This fit nicely into PostsRecord class and lets me deal with details such as pagination easily.
- Development
- PHP by Peter Goodman on Jul 5, 2007 @ 10:28pm
