0

I have the following tables (only pertinent info included for brevity and clarity)

Table Name: wp_term_relationships

object_id    term_taxonomy_id
1266         1341
1266         452
1266         449

Tabloe Name: wp_term_taxonomy

term_taxonomy_id    term_id
1341                1342
452                 453
449                 450

Table Name: wp_terms

term_id      name      slug
1342         792       792
453          396       396
450          394       394

I have a list of the wp_term "names" and I want to get a list of the object_ids (only unique ones) from wp_term_relationships

So, my list will be something like: 394, 396, 988, 666 And I would like it to return (based on the above data): 1266 (i.e. Using term_id from wp_terms to get term_taxonomy_id from wp_term_taxonomy and then finally using that value in wp_term_relationships to get the object_id)

I am quite new to mySQL and have been reading/experimenting with it all day and can't seem to get it quite right.

SELECT $wpdb->term_relationships.object_id
FROM $wpdb->term_relationships
    LEFT JOIN $wpdb->term_taxonomy ON $wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id
    LEFT JOIN $wpdb->terms ON $wpdb->terms.term_id = $wpdb->term_taxonomy.term_id   
WHERE name IN (394, 396, 988, 666)  
GROUP BY $wpdb->term_relationships.term_taxonomy_id
ORDER BY $wpdb->term_relationships.term_taxonomy_id DESC

I wondered if someone could maybe cast an experienced eye over it and help me see where I'm going wrong?

Thank you :-) This is my query so far:

  • Please explain why you want to do it that way. It could be that you are facing a XY Problem. – kaiser Sep 8 '14 at 20:36
  • Is your term data actually numeric? That is pretty unconventional case, could you elaborate on what are you doing with it? Typically API functions are easier in WP than raw SQL. – Rarst Sep 8 '14 at 21:11
  • 1
    You basically have it - just GROUP BY $wpdb->term_relationships.object_id instead. – bonger Sep 8 '14 at 22:41
  • @bonger thanks that sorted it. If you put that as an answer I'll accept it. kaiser / Rarst, unfortunately I have inherited this db and have to work with what I've got but your link to the x/y problem was a very interesting read. Thanks. – John T Sep 9 '14 at 6:34
1

Per comments, you basically have it - just GROUP BY $wpdb->term_relationships.object_id instead; also to be pernickety, you'd use standard (INNER) JOINs rather than LEFT ones:

SELECT $wpdb->term_relationships.object_id
FROM $wpdb->term_relationships
    JOIN $wpdb->term_taxonomy ON $wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id
    JOIN $wpdb->terms ON $wpdb->terms.term_id = $wpdb->term_taxonomy.term_id   
WHERE name IN (394, 396, 988, 666)  
GROUP BY $wpdb->term_relationships.object_id
ORDER BY $wpdb->term_relationships.term_taxonomy_id DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.