WordPress.org

1. Stompfrog
   Member
   Posted 2 weeks ago #

   I am trying to write some custom SQL to return all the posts tagged with 'x'. I am aware that this is easy to achieve with the codex but this is only the start of my challenge because once this query is working I need to add on some additional data from another table to order them by highest page views.

   I already have this SQL written and working for...

   - The most viewed posts in the blog
   - The most viewed posts in category 'x'

   Now I am struggling to get it working for
   - The most viewed posts tagged with 'x'

   So far I have got...

   SELECT * FROM wp_posts
   LEFT JOIN wp_term_relationships
   ON wp_posts.ID = wp_term_relationships.object_ID
   LEFT JOIN wp_terms
   ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id
   WHERE wp_terms.name = 'x'

   This is sort of working as it returns a single post which is indeed tagged with 'x'. However it should be returning four posts tagged with 'x'. Anyone have any ideas where I am going wrong?

   Cheers

2. Stompfrog
   Member
   Posted 2 weeks ago #

   Been thinking about this some more. I don't think the problem is with my SQL skills but rather my understanding of the wordpress databases.

   If I query the term_relationships table for a term_taxonomy_id that represents a category it returns multiple results. A simple example of this is...

   SELECT *
   FROM wp_term_relationships
   WHERE term_taxonomy_id = 99

   However... if I then do the same query with an ID that represents a tag rather than a category I only ever get one result. There seems to be a difference in the way that tag and category data is stored, but I can't work out what it is.

   Can anyone help?

Reply

You must log in to post.