Getting Attachments in Posts Under Specific Categories in WordPress with SQL

| One Comment

This post is actually a refinement to another post ‘Extracting Data From WordPress Database and Convert Into XML Format‘ that I wrote couple of years back. In that post I was using nested php-mysql calls to get attachments of posts in WordPress. Since I have received some inquiries regarding that post, I looked at it and found that the code is quiet messy and difficult to understand. So I decided to make the code less complicated by building a nested SQL query and make the PHP code simpler. I have two queries below. The first one simply gets all the posts that are under specific categories (slugs). The second query goes further to get all the attachments in each post under unspecific categories. You can run this SQL in phpmyadmin and see the result. See this WordPress database schema to understand what’s going on.

Query all posts under specific categories (slugs)

SELECT WPP.ID, WPP.post_title, WPTRT.name
FROM wp_posts AS WPP
RIGHT JOIN (

   SELECT WPTR.object_id, WPTTT.name
   FROM wp_term_relationships AS WPTR
   RIGHT JOIN (

      SELECT wptt.term_id, wptt.term_taxonomy_id, wpt.name
      FROM wp_term_taxonomy AS wptt
      RIGHT JOIN wp_terms AS wpt ON wptt.term_id = wpt.term_id
      WHERE slug IN ('uncategorized')

   ) AS WPTTT ON WPTR.term_taxonomy_id = WPTTT.term_taxonomy_id
   WHERE 1
) AS WPTRT ON WPP.ID = WPTRT.object_id
WHERE 1

you can include more than one slug. eg: slug IN (‘photographyposts’, ‘interiordesignposts’, ‘graphicsdesignposts’). If you use only one slug  then you can change the where clause to slug=’photographyposts’.

Query all attachments in posts under specific categories (slugs)

SELECT * 
FROM wp_posts AS ATH
RIGHT JOIN (

   SELECT WPP.ID, WPP.post_title, WPTRT.name
   FROM wp_posts AS WPP
   RIGHT JOIN (

      SELECT WPTR.object_id, WPTTT.name
      FROM wp_term_relationships AS WPTR
      RIGHT JOIN (

         SELECT wptt.term_id, wptt.term_taxonomy_id, wpt.name
         FROM wp_term_taxonomy AS wptt
         RIGHT JOIN wp_terms AS wpt ON wptt.term_id = wpt.term_id
         WHERE slug IN ('uncategorized')

      ) AS WPTTT ON WPTR.term_taxonomy_id = WPTTT.term_taxonomy_id
      WHERE 1
   ) AS WPTRT ON WPP.ID = WPTRT.object_id
   WHERE 1
) AS POSTS ON ATH.post_parent = POSTS.ID
WHERE ATH.post_type =  'attachment'

To see how to use this with WordPress please read my previous post.

 

One Response to Getting Attachments in Posts Under Specific Categories in WordPress with SQL

  1. Thanks a lot for these codes.

Leave a Reply

Your email address will not be published. Required fields are marked *