[wp-hackers] Creating Custom Taxonomies on the fly

[Dion Hulse (dd32)]

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.

Ultimately, The correct way of doing things, is what ever is the easiest  
for the developer, and which they can make scale well.

On Sun, 30 Jan 2011 13:45:51 +1100, Haluk Karamete  
<halukkaramete at gmail.com> wrote:

> Dion, here is the part I don't get; "We want Tags which are attached
> to <list of posts in category>"
>
> I think your solution approaches the matter from a different
> implementation. My first idea was to sort this matter our per post and
> at post update time. One post at a time. From you, what I get is do
> this every 24 hrs or so as a batch process. That for sure will cause
> some DB crunching while that process is sorting the matter out.
>
> Let's take it from another example... that is run time processing at
> categorty.php level and relying on super cache magic.
>
> category.php will ask
>
> what category of posts I'm supposed to deliver now?
> answer: deliver catid:14
>
> category.php will ask to wp_posts and to wp_term_relationships and to
> wp_terms tables
> Give me a list of all the distinct tags that belong to those posts
> where the posts have been categorized under 14.
>
> This is one heck of SQL query. I would never do it.
>
> I think doing this at post update time is better. What do you think?
>
>
>
>
> On Sat, Jan 29, 2011 at 6:30 PM, Haluk Karamete  
> <halukkaramete at gmail.com> wrote:
>> For the site I have in mind, 100's of cats, 1000's of posts... So
>> whatever it is got to scale up.
>>
>> On Sat, Jan 29, 2011 at 3:43 PM, Dion Hulse (dd32)  
>> <wordpress at dd32.id.au> wrote:
>>> On Sun, 30 Jan 2011 10:33:50 +1100, Haluk Karamete  
>>> <halukkaramete at gmail.com>
>>> wrote:
>>>>
>>>> Do you see some value in this approach? Or are there easier/smarter
>>>> ways to address the problem I'm trying to solve with less hassle?
>>>
>>> How many posts are we talking here?
>>>
>>> Whilst your proposed selection would work, it does sound like a lot of
>>> processing to ensure that the terms are kept in sync..
>>>
>>> My method would be:
>>> Generate tag cloud
>>>  - Are we on a category view?
>>>  - No: Generate as normal
>>>  - Yes: Filter the terms:
>>>   - We want Tags which are attached to <list of posts in category>
>>>
>>> You'd have to add some caching to that, and theres a good chance you'd  
>>> have
>>> to filter some SQL there for best performance.
>>> I've done something similar (intersections of terms) for navigational
>>> elements of a custom taxonomy, ie. Site has multiple sections, and a  
>>> set of
>>> navigational elements to drill down, In the drill down, you only want
>>> elements selectable which the section of the site has available
>>>
>>>
>>> Dion Hulse / dd32
>>> Contact:
>>>  e: contact at dd32.id.au
>>>  Web: http://dd32.id.au/
>>> _______________________________________________
>>> wp-hackers mailing list
>>> wp-hackers at lists.automattic.com
>>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>>>
>>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>


-- 
Dion Hulse / dd32
Contact:
  e: contact at dd32.id.au
  Web: http://dd32.id.au/