Custom WordPress Loop

  • gcarson

    (@gcarson)


    14 years 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

Viewing 5 replies - 1 through 5 (of 5 total)
  • ambrosite

    (@ambrosite)

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

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

    Thread Starter gcarson

    (@gcarson)

    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!

    ambrosite

    (@ambrosite)

    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
 ";
    Thread Starter gcarson

    (@gcarson)

    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.

    ambrosite

    (@ambrosite)

    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.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Custom WordPress Loop’ is closed to new replies.