[wp-hackers] Creating Custom Taxonomies on the fly

[Haluk Karamete]

Thanks for the SQL statement guys. What do you think of the
methodology though? If this feature were to be turned on by a simple
flag, wouldn't it be time-saver for a ton of functionality for content
rich multi-level web sites. In my opinion, coming up with cats & sub
cats are always a challenge. It's wishful thinking vs reality haywire
hell. But with organically growing tags under related taxonomies, sub
categories are born naturally and evolve naturally - totally
hands-free.

Any thought on this as a generic feature that might come with WP out
of the box. Just like the multi-site feature, it can only be turned on
by those who want it. Do you guys think this technique would ease the
hierarchy management of complex web sites? Or is it not a good idea to
begin with anyway?



On Sat, Jan 29, 2011 at 11:02 PM, Mike Schinkel
<mikeschinkel at newclarity.net> wrote:
> On Jan 30, 2011, at 1:35 AM, Dion Hulse (dd32) wrote:
>> I'll be honest, I'm not a SQL performance nut, and I hate data duplication.
>> I've just grabbed a SQL in use on a reasonably -low traffic- site which has about 200 terms in total and a few hundred posts, the result of this is transient cached(which is cleared when tags/cats change) and the site uses super cache as well, So the site has never had any performance issues or scaling problems due to this - Thats not to say it won't in the future of course.
>>
>> SELECT DISTINCT t.*
>> FROM wp_terms AS t
>>  JOIN wp_term_taxonomy AS tt ON ( t.term_id = tt.term_id AND tt.count >0 )
>>  LEFT JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
>> WHERE tt.taxonomy = 'post_tag'
>> AND tr.object_id
>> IN (
>>  SELECT tr.object_id
>>  FROM wp_term_relationships AS tr
>>    LEFT JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
>>  WHERE tt.term_id = 3 # This is the Term ID of the category (or any related taxonomy really) that you want to intersect the tags with
>> )
>> ORDER BY t.name ASC
>>
>> I'm sure it's not the best query, it could certainly be optimized (I'm pretty sure it's possible to get rid of that sub select, that'll choke when there are hundeds of posts in a category), but my main reason for digging it up, is the point out that you don't have to load all the posts up, just the ID's of the posts, which is stored in the taxonomy tables anyway.
>>
>
> You are right, it's not very optimized. You really don't need dependent subqueries[1] like that if you can at all help it.  This unravels them:
>
> SELECT DISTINCT
>        wp_terms.*
> FROM wp_terms
>        INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
>        INNER JOIN wp_term_relationships ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
>        INNER JOIN wp_term_relationships intersected_term_relationships ON wp_term_relationships.object_id = intersected_term_relationships.object_id
>        INNER JOIN wp_term_taxonomy intersected_term_taxonomy ON intersected_term_relationships.term_taxonomy_id = intersected_term_taxonomy.term_taxonomy_id
> WHERE 1=1
>        AND wp_terms.term_id<>intersected_term_taxonomy.term_id
>        AND wp_term_taxonomy.count > 0
>        AND wp_term_taxonomy.taxonomy = 'post_tag'
>        AND intersected_term_taxonomy.term_id=3
> ORDER BY
>        wp_terms.name ASC
>
> Of course if you are going to use it in PHP for WordPress this would be more useful:
>
>  global $wpdb;
>  $sql =<<<SQL
> SELECT DISTINCT
>        {$wpdb->terms}.*
> FROM {$wpdb->terms}
>        INNER JOIN {$wpdb->term_taxonomy} ON {$wpdb->terms}.term_id = {$wpdb->term_taxonomy}.term_id
>        INNER JOIN wp_term_{$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_taxonomy}.term_taxonomy_id
>        INNER JOIN {$wpdb->term_taxonomy} intersected_term_relationships ON {$wpdb->term_taxonomy}.object_id = intersected_term_relationships.object_id
>        INNER JOIN {$wpdb->term_taxonomy} intersected_term_taxonomy ON intersected_term_relationships.term_taxonomy_id = intersected_term_taxonomy.term_taxonomy_id
> WHERE 1=1
>        AND {$wpdb->terms}.term_id<>intersected_term_taxonomy.term_id
>        AND {$wpdb->term_taxonomy}.count > 0
>        AND {$wpdb->term_taxonomy}.taxonomy = 'post_tag'
>        AND intersected_term_taxonomy.term_id=%d
> ORDER BY
>        {$wpdb->terms}.name ASC
> SQL;
>  $sql = $wpdb->prepare($sql,3); // 3 is your category ID
>
> Hope this helps.
>
> -Mike
> [1] http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/
>
> P.S. Too bad stuff like this isn't available natively in WP_Query.
>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>