Disambiguating object_id in wp_term_relationships table (5 posts)

  1. Tom Auger
    Posted 5 years ago #

    As I dig through the database structure, there seems to be a potential ambiguity in the wp_term_relationships intersection table. There is nothing that qualifies the object type referenced by object_id.

    In the documentation an "object" in this context can be either a POST or a LINK. So it's possible to have a post with ID 5 and a link with ID 5.

    Let's say we associate Link #5 with Category #1 and Post #5 with Category #2.

    Now there will be 2 entries in wp_term_relationships -
    object_id, term_taxonomy_id, term_order
    5, 1, 0
    5, 2, 0

    If I am trying to find all categories for post ID #5, it will look like that post has been associated with Category #1 AND #2!

    What am I missing?

  2. Tom Auger
    Posted 5 years ago #

    I'll answer my own question as I have dug deeper into the WP taxonomy structure (don't you just love having an excuse to bust out the word 'taxonomy'?).

    While the potential for this kind of data collision is there in the database structure (IMO, this should be addressed at some point), in actual fact it will never occur, because LINKS cannot be associated with CATEGORIES - they can only be associated with LINK CATEGORIES, whereas POSTS are associated with CATEGORIES.

    So to properly join on wp_term_relationships, you need to join to wp_term_taxonomy on the term_taxonomy_id and taxonomy columns, specifying the type of taxonomy (category, link_category, post_tag) that you're interested in.

    It seems unfortunate that the devs approached the problem in this way, as you ought to be able to use the same categories for your links as for your posts (I can think of a number of scenarios where I might want to do this). But at least a solution exists that's quite practicable.

  3. Dave Doolin
    Posted 4 years ago #

    Thanks for writing this up, Tom. As it turns out, I am looking at this very thing at the moment.

  4. Tom Auger
    Posted 4 years ago #

    Dave, make sure that before you go the route of doing your own SQL joins and queries, you fully explore (and exploit) all the really useful functions that are available in the WP Core.

    In particular, you'll want to look at the wp-includes/taxonomy.php file and leverage things like get_term() and get_objects_in_term() etc...

  5. Dave Doolin
    Posted 4 years ago #

    Oh yeah, I almost never deal with the SQL directly.

    I just stubbed out a little plugin for investigating terms. I read source quite a bit, but sometimes it's just handy to have the output from various functions in the admin area.

Topic Closed

This topic has been closed to new replies.

About this Topic