WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Issue when displaying posts using a custom select query (4 posts)

  1. leandrovieira
    Member
    Posted 4 years ago #

    Hi,

    I need to display some posts using a custom select query. This custom select query needs to get the posts in two taxonomies.

    Look the query:

    SELECT *
    FROM $wpdb->posts
    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID =
    $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy
    ON($wpdb->term_relationships.term_taxonomy_id =
    $wpdb->term_taxonomy.term_taxonomy_id)
    LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id =
    $wpdb->terms.term_id)
    WHERE
    $wpdb->posts.post_type = 'post'
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->term_taxonomy.taxonomy = 'continentes'
    AND $wpdb->terms.slug = 'asia'
    AND $wpdb->term_taxonomy.taxonomy = 'pais'
    AND $wpdb->terms.slug = 'china'
    ORDER BY $wpdb->posts.post_date DESC

    If a just use one condition the query works well, like it:

    ...
    AND $wpdb->term_taxonomy.taxonomy = 'continentes'
    AND $wpdb->terms.slug = 'asia'
    ...

    But I need the posts in taxonomy "continentes" and "pais".

    Best regards

  2. vtxyzzy
    Member
    Posted 4 years ago #

    I do not have a way to test this, but this should be close to what you want:

    SELECT *
    FROM $wpdb->posts
    LEFT JOIN $wpdb->term_relationships tr1 ON($wpdb->posts.ID = tr1.object_id)
    LEFT JOIN $wpdb->term_taxonomy tt1 ON(tr1.term_taxonomy_id = tt1.term_taxonomy_id AND tt1.taxonomy = 'continentes')
    LEFT JOIN $wpdb->terms t1 ON(tt1.term_id = t1.term_id and t1.slug = 'asia')
    LEFT JOIN $wpdb->term_relationships tr2 ON($wpdb->posts.ID = tr2.object_id)
    LEFT JOIN $wpdb->term_taxonomy tt2 ON(tr2.term_taxonomy_id = tt2.term_taxonomy_id AND tt2.taxonomy = 'pais')
    LEFT JOIN $wpdb->terms t2 ON(tt2.term_id = t2.term_id and t2.slug = 'chins')
    WHERE
    $wpdb->posts.post_type = 'post'
    AND $wpdb->posts.post_status = 'publish'
    ORDER BY $wpdb->posts.post_date DESC
  3. leandrovieira
    Member
    Posted 4 years ago #

    Hi,

    Thanks to reply.

    I have tested, but it doesn't functions too.

    I have this way:

    SELECT DISTINCT post_title
    FROM $wpdb->posts
    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID =
    $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy
    ON($wpdb->term_relationships.term_taxonomy_id =
    $wpdb->term_taxonomy.term_taxonomy_id)
    LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id =
    $wpdb->terms.term_id)
    WHERE
    $wpdb->posts.post_type = 'post'
    AND $wpdb->posts.post_status = 'publish'
    AND ( $wpdb->term_taxonomy.taxonomy = 'continentes' OR $wpdb->term_taxonomy.taxonomy = 'pais' )
    AND ( $wpdb->terms.slug = 'asia' OR $wpdb->terms.slug = 'china' )
    ORDER BY $wpdb->posts.post_date DESC

    Suggested here http://stackoverflow.com/questions/2595356/wordpress-issue-when-displaying-posts-using-a-custom-select-query

    REgards

  4. vtxyzzy
    Member
    Posted 4 years ago #

    Glad you got it to work. Now, please use the dropdown at top right to mark this topic 'Resolved'.

Topic Closed

This topic has been closed to new replies.

About this Topic