Custom meta date order wont show two posts with same date.

You can’t use duplicate dates the way it is set up because, by assigning the date to a key, any new post with the same date will replace the the previous post in the array that had the same date. The sort order is messed up because the date stamps become strings in order to be keys, so the sort is in ascii order instead of numeric.

You should abandon this approach. It is possible to query the meta table for the needed dates, in order, along with associated post_IDs. Then you can just get_post() for each element in the array. Constructing unusual queries is not everyone’s forte, it’s not mine.

So as an alternate, you could construct an unordered array of post_IDs and meta date values, similar to what you have, but not as keys! A multi dimensional array where each parent element has both ID and date values, the date will stay as a number. Sort this array by the date stamp, then get_post() for each element’s post_ID. No need for nested loops.

Thanks for your help bcworkz, I have gone for a much more simple approach, but now it doesn’t take the year into consideration and wont get posts where a custom date isn’t set.

(https://development.hanselldesign.co.uk/ffa/?page_id=172)

<?php $args = array(  'category_name' =>'big-lottery', 'post_type' => 'post', 'orderby' => 'meta_value', 'meta_key' => 'fund_dead', 'order'=>'ASC' );

$loop = new WP_Query( $args );
while ( $loop->have_posts() ) : $loop->the_post(); ?>

<div class="divide"></div>

<div class="event-main">

<a href="<?php the_permalink() ?>"><h1><?php the_title(); ?></h1></a>

<h3>Deadline: <span id="event-list"><?php $fund_dead = get_post_meta($post->ID, 'fund_dead', true);
esc_html_e( date_i18n( 'l, F jS, Y' , strtotime( $fund_dead ) ) ); ?></span> </h3>

<h3>Eligibility: <span id="event-list"><?php $fund_elig = get_post_meta($post->ID, 'fund_elig', true);
echo apply_filters('',  $fund_elig); ?></span> </h3>

<a class="event-more" href="<?php the_permalink() ?>">Read More</a>

</div>

<div style="clear:both;"></div>

 <?php endwhile;?>

<div class="divide"></div>

I like your new approach much better! Too bad it’s not working quite right, but something that can be resolved, I believe.

Unfortunately, mySQL is not my forte, so I can’t help much. The inability to use the year must be SQL cannot deal with how the date is stored for some reason? It obviously can deal with WP dates, how are your’s different? I’m guessing of course.

Another guess is the arguments parser is botching converting your arguments into a query, you can hook the ‘posts_request’ filter in order to examine the actual query to ensure it got translated correctly.

As far as empty fields, I guess that is to be expected in hind sight. There is probably another way to query that will bring them up, but such a thing is beyond my knowledge.

You can experiment with queries directly in phpMyAdmin to see what is working and what is not is all I can suggest. I hope these random thoughts help somehow. Cheers.

Ok if I query like this it get all posts but My order is completely messed up… I am lost with the SQL… any thoughts?

<?php ; } elseif  ( is_page( 'Big Lottery' ) || '172' == $post->post_parent ) {	?>

<?php $args = array(
							'category_name' =>'big-lottery',
							'post_type' => 'post',
							'posts_per_page' => -1,
							'orderby' => 'meta_value',
							'order'=>'ASC',
							'meta_query'=> array(
							 array(
						  	'category_name' =>'big-lottery',
							'post_type' => 'post',
							'posts_per_page' => -1,
							'meta_key' => 'fund_dead' ,
							'orderby' => 'meta_value',
							'order'=>'ASC',
										)
											)
												);

$loop = new WP_Query( $args );

while ( $loop->have_posts() ) : $loop->the_post(); ?>

<div class="divide"></div>

<div class="event-main">

<a href="<?php the_permalink() ?>"><h1><?php the_title(); ?></h1></a>
<?php $eventDate = get_post_meta($post->ID, 'fund_dead', true);
$today     = esc_html_e( date( '' , strtotime( $fund_dead ) ) );
if($eventDate == $today) { ?>

     <h3>Deadline: <span id="event-list"><?php $date = 'Ongoing' ; echo($date); ?></span> </h3>

  <?php } else { ?>

      <h3>Deadline: <span id="event-list"><?php $fund_dead = get_post_meta($post->ID, 'fund_dead', true);
esc_html_e( date_i18n( 'l, F jS, Y' , strtotime( $fund_dead ) ) ); ?></span> </h3>

  <?php } ?>

<h3>Eligibility: <span id="event-list"><?php $fund_elig = get_post_meta($post->ID, 'fund_elig', true);
echo apply_filters('',  $fund_elig); ?></span> </h3>

<a class="event-more" href="<?php the_permalink() ?>">Read More</a>

</div>

<div style="clear:both;"></div>

<?php endwhile;?>

At first, I wasn’t sure how your latest query even worked for you, as the arguments supplied to meta_query should be invalid. Thinking about it some, I think the mere presence of the meta_query allowed the missing field posts to be retrieved.

The reason the sort is not working is because you cannot specify it in a meta_query, and the sort outside the meta_query is missing the meta_key argument, so it doesn’t know which value to use.

I think you should correct the meta_key location, which I can see would cause the missing value posts to be rejected again. But I’m hoping a cleaned up meta_query argument will override this. We will clean up the meta_query by asking it to return all posts with a value as well as all post without a value. Like so:

$args = array(
   'category_name' =>'big-lottery',
   'post_type' => 'post',
   'posts_per_page' => -1,
   'meta_key' => 'fund_dead',
   'orderby' => 'meta_value',
   'order'=>'ASC',
   'meta_query'=> array(
      array(
         'key' => 'fund_dead' ,
         'compare' => 'EXISTS',
         'type' => 'DATE'
      )
      array(
         'key' => 'fund_dead' ,
         'compare' => 'NOT EXISTS',
         'type' => 'DATE'
      )
   )
);

The EXISTS argument is new, you must have at least WP 3.5. The problem, I can now see, is in order to sort by meta value, you have to specify meta_key, which will also reject posts without the key. In raw SQL, these arguments are unrelated, but in WP_Query, they are tied together. So if my idea above does not work, another approach would be to simply query all posts of the category with no specification on meta keys or sort order.

To get the sort order without limiting the query, hook the filter ‘posts_orderby’ and return the proper orderby argument there. You’ll need to use the same conditional is_page or $post->parent so the orderby is not applied to all queries.

If you’re unsure, like me, of the proper orderby syntax, you can get WP_Query to show it to you by leaving the old arguments in and hooking the ‘posts_request’ filter. Your filter function will be passed the raw SQL query that WP_Query constructed.

I sense I am sort of the blind leading the blind here, but despite that, I think we are zeroing in on the proper solution.

Ok your query also only seems to show the posts with the date meta input, but again order correctly by day/month, but not year, so still having an issue pulling posts with empty fund_dead meta.

You can view the site HERE

Could you explain the ‘posts_orderby’ slightly better then the wp codex?

This is the SQL Query that I get returned with the ‘posts_request’ filter:

SELECT wp_ffaposts.* FROM wp_ffaposts INNER JOIN wp_ffaterm_relationships ON (wp_ffaposts.ID = wp_ffaterm_relationships.object_id) INNER JOIN wp_ffapostmeta ON (wp_ffaposts.ID = wp_ffapostmeta.post_id) INNER JOIN wp_ffapostmeta AS mt1 ON (wp_ffaposts.ID = mt1.post_id) WHERE 1=1 AND ( wp_ffaterm_relationships.term_taxonomy_id IN (9) ) AND wp_ffaposts.post_type = 'post' AND (wp_ffaposts.post_status = 'publish' OR wp_ffaposts.post_status = 'private') AND (wp_ffapostmeta.meta_key = 'fund_dead' AND mt1.meta_key = 'fund_dead' ) GROUP BY wp_ffaposts.ID ORDER BY wp_ffapostmeta.meta_value ASC

I think I get it now. The date meta is stored as a string, so the orderby meta_value sorts the posts as if the meta_value were an ascii string, sorted alphabetically as the chars are found in the ascii table. So Jan 14 2020 will sort before Jan 15 1984.

The proper solution is to store dates in a column formatted for dates, but then you cannot use any post_meta functions, you’d be basically joining in your own terms table.

One thing to try is to set the ORDER BY parameter to be STR_TO_DATE(wp_ffapostmeta.meta_value, %M %D, %Y) . Of course, if you put that into your arguments array, it’ll get butchered by the query parser. It needs to be returned from the ‘posts_orderby’ filter hook in order to be placed properly in the query. This will work for your site, but for distribution, you need to address the variable table prefixes.

Can I explain posts orderby? Probably, but I’m not sure where to start! Here goes… What you supply as an argument for ‘orderby’ as a WP_Query or related arguments must be only one of several possible strings because the query parser needs to recognize these strings in order to know what table.column name to place after the ORDER BY command in the actual query. If you supply 'orderby' => 'author' as argument, the parser will generate a portion of the query that looks like ORDER BY wp_ffaposts.post_author for your site. Other sites will have their correct table prefix specified. The strings recognized by the parser are essentially ‘friendly’ labels for the actual column names. The parser makes the translation, and in some cases like the meta_value, joins in the needed tables and adds the needed meta_key argument as well. And now perhaps you can see why not providing a meta_key argument causes the query to not work right, the whole wp_ffapostmeta.meta_key = 'fund_dead' would be missing.

This friendly label thing is why we cannot add a STR_TO_DATE() function to our orderby argument, the parser will not recognize it. It will only see the meta_value portion, and make an ORDER BY command like you see in your ‘posts_request’ dump. The function we tried to pass would be gone.

Now, finally, the not returning posts with no meta_value. I think if you form a query to simply return all posts with the category, ordered by anything, we can drop in the STR_TO_DATE() argument via filter and things will work… almost. Without a meta_key argument, posts without the meta value will be returned, and those posts will be ordered by the meta_value column, regardless of which key the value is associated to. Not sure what happens to meta values that are not dates, I’m hoping they will not exist.

The other problem is without a meta_key and meta_value argument, the postmeta and term_relationships tables will not even be joined into the query, so the query will fail again. So we need to add the whole join section, from the first INNER JOIN to just before the WHERE, in using the ‘posts_join_request’ filter. Alternately, we could stick with the whole thing in ‘posts_request’ and simply inject the correct query right there, all at once. The initial WP_Query arguments would essentially be ignored. The only reason we are using WP_Query instead of $wpdb is so we can use ‘the loop’.

As I warned earlier, SQL is not my thing, but this is what I think the query needs to be:
SELECT wp_ffaposts.* FROM wp_ffaposts INNER JOIN wp_ffaterm_relationships ON (wp_ffaposts.ID = wp_ffaterm_relationships.object_id) INNER JOIN wp_ffapostmeta ON (wp_ffaposts.ID = wp_ffapostmeta.post_id) INNER JOIN wp_ffapostmeta AS mt1 ON (wp_ffaposts.ID = mt1.post_id) WHERE 1=1 AND ( wp_ffaterm_relationships.term_taxonomy_id IN (9) ) AND wp_ffaposts.post_type = 'post' AND (wp_ffaposts.post_status = 'publish' OR wp_ffaposts.post_status = 'private') GROUP BY wp_ffaposts.ID ORDER BY STR_TO_DATE(wp_ffapostmeta.meta_value, %M %D, %Y) ASC

I just removed the restriction that a meta_key needs to equal anything and fixed the ORDER BY to treat data as a date. I’m not confident this will work because I don’t fully understand how to use term_relationships, only that it is required to tie post IDs to the metadata. But it’s probably closer to what you need than anything else we’ve come up with. Ideally we’d want to order by meta_values that only have a meta_key=’fund_dead’, without restricting the posts returned. Unfortunately, I don’t know how to do that. I’m hoping the query will work for you without that nuance.

I think I’m now even more lost :S

So is this what I need:

<?php add_filter('posts_orderby', 'edit_posts_orderby' );

function edit_posts_orderby($orderby) {
	$orderby = " STR_TO_DATE(wp_ffapostmeta.meta_value, %M %D, %Y) ASC ";
 	return $orderby;
} ?>

<?php $args = array(
   'category_name' =>'big-lottery',
   'post_type' => 'post',
   'posts_per_page' => -1,
   'meta_key' => 'fund_dead',
   'orderby' => 'meta_value',
   'order'=>'ASC',
   'meta_query'=> array(
      array(
         'key' => 'fund_dead' ,
         'compare' => 'EXISTS',
         'type' => 'DATE',

      array(
         'key' => 'fund_dead' ,
         'compare' => 'NOT EXISTS',
         'type' => 'DATE'
         )
      )
   )
);

Because this doesn’t return any posts at all…

How would I go about using you SQL Query to pull in the posts?

The orderby portion would be OK, but we need to cut back the initial query arguments so you get some posts returned to put in order. We don’t want to restrict by the meta key or you will never get posts with no value. I’m hoping you’ll get only the posts you want by just restricting by category.

But if we take out the meta key restriction, the orderby reference will not work because the referenced tables will not be joined. This is why I’m inclined to just inject the whole query, so you end up with something more like this:

add_filter('posts_request', 'edit_posts_request' );

function edit_posts_request($request) {
    if( 'big-lottery' == $request[1]->get('category_name') ) {
	$request[0] = "SELECT wp_ffaposts.* FROM wp_ffaposts INNER JOIN wp_ffaterm_relationships ON (wp_ffaposts.ID = wp_ffaterm_relationships.object_id) INNER JOIN wp_ffapostmeta ON (wp_ffaposts.ID = wp_ffapostmeta.post_id) INNER JOIN wp_ffapostmeta AS mt1 ON (wp_ffaposts.ID = mt1.post_id) WHERE 1=1 AND ( wp_ffaterm_relationships.term_taxonomy_id IN (9) ) AND wp_ffaposts.post_type = 'post' AND (wp_ffaposts.post_status = 'publish' OR wp_ffaposts.post_status = 'private') GROUP BY wp_ffaposts.ID ORDER BY STR_TO_DATE(wp_ffapostmeta.meta_value, %M %D, %Y) ASC";
    }
 	return $request;
}

$args = array(
   'category_name' =>'big-lottery',
   'post_type' => 'post',
   'posts_per_page' => -1,
);
$loop = new WP_Query( $args );

Completely untested, I’m on unfamiliar ground here myself, both in how to handle the array passed in ‘posts_request’ and how to form a proper SQL query, so significant debugging may be still required. I’m mainly trying to illustrate the need to not be very specific about what’s requested, but specific about how what is returned is ordered.

I hope this makes more sense and gets you pointed in a good direction.

Thanks for your continued help ??

I have had to change what you supplied to:

<?php add_filter('posts_request', 'edit_posts_request' );

function edit_posts_request($request) {
    if(is_page('blog')) { $request['category_name'] = 'Blog';
	$request[0] = "SELECT wp_ffaposts.* FROM wp_ffaposts INNER JOIN wp_ffaterm_relationships ON (wp_ffaposts.ID = wp_ffaterm_relationships.object_id) INNER JOIN wp_ffapostmeta ON (wp_ffaposts.ID = wp_ffapostmeta.post_id) INNER JOIN wp_ffapostmeta AS mt1 ON (wp_ffaposts.ID = mt1.post_id) WHERE 1=1 AND ( wp_ffaterm_relationships.term_taxonomy_id IN (9) ) AND wp_ffaposts.post_type = 'post' AND (wp_ffaposts.post_status = 'publish' OR wp_ffaposts.post_status = 'private') GROUP BY wp_ffaposts.ID ORDER BY STR_TO_DATE(wp_ffapostmeta.meta_value, %M %D, %Y) ASC";
    }
 	return $request;
}

$loop = new WP_Query( $request );

while ( $loop->have_posts() ) : $loop->the_post(); ?>

As if( 'big-lottery' == $request[1]->get('category_name') ) was returning and error.

It doesn’t seem to order the posts by date still. Very frustrating…

Frustrating is right!
I hope you are at least getting all the correct results, just not properly ordered? There could simply be a syntax error in the order by segment, I wouldn’t be able to see it. Or maybe there’s other values in the meta_value column confusing the results. Ideally we should be ordering by meta_values only where the meta_key == ‘fund_dead’ but without restricting the results, but I don’t even know how to begin writing that sort of query, or if it’s even possible.

One last little thing, I doubt it will change things, but it certainly is not helping and might be confusing things. Take out the $request['category_name'] = 'Blog'; line.

You probably will not be surprised at what’s next. I’m out of ideas ?? I’ve been at the limits of my knowledge (and beyond) for a while. I appreciate your willingness to play along despite that. I shouldn’t waste any more of your time on fruitless attempts.

Where to go from here? You could try starting a new topic here that is more targeted based on what we have learned so far. I doubt anyone else would jump in on this thread any more. To be honest, you would probably get more knowledgeable support if you posted a question on stackexchange.

I’m sorry I’m unable to help you. Best of luck to you.

After some Googleing and further trawling through the interweb, I feel I am nearing the solution.

Thanks for all your help bcworkz It was greatly appreciated!

Here is where I am at:

<?php
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$ordered_posts = new WP_Query(array(
    'paged' => $paged,
    'category_name' =>'big-lottery',
    'post_type' => 'post',
    'posts_per_page' => 24,
    'meta_key' => 'fund_dead',
    'orderby' => 'meta_value_num',
    'order' => 'ASC',
));

$unordered_posts = new WP_Query(array(
    'category_name' =>'big-lottery',
    'post_type' => 'post',
    'paged' => $paged,
    'posts_per_page' => 24 - $ordered_posts->post_count,
));

    while ($ordered_posts->have_posts()) : $ordered_posts->the_post();
    // loop 1

 ?>

<div class="divide"></div>

<div class="event-main">

<a href="<?php the_permalink() ?>"><h1><?php the_title(); ?></h1></a>
<?php $eventDate = get_post_meta($post->ID, 'fund_dead', true);
$today     = esc_html_e( date( '' , strtotime( $fund_dead ) ) );
if($eventDate == $today) { ?>

     <h3>Deadline: <span id="event-list"><?php $date = 'Ongoing' ; echo($date); ?></span> </h3>

  <?php } else { ?>

      <h3>Deadline: <span id="event-list"><?php $fund_dead = get_post_meta($post->ID, 'fund_dead', true);
esc_html_e( date_i18n( 'l, F jS, Y' , strtotime( $fund_dead ) ) ); ?></span> </h3>

  <?php } ?>

<h3>Eligibility: <span id="event-list"><?php $fund_elig = get_post_meta($post->ID, 'fund_elig', true);
echo apply_filters('',  $fund_elig); ?></span> </h3>

<a class="event-more" href="<?php the_permalink() ?>">Read More</a>

</div>

<div style="clear:both;"></div>

<?php endwhile;?>

<?php

   $my_query = null;
$my_query = new WP_Query($unordered_posts);
if( $my_query->have_posts() ) {
  while ($my_query->have_posts()) : $my_query->the_post();
    $custom = get_post_meta($my_query->post->ID, 'fund_dead', true);
    if ( ! $custom ){
    ?>
      <div class="divide"></div>

<div class="event-main">

<a href="<?php the_permalink() ?>"><h1><?php the_title(); ?></h1></a>
<?php $eventDate = get_post_meta($post->ID, 'fund_dead', true);
$today     = esc_html_e( date( '' , strtotime( $fund_dead ) ) );
if($eventDate == $today) { ?>

     <h3>Deadline: <span id="event-list"><?php $date = 'Ongoing' ; echo($date); ?></span> </h3>

  <?php } else { ?>

      <h3>Deadline: <span id="event-list"><?php $fund_dead = get_post_meta($post->ID, 'fund_dead', true);
esc_html_e( date_i18n( 'l, F jS, Y' , strtotime( $fund_dead ) ) ); ?></span> </h3>

  <?php } ?>

<h3>Eligibility: <span id="event-list"><?php $fund_elig = get_post_meta($post->ID, 'fund_elig', true);
echo apply_filters('',  $fund_elig); ?></span> </h3>

<a class="event-more" href="<?php the_permalink() ?>">Read More</a>

</div>

<div style="clear:both;"></div>
      <?php
    }
  endwhile;
}
wp_reset_query();  // Restore global post data stomped by the_post().
?>

Using the two query approach gets all the desired posts and lists them in the correct order. Phew! The only issue now is the that it is still ordering by year then month. This is good enough for the meantime, but I will still be searching for a fix to the year order issue.

It’s great to hear you are so close to a complete solution! The date sort thing is unfortunate. In reviewing this thread to refresh my memory, another idea came to me. My ideas have not worked well for you so far, so I would take no offense if you completely ignore this one. Just in case it may help, here it is.

Since trying to sort by query was such a disaster, why not sort the array returned by the query? The entire array of posts that are returned are available as $my_query->posts for example, or $ordered_posts->posts. You could use usort() or one of it’s relatives which sorts arrays by the results of a user defined function. Your sort function could grab the related post meta dates for each comparison and return the appropriate value accordingly.

This is not nearly as efficient as letting mySQL do the sorting, but since that was a total bust, it’s starting to look pretty good. So that’s my new idea. Do with it what you like. Cheers!