Complex custom SELECT, posts and tags

  • hi

    Having a small issue with a custom query (below) – any mysql gurus out there? Not going to explain what it does so if you can’t read it please don’t respond but essentially the return of posts and tags is not quite matched, can anyone see why?

    a+
    gar

    $query = "SELECT DISTINCT wp_posts.post_title, wp_terms.name AS tag_name FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) LEFT JOIN wp_terms ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id) WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'post' AND wp_term_taxonomy.taxonomy = 'post_tag' AND wp_term_taxonomy.count > 0 AND EXISTS(SELECT NULL FROM wp_term_relationships LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE wp_term_relationships.object_id = wp_posts.ID AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_relationships.term_taxonomy_id NOT IN (1)) ORDER BY post_date DESC";

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

    (@asquare)

    This seems to be better

    $query = "SELECT DISTINCT wp_posts.post_title, wp_terms.name AS tag_name FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_terms ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id) LEFT JOIN wp_term_taxonomy ON (wp_terms.term_id = wp_term_taxonomy.term_id) WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'post' AND wp_term_taxonomy.taxonomy = 'post_tag' AND wp_term_taxonomy.count > 0 AND EXISTS(SELECT NULL FROM wp_term_relationships LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE wp_term_relationships.object_id = wp_posts.ID AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_relationships.term_taxonomy_id NOT IN (1)) ORDER BY post_date DESC";

Viewing 1 replies (of 1 total)
  • The topic ‘Complex custom SELECT, posts and tags’ is closed to new replies.

Tags