WordPress.org

1. susb8383
   Member
   Posted 5 years ago #

   I'm sure this is a fairly easy question, but I'm pretty new at this.

   I'm trying to use SQL to restrict posts by a certain category and sort them by the value of a custom field.

   Here's what I have for the SQL:

   $pageposts = $wpdb->get_results("SELECT wposts.* FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
   WHERE wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'productname' AND wposts.category_id = '3' AND wposts.post_status = 'publish' AND wposts.post_date < NOW() ORDER BY wpostmeta.meta_value", OBJECT);

   Now here's the problem. It is sorting correctly, but it is selecting every post, not just the posts in category 3.

   Must be something obvious, but...I'm not seeing it.

   Thanks.

2. susb8383
   Member
   Posted 5 years ago #

   Ok, figured it out myself. Would have been a very easy question for someone to answer for me and would have saved me time...but, oh well. I'll post the answer here so that it may save someone else time.

   Not knowing the relationship of the WordPress tables, I just assumed that posts.category_id would contain the category id of the post. What was I thinking???

   After looking at other people's questions, it dawned on me that for some reason, you have to look up the category id in the post2cat table, not in the posts table. So I changed my SQL to this and it worked fine:

   $pageposts = $wpdb->get_results("SELECT wposts.* FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->post2cat wpost2cat WHERE wposts.ID = wpostmeta.post_id AND wposts.id = wpost2cat.post_id AND wpostmeta.meta_key = 'productname' AND wpost2cat.category_id = '3' AND wposts.post_status = 'publish' AND wposts.post_date < NOW() ORDER BY wpostmeta.meta_value", OBJECT);

   Is there any place where all the tables and their relationships are diagramed?

Topic Closed

This topic has been closed to new replies.