mysql query for nonexpired posts for multiple tags

  • Hi,

    I am trying to put together an mysql query that will give me all nonexpired posts(using Atrapos) which also have a category=7 and a custom field key=”myimportance” ordered by the value of the “myimportance” custom field.

    I think I need to do this solely in mysql because I am accessing the blog server from a different server and don’t have an easy way to parse an rss feed. And I don’t think an rss url can do what I need anyway. If it can, it might be worth it to try the xml parsing.

    I’m starting with a query that will return all posts that are in category=7, but I’m just not sure how to get non-expired posts and posts with the “myimportance” custom field. What I am getting hung up on is that a non-expired post could either have wp_postmeta.meta_key=’_atropos_expiration_date’ with a value > now() or it could not even have an expiration date listed. And the same with the “myimportance” meta_key. Without a specific myimportance value, it would get the lowest importance level.

    Here’s what I have so far:

    SELECT wp_term_relationships.object_id, wp_term_relationships.term_taxonomy_id, wp_terms.name, wp_posts.post_title, wp_posts.guid, wp_posts.post_modified from wp_posts right join (select max(post_modified) as newdate, post_parent as p from wp_posts j group by post_parent) n on wp_posts.post_parent=n.p and wp_posts.post_modified=n.newdate left join wp_term_relationships on wp_posts.post_parent=wp_term_relationships.object_id left join wp_terms on wp_term_relationships.term_taxonomy_id=wp_terms.term_id where term_taxonomy_id='7' and wp_posts.post_type<>'attachment';

    I think it would be faster to have a good mysql query that gets me exactly what I need than to loop through the results in php and then check meta_key values for expiration and importance with new queries. But maybe not.

    Any ideas? Is there a better place to ask this sort of question?

    Thanks!

Viewing 1 replies (of 1 total)
  • Thread Starter rramsey

    (@rramsey)

    I figured it out, but the sql statement could definitely be optimized:

    SELECT wp_posts.id, wp_postmeta.meta_key, wp_postmeta.meta_value, wp_postmeta.post_id as mypost, (select wp_postmeta.meta_value from wp_postmeta where wp_postmeta.meta_key='_atropos_expiration_date' and wp_postmeta.post_id=mypost) as expiration, wp_posts.post_content, wp_term_relationships.object_id, wp_term_relationships.term_taxonomy_id, wp_terms.name, wp_posts.post_title, wp_posts.guid, wp_posts.post_modified from wp_posts right join (select max(post_modified) as newdate, post_parent as p from wp_posts j group by post_parent) n on wp_posts.post_parent=n.p and wp_posts.post_modified=n.newdate left join wp_postmeta on wp_postmeta.post_id=wp_posts.post_parent left join wp_term_relationships on wp_posts.post_parent=wp_term_relationships.object_id left join wp_terms on wp_term_relationships.term_taxonomy_id=wp_terms.term_id where term_taxonomy_id='8' and wp_posts.post_type<>'attachment' and wp_posts.post_status<>'draft' group by wp_posts.id having (expiration is null or expiration >now()) limit 3

    So if someone else is searching, this is what worked for me.

Viewing 1 replies (of 1 total)
  • The topic ‘mysql query for nonexpired posts for multiple tags’ is closed to new replies.