CODEWITHSUNDEEP
wordpress
andys
andys

Reputation: 708

Wordpress: Display posts by custom select query

I need to get posts by custom category id, sort by custom field value and with another custom field if this field exists. I think I need to use custom selection query. Look at my query: The problem is that this query returns the same post 5 times... In admin panel I made 20 posts per page. Maybe someone have their own solution ? Thanks.

$wp_query->request = "SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
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->terms.term_id = $wpdb->term_taxonomy.term_id)
WHERE $wpdb->terms.slug = 'categoryname'
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_type = 'post'
ORDER BY $wpdb->posts.post_date DESC";
$pageposts = $wpdb->get_results($wp_query->request, OBJECT);

Upvotes: 1

Views: 2354

Answers (3)

Vijaya Narayanasamy
Vijaya Narayanasamy

Reputation: 468

  1. Try this-

    get_results( " 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) WHERE $wpdb->posts.post_status = 'publish' AND $wpdb->term_taxonomy.taxonomy = 'category' AND $wpdb->term_taxonomy.term_id = 3 ORDER BY post_date ASC " ); ?>

Upvotes: -1

Roman
Roman

Reputation: 146

Why you use "$wp_query->request", this variable is for retrieving last query result, not to set new one...

just try

$my_custom_sql = "SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
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->terms.term_id = $wpdb->term_taxonomy.term_id)
WHERE $wpdb->terms.slug = 'categoryname'
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_type = 'post'
ORDER BY $wpdb->posts.post_date DESC";
$pageposts = $wpdb->get_results($my_custom_sql, OBJECT);

Upvotes: 0

Joshc
Joshc

Reputation: 3853

$customSelect = new WP_Query(array(

    'post_type'       => 'post',      
    'posts_per_page'  => 10,
    'taxonomy'        => 'category',
    'order'           => 'DESC',
    'meta_key'        => 'your custom field',
    'orderby'         => 'meta_value'

This is probably way off what your trying to achieve, the only bit I can't get my head round is how to test if meta key exists using a variable or something.

Anyways good luck

Upvotes: 2

Related Questions