WordPress MU

version 2.8.2 (not available in version drop-down yet)

I am attempting to use wp_sitecategories to store preset, site-level categories. I am trying to determine how the id numbers auto-generated by MySQL coordinate across these three tables. I have added categories and manually set id numbers, as well as reading what I could find in the docs/forums.

There is some overlapping across these tables. First, there is no wp_site_term_taxonomy table. So all the taxonomy, even for site-level categories, is stored in each blog, not at the site level.

Along with this, it appears that the site categories, because they are implemented by copying ids into the wp_X_terms and wp_X_term_taxonomy tables and auto-generating a new term_taxonomy_id, have issues with possible mismatched term_ids across site-level and blog-level tables.

Here is the scheme I am planning to use, unless someone here at the forms has a legit criticism or better idea. I'm new to WordPress and MU, so I'm very open to suggestions:

My scheme:
I will be reserving a block of approximately 10,000 id numbers for site-level categories. I will achieve this by creating these entries in these tables:
  wp_sitecategories:
    term_id = 9999
  wp_X_term_taxonomy:
    term_taxonomy_id = 9999
    term_id = 9999

This way, when a user creates their first new, blog-level category, the term_id and term_taxonomy_id will be 10000, and will auto-increment from there.

I said "approximately" 10,000 reserved ids because Uncategorized and Blogroll are taken as id=1 and id=2, respectively. Plus my 9999 is used, and zero is apparently never used, so I'm left with 9,996 reserved site-level categories/tags, which should be plenty.

The only thing I haven't quite figured out yet is how the wp_X_term_taxonomy tables are created for new blogs. There is no site-level taxonomy table to copy from, only many blog-level taxonomy tables. If anyone has insight into this I would appreciate it.

What do you think of this approach of reserving ids? Is there a better approach? I have tested adding new categories after manually editing term_ids to 9999, and the auto-increment works as I described.

Thanks!

After adding a second blog to my site, I have noticed this:

It appears that the site categories are implemented as follows:

  On creation of new wp_sitecategory:catID, that catID is copied into the wp_1_terms and wp_1_term_taxonomy tables and a new term_taxonomy_id is generated. But this only applies to wp_1_terms and wp_1_taxonomy, not wp_2 or wp_N. I'm not exactly sure what's going on here, but the site-level categories I add never show up in the wp_2 tables and always show up in the wp_1 tables. I cannot access the site-level categories from the 2nd blog when I add a new post, but they do show up for the first blog.

I am no longer able to edit my original post, so here it is as a reply.

The fn global_terms() in wpmu-functions.php is the target of the filter 'term_id_filter' in wp_insert_term() and wp_update_term() is the source of the sitecategories table. (Catching people up...)

Looks to me like it takes any term on any blog and inserts it into the sitecategories table and then changes the term_id in the blog's terms table and then changes the term_taxonomy table on that blog to reflect the new term_id.

That should work on your blog id 2 if that is where your code is executing.

It sounds like global_terms() is a way to make a blog-level category into a site-level category, but that's not really what I'm looking for. Is it that the term_id must be in the blog's wp_X_terms table for the category to show up in the Add Post page?

I just want to make site-level categories available to users when they post, but I would actually prefer to avoid any synchronization issues between the wp_X_terms and wp_site_categories. Because the taxonomy is at the blog-level only, I want to copy the wp_sitecategories into every wp_X_Term_taxonomy, and possibly also wp_X_terms, every time a new blog is created and every time I update wp_sitecategories.

It would, of course, be better if the taxonomy was also available at the site-level and the two could be kept completely separate, but that is not the case today.

I'll look at those functions at see if there/s a way to copy the other direction, from site to blog.

Thanks!

I just looked at global_terms(), wp_insert_term(), wp_update_term() and other parts of taxonomy.php, and now I understand much more. Thanks for the pointer to global_terms().

It appears that wp_sitecategories serves an entirely different purpose than I am seeking. The entire paradigm is one I hadn't anticipated, being new to blogging and WordPress. I hadn't really thought about the need to summarize all the blogs' categories at the site level, but now that I see it I can understand it.

But back to my problem: what is the best way to create some preset categories which are inherited by all users? The concept of reserving a set of id numbers makes sense to me, and the auto-increment scheme is simple enough to manipulate. But my site-level preset categories have hierarchies, and the taxonomy is at the blog level. I'm going to need a stub table, like wp_0_term_taxonomy from which I copy the taxonomy to each blog.

This is part of what confuses me about the existing implementation of wp_sitecategories: it stores a unique list of all the categories created by all the users, but the taxonomy is entirely by user. I can understand the need for hierarchies and not relying on the category name to be unique. But within wp_sitecategories, in spite of there being no taxonomy for this table, duplicate names are allowed, only the term_id and slug (category_nicename) must be unique. I need to understand more of how wp_sitecategories is used as a data source in wmpu - what kind of data retrieval queries are run against it?

The sitecategories aren't really used. I think they're in there so you *could* use them, but by default, they just sit there.

When users make new categories, they get added to that table, that's pretty much it.

Andrea,

Yes, that's how it appears. But there is an issue with how they are implemented that interferes with what I want to do. I think there is a flaw with how wp_sitecategories is implemented that makes it less useful. It has to do with the wp_terms:slug and wp_sitecategories:category_nicename. Here is the code in question:

from global_terms():
$global_id = $wpdb->get_var( $wpdb->prepare( "SELECT cat_ID FROM $wpdb->sitecategories WHERE category_nicename = %s", $c->slug ) );
if ( $global_id == null ) {
$wpdb->insert( $wpdb->sitecategories, array('cat_name' => $c->name, 'category_nicename' => $c->slug) );
$global_id = $wpdb->insert_id;
}

from wp_insert_term():
1384 if ( ! $term_id = is_term($slug) ) {
1385 if ( false === $wpdb->insert( $wpdb->terms, compact( 'name', 'slug', 'term_group' ) ) )
1386 return new WP_Error('db_insert_error', __('Could not insert term into the database'), $wpdb->last_error);
1387 $term_id = (int) $wpdb->insert_id;
1388 } else if ( is_taxonomy_hierarchical($taxonomy) && !empty($parent) ) {
1389 // If the taxonomy supports hierarchy and the term has a parent, make the slug unique
1390 // by incorporating parent slugs.
1391 $slug = wp_unique_term_slug($slug, (object) $args);
1392 if ( false === $wpdb->insert( $wpdb->terms, compact( 'name', 'slug', 'term_group' ) ) )
1393 return new WP_Error('db_insert_error', __('Could not insert term into the database'), $wpdb->last_error);
1394 $term_id = (int) $wpdb->insert_id;
1395 }

So it is possible for a term in one blog to share the same term_id in wp_sitecategories, yet have an entirely different taxonomy. It all hinges on the wp_insert_term() slug using the hierarchy only if the slug exists for this user already. If the first instance of a name/slug has a parent, in one blog and a different parent in another blog, they can still share the same term_id.

I will probably end up implementing my own "preset_terms" and "preset_terms_taxonomy" tables and hacking in some preset/copy code to make sure all blogs have these terms and that that wp_sitecategories table also has those terms because the auto-increment scheme depends on wp_sitecategories because of the global_terms() code above.

Oh well... I think that if I really wanted to implement wp_sitecategories in my site, I would end up implementing a query where I retrieve only unique names, not cat_IDs and then searching blogs by category name, but id, which kind of stinks.

If wp_sitecategories used the name, not the nicename as its "effective" key, that would help, but then it would still either require that all terms of the same name use the same id, or it would require searches by category name anyway. So I don't see a clear solution here that preserves the uniqueness of a term_taxonomy regardless of the term name. I'm still pondering this...

Thanks!

You may also want to look into the new taxonomy system in WP, and consider that the codebases are merging - SO, at some point they'll either figure out a way to include this and make it better (I certainly agree it needs works), or if you figure out anything useful, it'd be great if you could file a trac tickets with your changes. :) It could get rolled in.

What is this "new taxonomy system in WP", and what codebases are merging? I'm new here and I just searched wordpress.org and the only "new taxonomy" I could find was v2.3. Is this a v3 thing? How do I find out more about it?

I'm still pondering the solution to my issue. I'm hesitant to modify any of these wp_ functions, including global_terms(). I'm more likely to take an alternate, "new functionality" path. If it ends up being something of general use and others would like me to roll it in or merge it with wp_sitecategories in some way, then we can deal with that later.

Thanks!

The codebases of single user WordPress and WordPressMU are merging. There will no longer be two programs, only one.

New taxonomy system explained here, and a correction - it's custom taxonomies (but still new):
http://justintadlock.com/archives/2009/05/06/custom-taxonomies-in-wordpress-28

No idea how ti related to wpmu sitewide categories yet.

OK, now I understand taxonomies in wordpress. The new 2.8 features are for users more than developers, so they aren't that interesting to me (yet), but that tutorial explained wp_X_term_taxonomy:taxonomy field so that I now understand how to use the 2.3 features. I had seen register_taxonomy(), but had no idea what it did, and I assumed that the enumeration of taxonomies didn't support custom values. That's cool that it does. It gives me another level to work with in terms of my hierarchies, and that top level has advanced functionality.

This taxonomy api has no direct bearing on wp_sitecategories, because wp_sitecategories has no taxonomy available.

That's definitely useful for my still evolving scheme. Thanks for all the info.