How to order posts by one custom field and filter them by another one?

Question

In my WordPress blog I have events I want to order and filter by custom fields. The order should be done by the custom field "event_date_end" which means that every event that is still running should be displayed.

To order the events I use a second custom field "event_date_start". So an event, that start on the 10th and ends on the 20th it should be displayed until the 20th but should be order next to all other events starting at the 10th.

I use Thematic as a parent theme and so I use the query_posts() function adding both rules as parameters. This is how I use it in the index loop:

function my_index_loop($content) {
    global $query_string;
    query_posts($query_string
    // filter by event_date_end
    ."&meta_key=event_date_end&meta_compare=>=&meta_value=".date('Y-m-d')
    // order by event_date_start
    ."&meta_key=event_date_start&orderby=meta_value&order=ASC"); 
}
add_filter('thematic_indexloop', 'my_index_loop');

Now the posts are correctly ordered, but also the filter will be done on the "event_date_start" value. So an event, that has been started yesterday and still going on, will not be displayed.

If I switch the order of the "filter" and "order" parts, I will see all events, but an event starting at the 10th and ending at the 20th will be placed to all event on the 20th.

I know I can create queries in SQL myself, but will they work with Thematic? And how do I have to write statements for archive, category, tag listing?

The solution:

After reading the mentioned pages, I found the following solution. I had to use some raw SQL and add them through filters within the specific loop filter. These are the functions I added:

function my_loop_where($where){
    global $wpdb;
    $where .= " AND pm_where.meta_value >= '".date('Y-m-d')."'";
    return $where;
}

function my_loop_join($join){
    global $wpdb;
    $join .= " LEFT JOIN " . $wpdb->postmeta . " AS pm_order ON (" . $wpdb->posts . ".ID = pm_order.post_id AND pm_order.meta_key = 'event_date_start')";
    $join .= " LEFT JOIN " . $wpdb->postmeta . " AS pm_where ON (" . $wpdb->posts . ".ID = pm_where.post_id AND pm_where.meta_key = 'event_date_end')";
    return $join;
}

function my_loop_order(){
    return 'pm_order.meta_value ASC';
}

And than I simply added filter to these functions within the e.g. index loop:

function my_index_loop($content) {
    global $query_string;
    add_filter('posts_where', 'my_loop_where' );    
    add_filter('posts_join', 'my_loop_join' );
    add_filter('posts_orderby', 'my_loop_order' );
    query_posts($query_string . "&meta_key=event_date_end&meta_compare=>=&meta_value=".date('Y-m-d')/*."&meta_key=event_date_start&orderby=meta_value&order=ASC"*/);
}
add_filter('thematic_indexloop', 'my_index_loop');

Now I can easily use that filter and order on the index and category loops but keep the default "log style" filter and orders on the archive pages.

Answer 1

This is not going to work, because you are setting same meta_key parameter twice with different values and only one will be effective.

Rather than building raw queries it is better to use filters to modify WP query. Query Overview documentation has list of hooks where you can insert your mods (as the end under What Plugins can Modify).