Ask your WordPress questions! Pay money and get answers fast! Comodo Trusted Site Seal
Official PayPal Seal

Sort by Meta Value Num and Filter Results WordPress

  • SOLVED

Hi everyone,

This is a two part problem related to sorted posts by meta keys / meta values.

Problem #1

I have a site dedicated to sporting events and have three example posts below and I am sorting posts by meta_key => date and order "descending" and this is what I get;

03/01/2012 - Event C
01/12/2011 - Event A
01/01/2012 - Event B

*Note, the dates are listed in Non-US format - Day, Month, Year

You will notice that the order is,

C
A
B

but it should be

C
B
A

...based upon the order of the dates.


The strange thing is that IF I set the order property to "ascending" then I get,

01/12/2011 - Event A
01/01/2012 - Event B
03/01/2012 - Event C

Here is my code;



<?php
global $wp_query;
$args = array_merge( $wp_query->query, array(
'post_type' => 'sporet_event',
'meta_key' => 'start_date',
'orderby' => 'meta_value_num',
'order' => 'desc'
)
);
query_posts( $args );
if (have_posts()) : while (have_posts()) : the_post(); ?>


<li><?php echo get_post_meta($post->ID, 'start_date', true); ?></li>

<li><?php the_title(); ?></li>

<li><?php echo get_post_meta($post->ID, 'sport_type', true); ?></li>

<?php endwhile; ?>


Problem #2

I've also included a select field that enables users to sort the results by Start Date, Sport Type or Title by using this code;


<form name="myform" action="localhost/wordpress/events" method="GET">
<select name="rating-search" id="rating-search" OnChange="document.myform.submit()">
<option value="at_event_null">select</option>
<option value="at_event_type---asc">type asc</option>
<option value="at_event_type---desc">type desc</option>
<option value="at_event_start_date---desc">start desc</option>
<option value="at_event_start_date---asc">start asc</option>
<option value="place">place</option>

</select>
</form>


and normally I would do this to enable the select field to modify the results;


<?php

// Example 1
$select = $_GET['rating-search'];
$value = explode("---", $select);
$value[0]; // meta key
$value[1]; // asc or desc

global $wp_query;
global $wp_query;
$args = array_merge( $wp_query->query, array(
'post_type' => 'events',
'meta_key' => $value[0],
'orderby' => 'meta_value_num',
'order' => $value[1]
)
);
query_posts( $args );
if (have_posts()) : while (have_posts()) : the_post(); ?>


The issue is; that when I navigate to http://localhost/wordpress/events I need the first query in Problem 1 to be run and then when a user decides to filter results, I need the same query to be modified with the select values all the while still staying on the same page (it doesn't matter if the page needs to refresh but the permalink shouldnt change but adding events?filter=start_date is ok I guess)

Does this make sense guys?

Your help as always is appreciated.

WP

** UPDATE **

Thanks guys a combination of Gabriel's and Hai's answers were exactly what I needed.

Thanks to everyone else who chimed in, each answer was actually helpful in understand this problem in more depth.

Answers (6)

2011-12-22

Gabriel Reguly answers:

Hi WordPressing,

In which format are you storing your dates? start_date is Y-m-d or d/m/Y?

This most likely is your first issue.

Regards,
Gabriel


Wordpressing comments:

Hi Gabriel,

d/m/Y


Gabriel Reguly comments:

Hi Wordpressing,

For your second issue, please try this code:


$select = $_GET['rating-search'];
if ( ! empty( $select ) ) {
$value = explode("---", $select);
} else {
$value[0] = 'start_date';
$value[1] = 'desc';
}


Regards,
Gabriel


Gabriel Reguly comments:

Hi Wordpressing,

As John already explained,you will need to change the storing format of your dates.

Is post type events coming from a plugin or is that custom code?

Be warned that you will need to amend code for any instance where you display your events dates, but that is the correct way to deal with this issue.

Regards,
Gabriel


Wordpressing comments:

I am using a plugin Advanced Custom Fields so I don't think I can change the storing format of the date field unless I forgo the ability to use the datepicker.,


Gabriel Reguly comments:

Hi Wordpressing,

The datepicker ( from jQuery ) can output any date format, it shall be no issue.

Follows code from my <em>jquery.ui.datepicker-pt-BR.js</em>, I use it in a theme custom made for a client.


jQuery(function($){
$.datepicker.regional['pt-BR'] = {
closeText: 'Fechar',
prevText: '&#x3c;Anterior',
nextText: 'Pr&oacute;ximo&#x3e;',
currentText: 'Hoje',
monthNames: ['Janeiro','Fevereiro','Mar&ccedil;o','Abril','Maio','Junho',
'Julho','Agosto','Setembro','Outubro','Novembro','Dezembro'],
monthNamesShort: ['Jan','Fev','Mar','Abr','Mai','Jun',
'Jul','Ago','Set','Out','Nov','Dez'],
dayNames: ['Domingo','Segunda-feira','Ter&ccedil;a-feira','Quarta-feira','Quinta-feira','Sexta-feira','S&aacute;bado'],
dayNamesShort: ['Dom','Seg','Ter','Qua','Qui','Sex','S&aacute;b'],
dayNamesMin: ['Dom','Seg','Ter','Qua','Qui','Sex','S&aacute;b'],
weekHeader: 'Sm',
dateFormat: 'dd/mm/yy',
firstDay: 0,
isRTL: false,
showMonthAfterYear: false,
yearSuffix: ''};
$.datepicker.setDefaults($.datepicker.regional['pt-BR']);
});


Sorry, but I need to go offline.

Other experts shall be able to help you.

Good luck,
Gabriel

2011-12-22

Hai Bui answers:

Hi,

For problem #1: You should use this format for the date: yyyy/mm/dd and use 'orderby' => 'meta_value' instead of 'orderby' => 'meta_value_num'
I think this is the only way you can sort the results by date in custom field. If you need to display the date in a different format, you can use extra function to convert the date to the format you wanted.

For problem #2:

<?php

// Example 1

// if there is a rating-search value in url, then use it to sort, otherwise use 'start_date'
$select = $_GET['rating-search']?$_GET['rating-search']:'start_date---desc';

$value = explode("---", $select);

$value[0]; // meta key

$value[1]; // asc or desc


global $wp_query;

global $wp_query;

$args = array_merge( $wp_query->query, array(

'post_type' => 'events',

'meta_key' => $value[0],

'orderby' => 'meta_value_num',

'order' => $value[1]

)

);

query_posts( $args );

if (have_posts()) : while (have_posts()) : the_post(); ?>



Wordpressing comments:

Hi Hai,

I'm trying to implement the code for problem #2 but when I navigate to

localhost/wordpress/events

I receive a 404 Not Found

I am still able to use the select menu but I can't load the default page with it's default start_date query.

Also if I store the date in yyyy/mm/dd what is one such way that I can manipulate it's display in the browser back to dd/mm/yyyy?

Thank you,
WP


Hai Bui comments:

Should the field be 'at_event_start_date' instead of just 'start_date'? You use 'start_date' in the first example and 'at_event_start_date' in the form field values, I'm not sure which one is correct. If 'at_event_start_date' is the correct custom field, please change:

$select = $_GET['rating-search']?$_GET['rating-search']:'at_event_start_date---desc';

About converting the date format to 'dd-mm-yyyy', you can use this code:
<?php
$time = strtotime($date); // with $date is the date in yyyy/mm/dd
echo date( 'd-m-Y', $time ); // print date in dd/mm/yyyy format
?>

2011-12-22

Arnav Joy answers:

have you trie orderby='meta_value' in $args array

2011-12-22

John Cotton answers:

<blockquote>hen a user decides to filter results, I need the same query to be modified with the select values all the while still staying on the same page </blockquote>

Then you should change your form code to this:

<form name="myform" action="" method="POST">


That way, the page stays the same and you just need to pick up the $_POST values to adjust your output.

On the meta value, you're in difficulties trying to sort a date string as a number!

I think you've got two options:

1/ Store the dates as time values (which is a number), then the number sort would work
2/ Store the dates as date string (ie as you are now) remove the sorting from the query posts and do a uasort on $wp_query->posts with a custom function that does a proper date sort.

Personally, I would go for the first as it's more reliable in the long term and not very difficult to change your current code. You'd just need to format the time ( date('d/m/Y', $meta) ) before output.


Wordpressing comments:

Thanks John!

2011-12-22

Francisco Javier Carazo Gil answers:

Hi Wordpressing,

Dates are in Spanish format and you cannot order directly. You have some posibilities.

I recommend you to create a new meta value with date in this format: YYYY-MM-DD so the orderby works well.

Other posibility is SELECT all rows, saved them in an array and order it. You will have to split this string.

list($day, $month, $year) = split('/', $date);


Francisco Javier Carazo Gil comments:

The second problem can be solved by John Solution.

2011-12-22

Julio Potier answers:

Hello

Sory but WP Queries can not sort a meta field by date, 2 possibilities are "string" or "number"
Now i have to tell you that when you save a date, you have to do this :
YYYY-MM-DD (or / or whatever for separator)
or the time() !
Can you go back in time and change this ?
You'll save time and headache ^^


Julio Potier comments:

Or you can create you own query and cast the meta_field in date;
Kind of "...ORDER BY CAST(meta_value as DATE)"