Need help with complex SQL query

  • Resolved perarne

    (@perarne)


    14 years, 5 months ago

    Hello,

    I’m currently working on a WordPress site with a custom post type ‘note’ that has a custom hierarchical taxonomy called ‘note_category’. Each post of type ‘note’ has a custom field called ‘display_priority’.

    On the front page of the site, all posts of type ‘note’ having a ‘display_priority’ value of 1 are listed in date order followed by all other notes in date order. I achieved this using the following custom SQL query string, which seems to work fine.

    $querystr = "
	    (SELECT $wpdb->posts.*
	    FROM $wpdb->posts, $wpdb->postmeta
	    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
	    AND $wpdb->postmeta.meta_key = 'display_priority'
	    AND $wpdb->postmeta.meta_value = '1'
	    AND $wpdb->posts.post_status = 'publish'
	    AND $wpdb->posts.post_type = 'note'
	    ORDER BY $wpdb->posts.post_date DESC)
	    UNION ALL
	    (SELECT $wpdb->posts.*
	    FROM $wpdb->posts, $wpdb->postmeta
	    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
	    AND $wpdb->postmeta.meta_key = 'display_priority'
	    AND $wpdb->postmeta.meta_value != '1'
	    AND $wpdb->posts.post_status = 'publish'
	    AND $wpdb->posts.post_type = 'note'
	    ORDER BY $wpdb->posts.post_date DESC)";

    Now for my problem: I need to modify the above query to only return entries beloning to a given ‘note_category’. How would one go about this?

    I’m pretty new to both SQL and the inner workings of WordPress so any help would be greatly appreciated.

Viewing 11 replies - 1 through 11 (of 11 total)
  • James Laws

    (@jameslaws)

    14 years, 5 months ago

    I think you might be working a little to hard for this. Everything you are wanting to do can be more easily accomplished WP_query class: http://codex.ww.wp.xz.cn/Class_Reference/WP_Query

    It will allow you do query your posts with taxonomy and custom meta conditions. Check it out and if you have any questions let me know.

    Thread Starter perarne

    (@perarne)

    14 years, 5 months ago

    thanks for your quick reply and for making me read the WP_Query documentation again…

    the following query gets me exactly what i want except the posts are in reverse order. 

    $taxArgs = array(array('taxonomy' => 'note_category',
		       'terms' => 'the_category_slug',
		       'field' => 'slug'));

$the_query = new WP_Query(array('post_type' => 'note',
				'orderby' => 'meta_value date',
				'meta_key' => 'display_priority',
				'order' => 'ASC',
				'tax_query' => $taxArgs));

    changing ‘order’ only seems to affect the sorting by the second ‘orderby’ value.

    i could probably add a hack to reverse the result, but a proper solution would be nice. any ideas?

    James Laws

    (@jameslaws)

    14 years, 5 months ago

    The problem is with the orderby declaration I think. You don’t want to orderby => meta_value but by date. The meta_query will simply make sure that display priority is the one you are looking for.

    Like you did your $taxArgs you may want to do a $metaArgs as well. Something like…

    $metaArgs = array(array(
 			'key' => 'display_priority',
 			'value' => 1,
 			'type' => 'numeric',
 			'compare' => '='));

    and then the query would be something like…

    $the_query = new WP_Query(array('post_type' => 'note',
				'orderby' => 'meta_value date',
				'meta_key' => 'display_priority',
				'order' => 'ASC',
				'tax_query' => $taxArgs,
                                'meta_query' => $metaArgs));
    Thread Starter perarne

    (@perarne)

    14 years, 5 months ago

    i tried what you suggested but it seems that the ‘meta_query’ args affect selection as opposed to ordering, so i only get posts having a given display_priority value. or am i missing something here?

    what i tried to achieve by using multiple ‘order_by’ values (as described here) in my previous post was to get a result containing posts having display_priority values of both 0 and 1, with posts having values of 1 appearing first ordered by date (descending) followed by the rest of the posts also ordered by date, hence 'orderby' => 'meta_value date'

    thanks again for helping me out.

    James Laws

    (@jameslaws)

    14 years, 5 months ago

    Well, just as you described in your initial question, I assumed you would do this twice. Once for the display_priority of 1 and once for the ones that didn’t. Still two separate queries but using the wp_query class.

    This just makes it easy to grab the data you want based on the category and on the meta_value.

    If that makes sense.

    Thread Starter perarne

    (@perarne)

    14 years, 5 months ago

    it sure does make sense. i’ll go with that solution. just wanted to see if it was possible to achieve the desired result in one query.

    thanks!

    James Laws

    (@jameslaws)

    14 years, 5 months ago

    You are very welcome. And yeah, I’m not aware of it being possible with one query, in fact I’m pretty sure it isn’t. But this will definitely get you what you needed.

    Glad I was able to help a little.

    Thread Starter perarne

    (@perarne)

    14 years, 5 months ago

    hmm… just realized i need to implement pagination, which will be much easier if i only do one query that actually returns exactly what i need (as opposed to using multiple queries or manipulating the order of the returned post after the query has been made).

    this brings me back to my second post i guess: i need to reverse the order of the results generated by

    $taxArgs = array(array('taxonomy' => 'note_category',
		       'terms' => 'the_category_slug',
		       'field' => 'slug'));

$the_query = new WP_Query(array('post_type' => 'note',
				'orderby' => 'meta_value date',
				'meta_key' => 'display_priority',
				'order' => 'ASC',
				'tax_query' => $taxArgs));

    and i want to achieve this by manipulating the query somehow.

    any ideas?

    thanks

    James Laws

    (@jameslaws)

    14 years, 5 months ago

    I could be completely wrong but I don’t think what you want to do it possible whether by WP_Query or MySql. You can only orderby one column.

    Thread Starter perarne

    (@perarne)

    14 years, 5 months ago

    the least ugly way i could come up with was to make 1 and 0 mean low and high display priority respectively. this way the default sorting with 'order' => 'DESC' gives me exactly what i want. thanks for pointing me in the right direction.

    James Laws

    (@jameslaws)

    14 years, 5 months ago

    Glad you found something that works for you. 🙂

Viewing 11 replies - 1 through 11 (of 11 total)

The topic ‘Need help with complex SQL query’ is closed to new replies.