Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have event posts with multiple custom date and time fields. To pull all the events happening this week, I am querying on the different date fields. Here is my code:

$today=date("Y-m-d");
$nextweek= date("Y-m-d", strtotime("+7 days"));

$args2 = array(

'post_type' => 'post',
'meta_query' => array(
'relation' => 'OR',

array(
'key' => 'date2',
'value' => array( $today, $nextweek ),
'compare' => 'BETWEEN'
),
array(
'key' => 'date3',
'value' => array( $today, $nextweek ),
'compare' => 'BETWEEN'
),
array(
'key' => 'date1',
'value' => array( $today, $nextweek ),
'compare' => 'BETWEEN'

)
)
);
$query2 = new WP_Query( $args2 ); ?>

When I put var_dump($query2->request) after this, I get the following string:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 
WHERE 1=1 AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND ( 
  (wp_postmeta.meta_key = 'date2' AND CAST(wp_postmeta.meta_value AS CHAR) BETWEEN '2013-05-21' AND '2013-05-28') 
  OR (mt1.meta_key = 'date3' AND CAST(mt1.meta_value AS CHAR) BETWEEN '2013-05-21' AND '2013-05-28') 
  OR (mt2.meta_key = 'date1' AND CAST(mt2.meta_value AS CHAR) BETWEEN '2013-05-21' AND '2013-05-28') 
) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC LIMIT 0, 10

This is pulling the correct posts, but now I want to sort the results on the date1 field. I tried this and it isn't working. I end up with all of the events posted, and not sorted either. I've also tried the 'meta_value' for orderby without luck. The values in the date1 field are in date format YYYY-MM-DD Any help would be appreciated.

$args2 = array(
'order' => 'ASC',
'meta_key' => 'date1',
'orderby' => 'meta_value_num',
'post_type' => 'post',
'meta_query' => array(
'relation' => 'OR',

array(
'key' => 'date2',
'value' => array( $today, $nextweek ),
'compare' => 'BETWEEN'
),
array(
'key' => 'date3',
'value' => array( $today, $nextweek ),
'compare' => 'BETWEEN'
),
array(
'key' => 'date1',
'value' => array( $today, $nextweek ),
'compare' => 'BETWEEN',

)
)

);

$query2 = new WP_Query( $args2 ); ?>

When I put var_dump($query2->request) after this I get:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id 
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 
INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id) 
WHERE 1=1 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND (wp_postmeta.meta_key = 'date1' 
  OR (mt1.meta_key = 'date2' AND CAST(mt1.meta_value AS CHAR) BETWEEN '2013-05-21' AND '2013-05-28') 
  OR (mt2.meta_key = 'date3' AND CAST(mt2.meta_value AS CHAR) BETWEEN '2013-05-21' AND '2013-05-28') 
  OR (mt3.meta_key = 'date1' AND CAST(mt3.meta_value AS CHAR) BETWEEN '2013-05-21' AND '2013-05-28') 
) 
GROUP BY wp_posts.ID 
ORDER BY wp_postmeta.meta_value+0 ASC LIMIT 0, 10 
share|improve this question
    
Put var_dump($query2->request); right below the new WP_Query line and post the output, please. –  s_ha_dum May 18 '13 at 14:24
    
Also, what are all these different date fields doing? Why do you need 3 (or more maybe)? –  s_ha_dum May 18 '13 at 15:57
    
I have different date fields because one film can be scheduled to show on multiple dates. I will post separately the results from the var_dump($query2 - > request); –  Heather Goff May 21 '13 at 12:57
    
I'll see what I can do. Please provide the var_dump I asked for. –  s_ha_dum May 21 '13 at 12:59
    
Hi s_ha_dum, I had to add the var_dump results to the question above. Thank you! –  Heather Goff May 21 '13 at 13:03
add comment

1 Answer

Your meta fields are JOINing as mt* aliases in that query. Look for the lines like:

INNER JOIN wp_postmeta AS mt1 ON ...

But the ORDER BY parameter is just wp_postmeta.meta_value, which doesn't actually make all that much sense. If that isn't a bug, I call it bad design or very limited design, but moving on.

To specifically target one of your meta values, the first one, you'd need that ORDER BY to be...

ORDER BY mt1.meta_value

You don't want the meta_value_num, by the way, because you don't have numbers. There are dashes in there. MySQL will interpret the date '2013-12-15' + 0 as just '2013'. Put SELECT ('2013-12-15')+0 into PhpMyAdmin's SQL panel and run the query.

To do this with the data you have you will need to create a filter for your query. Add a parameter to your query...

$args2 = array(
  'my_orderby_field' => 'mt1',
  'order' => 'ASC',
  'meta_key' => 'date1',
  'orderby' => 'meta_value',
  /* ... the rest */

function order_by_one_meta($orderby,$qry) {
  $myorder = $qry->get('my_orderby_field');
  if (!empty($myorder)) {
    global $wpdb;
    $orderby = str_replace($wpdb->postmeta,$myorder,$orderby);
  }
  return $orderby;
}
add_filter('posts_orderby','order_by_one_meta',100,2);

Adding your own field to the query is a sort of a hack. I don't know it that works by design or if it is an unintentional side effect, so it does count as a somewhat dangerous piece of code. Another way to do it is simple to create the callback:

function order_by_one_meta($orderby) {
  $orderby = str_replace($wpdb->postmeta,$myorder,$orderby);
  return $orderby;
}

Apply it and then remove it where it is needed.

add_filter('posts_orderby','order_by_one_meta',100);
$query2 = new WP_Query( $args2 ); 
remove_filter('posts_orderby','order_by_one_meta',100);

Less dangerous, not as interesting. :(

I can't test that as I don't have your data in the database, but the SQL should be right.

I am not sure why you need multiple date keys though. Using the same key multiple times-- say _scheduled_event_dates-- should give you the same effect, and would make for more efficient queries and code.

share|improve this answer
    
Thank you so much for your expertise and help. When I try your suggestions, it pulls all of the events from the database, not just the events within the week. –  Heather Goff May 21 '13 at 14:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.