Filter by date instead of datetime on ajax call

Emily VonSydow

#80148

Hi,
I have a case where I have to order posts by date and post name.

array[3] {
  [0] {
    ["post_date"]=> string(19) "2016-10-10 18:51:08",
    ["post_name"]=> string(26) "aaa"
  },
  [1] {
    ["post_date"]=> string(19) "2016-12-11 16:51:08",
    ["post_name"]=> string(26) "ccc"
  },
  [2] {
    ["post_date"]=> string(19) "2016-10-10 17:51:08",
    ["post_name"]=> string(26) "bbb"
  }
}

Expected result:

array[3] {
  [0] {
    ["post_date"]=> string(19) "2016-12-11 16:51:08",
    ["post_name"]=> string(26) "ccc"
  },
  [1] {
    ["post_date"]=> string(19) "2016-10-10 18:51:08",
    ["post_name"]=> string(26) "aaa"
  },
  [2] {
    ["post_date"]=> string(19) "2016-10-10 17:51:08",
    ["post_name"]=> string(26) "bbb"
  }
}

The issue of ordering by date is that post’s date is in a datetime format (Y-m-d hh:mm:ss) and is usually unique, so the ‘Secondary Sort Order’ option is useless. Is it possible to sort posts by date (Y-m-d) instead.

I found a solution for initial form setup and display, but cannot alter the ajax calls:

<?php
function query_orderby_day( $orderby ) {
	global $wpdb;
	return str_replace( "{$wpdb->posts}.post_date", "CAST({$wpdb->posts}.post_date AS DATE)", $orderby );
	}

	// Add the filter to change the ORDER clause
	add_filter( 'posts_orderby', 'query_orderby_day', 10, 2 );

	the_content();

	// Remove the filter
	remove_filter( 'posts_orderby', 'query_orderby_day' );
?>

Add New Note to this Reply

Trevor Moderator

#80200

Are you saying that, on the S&F Pro form settings page, on the Posts tab, you tried it with the Primary sort as date and secondary sort as name? I would have thought that should have worked. as you say, there should be no need for the secondary sort, as the date would be sufficient.

My initial though would be that you could code around this using one of our filters. See here in the documentation.

If you use the filter sf_input_object_pre you get access to the options as an array, so you can order this array however you want, so you can use this to sort the array with php. But you would need to code this.

Add New Note to this Reply

Emily VonSydow

#80581

Hi, Trevor!
Thank you for your prompt response. What I’m saying is that when primary sort is date, then secondary sort by name does nothing as date for posts is saved in datetime format and not just date.
So let’s say I have posts from two dates x = 2017-01-05 and y = 2017-01-06. When I sort in DESC order by date first everything is sorted fine, y date posts are displayed first and then x date posts are displayed second. The issue is that the secondary sort by name is applied, but it makes no effect as none of the posts have the exact time, these values are unique to each post (99.99% of time), whereas I want y date posts to be sorted by name as well as x date posts, to get this picture:
y date (2017-01-06) posts go first

• aaa (01:00:00 PM) – oldest post (when sorted by date in DESC should be last)
• bbb (03:00:00 PM)
• ccc (02:00:00 PM)
• ddd (04:00:00 PM) – latest post (when sorted by date in DESC should be first)

x date posts go second

• aaa (03:00:00 PM)
• bbb (04:00:00 PM) – latest post (when sorted by date in DESC should be first)
• ccc (01:00:00 PM) – oldest post (when sorted by date in DESC should be last)
• ddd (02:00:00 PM)

I’ll check out the filter you’ve provided and see if it does have what I need. I don’t have a problem of coding, just want to have the easiest and most robust solution.

Add New Note to this Reply

Trevor Moderator

#80642

I think I need to refer this to the developer, Ross, to see if he has an insight. In effect, the data stored for post date is too accurate, and the ideal solution would be to have the option to sort by date and not datetime.

Add New Note to this Reply

Ross Moderator

#80915

Hey John

So I’ve read through and seems to make sense.

Secondary sorting does in effect not work when using post date as the first sort option, because its a datetime comparison and unless two posts were produced in the same millisecond then the second sort would not get used.

Actually, what I would do is add “post date” as a custom field. So, whenever a post is saved/published (save_post), save just the date into post meta / custom field…

Then you can sort by this post meta 🙂

It seems cleaner to me (and more future proof) although will add a little overhead in db storage.

Hope that helps!

Add New Note to this Reply

Emily VonSydow

#81928

Thank you both, Trevor and Ross, for your input, that really helped.

Keep up the good work!

Add New Note to this Reply

[Author]

Posts