WordPress.org

1. gcarson
   Member
   Posted 1 year ago #

   I'm looking to modify a custom wordpress loop. Currently, I have it working to pull posts based on meta tag value. But, I want to add in that it has to be in a certain category. I've tried dozens of combinations but couldn't come up with anything. I was hoping someone new the right mix here. Here is what I have so far:

   $wp_query->request = "
   	SELECT wposts.*
      FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
      WHERE wposts.ID = wpostmeta.post_id
      AND wpostmeta.meta_key = 'meta-value'
      AND wpostmeta.meta_value = 'yes'
      AND wposts.post_status = 'publish'
      AND wposts.post_type = 'post'
      AND $wpdb->term_taxonomy.term_id = '300'
      AND $wpdb->term_taxonomy.taxonomy = 'category'
        ORDER BY wposts.post_date DESC LIMIT $ppp OFFSET $offset
    ";

   So I'm trying to query posts that are published, that have a meta-value = yes and with a category ID of 300. It works without the 2-3 last lines in, but then it pulls posts from all categories. When I try that code, I get the error "Unknown column wp_term_taxonomy.term_id in where clause". I tried a join statement among other things. However, mysql is not my strong suite.

   Thanks for the help1

2. ambrosite
   Member
   Posted 1 year ago #

   Have you tried using advanced taxonomy queries, rather than writing your own SQL?

   http://ottopress.com/2010/wordpress-3-1-advanced-taxonomy-queries/

3. gcarson
   Member
   Posted 1 year ago #

   No I haven't. I read about what I posted above from another help topic. Modified as I needed, but couldn't figure out how to add an additional parameter to for only certain categories. I use similar queries as above throughout my site.

   Thanks for the link. I'll take a look at that post. I've read some of their other tutorials and its usually good stuff. Problem is... I'm still on 2.9.. not 3.1 yet. So maybe I should update first. I haven't updated as I hacked alot of the code and plugins to get my sites to work the way they do. I'm nervous about updating for sure!

4. ambrosite
   Member
   Posted 1 year ago #

   I understand how you feel. I have a heavily hacked 2.9 site myself and I'm a little nervous about updating it too!

   Your query is not working because the 'term_id' and 'taxonomy' fields exist in the wp_term_taxonomy table, but you did not join that table in your query. Furthermore, you must also join the wp_term_relationships table in order to match a post with a taxonomy term. I have not tested this query, but I think it should work:

   $wp_query->request = "
      SELECT wposts.*
      FROM $wpdb->posts wposts
      INNER JOIN $wpdb->postmeta wpostmeta ON ( wposts.ID = wpostmeta.post_id )
      INNER JOIN $wpdb->term_relationships tr ON ( wposts.ID = tr.object_id )
      INNER JOIN $wpdb->term_taxonomy tt ON ( tr.term_taxonomy_id = tt.term_taxonomy_id )
      WHERE wpostmeta.meta_key = 'meta-value'
      AND wpostmeta.meta_value = 'yes'
      AND wposts.post_status = 'publish'
      AND wposts.post_type = 'post'
      AND tt.term_id = '300'
      AND tt.taxonomy = 'category'
        ORDER BY wposts.post_date DESC LIMIT $ppp OFFSET $offset
    ";

5. gcarson
   Member
   Posted 1 year ago #

   Thank you for this! I have to admit, that looks like a foreign language to me. I think it might be time to upgrade to 3.1 to get those new easier commands.

   I tried it and got this error:

   MySQL server version for the right syntax to use near '->posts wposts INNER JOIN $wpdb->postmeta wpostmeta ON ( wposts.ID = wpostme'

   I'll have some time tomorrow to review and if I figure it out, I'll post for all.

6. ambrosite
   Member
   Posted 1 year ago #

   That's strange. I ran the query on my own database just now and it worked.

   Did you try running this directly in MySQL? If so, you need to replace the $wpdb variable with your WP database prefix. For example, $wpdb->posts becomes wp_posts, and so on. The $wpdb variable is only available from within WordPress.

Topic Closed

This topic has been closed to new replies.