WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Restricting Custom SQL by Certain Categories (5 posts)

  1. bcreighton
    Member
    Posted 6 years ago #

    Hi,

    I want to exclude two categories on my index template, however, I can't quite figure out the syntax for the custom sql query.

    I've taken a look at Otto42's custom category select statement, however it also restricts and orders it by custom meta data.

    $querystr = "
        SELECT $wpdb->posts.*
        FROM $wpdb->posts
        LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
        LEFT JOIN $wpdb->post2cat ON ($wpdb->posts.ID = $wpdb->post2cat.post_id)
        WHERE $wpdb->postmeta.meta_key = 'paragraf'
        AND $wpdb->posts.post_status = 'publish'
        AND $wpdb->posts.post_type = 'post'
        AND $wpdb->post2cat.category_id IN (1,2,3)
        ORDER BY $wpdb->postmeta.meta_value ASC
        ";

    I just want to exclude two categories and order by date. Can anyone help?

  2. bcreighton
    Member
    Posted 6 years ago #

    I feel like, I'm close... but I can't quite figure it out. This is what I have so far:

    SELECT $wpdb->posts.*
        FROM $wpdb->posts
        LEFT JOIN $wpdb->post2cat ON ($wpdb->posts.ID = $wpdb->post2cat.post_id)
        WHERE $wpdb->posts.ID = $wpdb->post2cat.post_id
        AND $wpdb->posts.post_status = 'publish'
        AND $wpdb->posts.post_type = 'post'
        AND $wpdb->post2cat.category_id IN (1,3,4,5,7,8)
        ORDER BY $wpdb->posts.post_date DESC

    Joining tables is slightly tricky... Please, any help on this?

  3. bcreighton
    Member
    Posted 6 years ago #

    Solved it, for anyone wondering:

    SELECT $wpdb->posts.*
        FROM $wpdb->posts INNER JOIN $wpdb->post2cat ON ID = post_id
        WHERE $wpdb->posts.post_status = 'publish'
        AND ($wpdb->posts.post_type = 'post'
        && $wpdb->post2cat.category_id NOT IN ( 2,6 ))
        ORDER BY $wpdb->posts.post_date DESC
  4. bcreighton
    Member
    Posted 6 years ago #

    Does anyone know how to update this to the new table structure?

  5. MichaelH
    Member
    Posted 6 years ago #

Topic Closed

This topic has been closed to new replies.

About this Topic