Multiple meta keys in wp_query

Hi @guido07111975, maybe to take a look at Codex:
Codex Custom Field Parameters

'meta_key' => 'key',                 
    'meta_value' => 'value',               
    'meta_value_num' => 10,               
    'meta_compare' => '=',             
    'meta_query' => array(               
       'relation' => 'AND',          
       array(
         'key' => 'color',                
         'value' => 'blue'                
         'type' => 'CHAR',        
         'compare' => '='       
       ),
       array(
         'key' => 'price',
         'value' => array( 1,200 ),
         'compare' => 'NOT LIKE'
       )
    ), 

Heya Guido!

Ugh! Not second queries, that can get messy fast. The problem is SQL sees both meta data types as values in one column and will try to order as such, ignoring the keys. The only solutions I see are either post processing in PHP, or placing event-order values somewhere besides meta_value. I’d be SO tempted to use the post’s menu_order field, it’s really what the field is intended for, except for menus, not plugin dev use. It would be fine right now, but I’d be concerned some other use in the future could override our usage.

If your events have commenting disabled, I suppose using the comment_count field could work. Or if media is not involved, maybe mime_type? I feel a little dirty even suggesting it ?? But the only fully legitimate way to separate columns would be a custom table, which greatly complicates any queries, to the point post processing in PHP starts to sound better. None of the solutions seem ideal.

In what form are dates stored? Would managing dates as timestamps, including the fractional time portion be viable? Then order can be managed all at once by simply strategically allocating an appropriate time of day for each event. Or if not timestamps, as long as the date/time is similar to RFC 2822, the end result would be the same.

@apsolut:
I have already taken a look at the codex… but wasn’t able to find a solution for this issue.

@bcworkz:
Hi man, I understand there’s no simple solution for this. Too bad. I use the (native?) datepicker for adding events, and also list my events using the timestamp which is added in DB by the datepicker. But I cannot use the “time” from this timestamp because it’s (almost) the same as publishing date. Or I should change my datepicker into a combined date/time picker. But in that case I should include a third party script, it’s not part of the native jquery library (if I’m not mistaken).

I don’t understand how I could use mime_type for ordering events?

Guido

I almost found a solution… using a second meta_key in my query args.

Have added an extra field called “event_order” so user can manually add a number to each event.

In my query I check for this value, if present, I use it. If not, I use event start date to list events in correct order.

// get date values from DB
$start = get_post_meta( get_the_ID(), 'event-start-date', true );
$end = get_post_meta( get_the_ID(), 'event-end-date', true );

// determine which key to use
if ($start == $end) :
	if (!empty('event-order')) :
		$key = 'event-order';
	else :
		$key = 'event-start-date';
	endif;
else :
	$key = 'event-start-date';
endif;

My query args:

'meta_key' => $key, 
'orderby' => 'meta_value_num', 

The only thing I cannot solve yet is to apply the number ordering ONLY if number of events with the same date is greater than 1. But have no time left at the moment ??

Guido

The only thing I cannot solve yet is to apply the number ordering ONLY if number of events with the same date is greater than 1.

And this ordering must only apply between the events with the same date of course.

(in my query args I determine which events to display, using the meta_query arg)

The mime_type column is just an unused place (for events) to save data. Completely unrelated to media or mime_types. I was just looking at table structures and identifying under utilized columns. They all of course have some use, but not for events. I’m assuming you at least are checking your products against WP release candidates, so if you were to “cheat” a little and use menu_order or whatever for ordering events, you’d have fair warning should such usage break something with a future release.

The only solution I see is to save order data in a separate column, then you can do something like 'orderby' => 'meta_value_num menu_order',

I just double checked the orderby docs to be sure menu_order was a valid argument and found this:

[menu_order] could be used for any post type with distinct ‘menu_order’ values

For me, this indicates this column is fair game for any post type that needs it! Not just menus. Do it! ??

Hi,

Thanks! I will take a look at it.

Unfortunately there’s still 1 thing I don’t understand (for quite some time now), regarding the orderby parameter, which contains 2 values:

'meta_key' => 'event-start-date', 
'orderby' => 'meta_value_num menu_order',

I only see the first value (meta_value_num / ‘event-start-date’) is being ignored. So the second value overrules the first one. Could you please explain how this works?

Guido

Hmmm. Not sure why the meta value would be ignored. It’s supposed to work the way you want it to work, the second only a factor for identical meta values. Something is not working correctly. You should check the actual SQL (WP_Query::request or use the “posts_request” filter) and determine why it’s failing to work as expected.

While the table references will likely be aliases, the order clause should be equivalent to ORDER BY wp_postmeta.meta_value+0, wp_posts.menu_order DESC

I’m unsure without more digging how the date picker stores dates. Could simple orderby meta_value work better? You could instead try using the array form of orderby meant for separate ordering directions.

Oooo! I just found out that we can assign an ordering key in “meta_query” arrays that can be used to order by multiple meta values! Check it out: https://wordpress.stackexchange.com/questions/246355/order-by-multiple-meta-key-and-meta-value/246358#246358

I suspect using a separate column like menu_order will be faster, but that’s pretty cool!

Hi BC,

The datepicker stores raw dates as Unix timestamp: 1539993600

That’s why I simply use 'orderby' => 'meta_value_num' to determine event order. Works perfectly, with a single meta key.

I will look into your suggestions, but currently I don’t have the patience nor time to look into this closely. I still find this quite difficult to understand.

Have a nice weekend and as always: thank you!

Guido

Hi again,

Found out that supporting the ‘page-attributes’ while registring the event post type and using the ‘menu_order’ (as mentioned before) in my query is a quick (and best?) way to do this properly. User can easily create a custom event order.

So now I only need to find a way to use this order ONLY between events with the same date. Guess by creating a nested query as @apsolut already suggested?

My example query only selects events with today’s date or later:

$today = strtotime( 'today' );
$my_meta_query = array( 
	'relation' => 'AND',
	array( 
		'key' => 'event-date', 
		'value' => $today, 
		'compare' => '>=', 
		'type' => 'NUMERIC'
	) 
); 

'meta_key' => 'event-date', 
'orderby' => 'meta_value_num', 

So how do I add a second array, which uses the event order? And apply this only to events with the same date?

As always, any help appreciated..

Guido

I don’t see why it needs to be conditional in PHP, it’s always conditional in SQL (in a way). When the ORDER BY clause looks like the equivalent of ORDER BY wp_postmeta.meta_value+0 DESC, wp_posts.menu_order ASC, results are first ordered by the date in meta_value. If there is only one record for a particular date value, that’s the end of it, SQL does not even look at menu_order, there would be no point. Only when there are identical date values will SQL then further sort the results by the menu_order field.

If you want the dates ordered oldest first, your query args can be in part

'meta_key' => 'event-date',
'orderby' => 'meta_value_num menu_order', 
'order' => 'ASC',

But if the order of each column is different, we need to use the array form of ‘orderby’

'meta_key' => 'event-date',
'orderby' => array( 'meta_value_num' => 'DESC', 'menu_order' => 'ASC' ),

In either case we can include menu_order even if there is not a single duplicate date. It only comes into play when there is a duplicate date. Now, should there be duplicate dates and menu_order is not user defined (actually set to default 0), technically the sub-ordering is undefined, but AFAIK the sub-order will be the order records appear in the table.

Hi BC,

Have thought about it again and I think it’s impossible to do this with the current native orderby parameters.

Because via the meta query I select all events with today’s date or later.
Than I order them on date via meta_value_num.
In case I apply the menu_order to those events as well, this will apply to ALL events (and I only want this for events with the same date).. as you’ve mentioned all events without order being set have default value 0.
So the menu order will overrule the meta_value_num order.
So this is going nowhere I assume..
Have to look for something else.

Guido