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

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.