WordPress.org

Ready to get started?Download WordPress

Forums

query_posts -> get results based on multiple criteria (5 posts)

  1. TwoThirdsWater
    Member
    Posted 3 years ago #

    Hi...

    I've been using the Wp_query command for a while, to create my own custom loops, but I'm not sure how to proceed with my current problem.

    I first want to sort on title name, this is easy... see arg's below.

    $args = array(
    										  'showposts' => -1,
    										  'orderby' => 'title',
    										  'cat' => get_post_meta($this_post_id, "course_category_id", true),
    										  'category__not_in' => get_post_meta(getSettingsPageID(), "demonstration_category_id", true),
    										  'order' => 'ASC'
    										);		
    
    							$temp = $wp_query;
    							$wp_query= null;
    							$wp_query = new WP_Query($args);

    Within the same query I then want to sort on a custom field... this by itself I can also do as:

    $args2 = array(
    										  'showposts' => -1,
    										  'meta_compare' => '>=',
    										  'meta_key' => 'date',
    										  'meta_value' => date("Ymd"),
    										  'orderby' => 'meta_value',
    										  'order' => 'ASC'
    										  'category__and' => array(get_post_meta($this_post_id, "course_category_id", true), get_post_meta(getSettingsPageID(), "demonstration_category_id", true))
    										);

    but I'd like to be able to do both of these queries in a single call.

    eg:
    get posts in category X
    sort by title
    sort all posts with the same title by custom field

    The only way I can think to do it at the moment is to get all the titles, then for each title do a seperate query sorted on date. There must be a better way!

    Any help gratefully recieved.

    Tom

  2. TwoThirdsWater
    Member
    Posted 3 years ago #

    Any one? Sorry to bump this up the queue, but any advise much appreciate.

    Thank you!

  3. TwoThirdsWater
    Member
    Posted 3 years ago #

    OK... i think I'm going to need to write some custom SQL for this, as the wp_query arg 'orderby' won't accept two parameters.

    What I'm looking to recreate is SQL that will achieve what the following set of wp_query args won't do (note the illegal orderby array):

    $argsLesson = array(
    	'showposts' => -1,
    	'meta_compare' => '>=',
    	'meta_key' => 'date',
    	'meta_value' => date("Ymd"),
    	'orderby' => array('title','meta_value'),
    	'order' => 'ASC',
    	'cat' => 10,
    	'category__not_in' => 20
    	);

    the SQL I've written so far is below, but it seems to be plucking posts and pages randomly out of the air. I've never written SQL this complex for my own applications, let alone as part of a wordpress installation, so any tips very much appreciated.

    $lessonQuery = "SELECT * FROM $wpdb->posts
    	LEFT JOIN $wpdb->postmeta AS Date ON( $wpdb->posts.ID = Date.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)
    	WHERE $wpdb->posts.post_status = 'publish'
    	AND $wpdb->term_taxonomy.taxonomy = 'category'
    	AND $wpdb->term_taxonomy.term_id = 10
    	AND $wpdb->term_taxonomy.term_id != 20
    	AND Date.meta_key = 'date'
    	AND Date.meta_value >= " . date("Ymd") . "
    	ORDER BY $wpdb->posts.post_title, Date.meta_value ASC"
  4. TwoThirdsWater
    Member
    Posted 3 years ago #

    I think the last query might be a bit outdated... am trying something like the following, but getting the same random results.

    SELECT wposts.* FROM wp_leiths_posts wposts
    LEFT JOIN wp_leiths_postmeta wpostmeta ON wposts.ID = wpostmeta.post_id LEFT JOIN wp_leiths_term_relationships ON (wposts.ID = wp_leiths_term_relationships.object_id)
    LEFT JOIN wp_leiths_term_taxonomy ON (wp_leiths_term_relationships.term_taxonomy_id = wp_leiths_term_taxonomy.term_taxonomy_id)
    WHERE wpostmeta.meta_key = 'date'
    AND wpostmeta.meta_value >= 20100713
    AND wp_leiths_term_taxonomy.taxonomy = 'category'
    AND wp_leiths_term_taxonomy.term_id = 10
    AND wp_leiths_term_taxonomy.term_id != 20
    ORDER BY wposts.post_title, wpostmeta.meta_value ASC

    Please help!

  5. TwoThirdsWater
    Member
    Posted 3 years ago #

    Really? Can no-one give me a heads up on the syntax of a custom SQL statement here please?

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.