Support » Fixing WordPress » Issue when displaying posts using a custom select query

  • Resolved leandrovieira

    (@leandrovieira)


    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

Viewing 3 replies - 1 through 3 (of 3 total)
  • 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

    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

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

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Issue when displaying posts using a custom select query’ is closed to new replies.