This post is a report about my proposal “Implementing MPTT technique for store hierarchical data” on WordPress, at the Google Summer of Code program. If you don’t know the MPTT algorithm, you can read this article.
The plan for this proposal is understand the actual way for get tree data in categories and make the modifications for get/store the tree data. Later, when all has been ready for categories, add the code for pages.
My deliverable will be a plugin with a new Walker class that use MPTT algorithm, some functions for get data from the database, and a few modifications to the core, for attach the plugin functions.
In the last days, I read the code where WordPress has been using the actual way for get/store the tree data. For get that information, I used the phpdoc page of WordPress.
The main class for get tree data is called “Walker“. It have some subclasses that extend the function of Walker class, getting the html code for list categories, pages, etc…:
- Walker_Category
- Walker_CategoryDropdown
- Walker_Comment
- Walker_Page
- Walker_PageDropdown
- Walker_Category_Checklist
But, these classes don’t get the data directly from the database. The way for get that data it’s calling the function get_terms (it’s was defined on the line 605, in the file /wp-includes/taxonomy.php) from another functions. These functions are (each function use a subclass of Walker class):
- walk_category_tree()
- walk_category_dropdown_tree()
- walk_page_tree()
- walk_page_dropdown_tree()
- wp_list_comments()
- get_comment_pages_count()
Now, I have an small idea about the actual scenario for get tree data in WordPress, but for to implement the MPTT, I need to modify the database. I add two new columns y the table “wp_terms_taxonomy” called left and right. These colums are for store the data for get the tree with some sql queries.
I wrote a few lines for start my plugin. It have the original get_terms function with a little modifications for get a tree data using the MPTT algorithm.
Old query:
$query = “SELECT $select_this FROM $wpdb->terms AS t INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ($in_taxonomies) $where ORDER BY $orderby $order $limit”;
New query:
$subquery = “SELECT node . * , ( COUNT( parent.term_id ) -1) AS depth
FROM $wpdb->term_taxonomy AS node, $wpdb->term_taxonomy AS parent
WHERE node.left BETWEEN parent.left AND parent.right
GROUP BY node.term_id
ORDER BY node.left”;
$query = “SELECT $select_this FROM ($subquery) AS tt INNER JOIN $wpdb->terms AS t ON t.term_id = tt.term_id WHERE tt.taxonomy IN ($in_taxonomies) $where ORDER BY $orderby $order $limit”;
It’s works, but it need some modifications for get the tree data with options (get a sub-tree, show only when is a not empty tree, etc…).
The next step is make a new Walker class for print the tree data of categories.
Note: if my post have a mistake, just let me know for fix it 
.
.
).
.
