Support » Plugins » Hacks » Disambiguating object_id in wp_term_relationships table

  • Tom Auger


    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?

Viewing 4 replies - 1 through 4 (of 4 total)
  • Tom Auger


    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.

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

    Tom Auger


    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…

    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.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Disambiguating object_id in wp_term_relationships table’ is closed to new replies.