ORDER BY date ASC placing records with no dates before records with dates?

1 Views
global $wpdb;

$sql = "SELECT * FROM $wpdb->postmeta";
$sql .= " INNER JOIN $wpdb->posts ON $wpdb->postmeta.post_id = $wpdb->posts.ID";
$sql .= " WHERE $wpdb->posts.post_type = 'task'";
$sql .= " AND $wpdb->posts.post_parent IN ( SELECT `ID` FROM $wpdb->posts WHERE `post_type` = 'task_list' AND `post_parent` IN ( SELECT `ID` FROM $wpdb->posts WHERE `post_status` = 'publish' AND `post_type` = 'project' ) )";
$sql .= " AND $wpdb->postmeta.post_id IN ( SELECT `post_id` FROM $wpdb->postmeta WHERE $wpdb->postmeta.meta_key = '_completed' AND $wpdb->postmeta.meta_value = '0' )";
$sql .= " AND $wpdb->postmeta.post_id IN ( SELECT `post_id` FROM $wpdb->postmeta WHERE $wpdb->postmeta.meta_key = '_assigned' AND $wpdb->postmeta.meta_value = '%s' )";
$sql .= " AND $wpdb->postmeta.meta_key = '_due'";
$sql .= " GROUP BY $wpdb->posts.ID";
$sql .= " ORDER BY CAST( $wpdb->postmeta.meta_value AS DATE ) ASC, $wpdb->posts.ID ASC";

$tasks = $wpdb->get_results( sprintf( $sql, $user_id ) );

return $tasks;

The result of the query is like this:

  1. Post A.
  2. Post B.
  3. Post X. Due: August 2
  4. Post Y. Due: August 3
  5. Post Z. Due: August 4

Why are the posts with no _due value always returned first? How can I make it so they are always returned last? If I do this:

Read More
 $sql .= " ORDER BY $wpdb->posts.ID ASC, CAST( $wpdb->postmeta.meta_value AS DATE ) ASC";

The posts with no _due value are randomly placed in between posts with _due value.

The ideal result should be:

  1. Post X. Due: August 2
  2. Post Y. Due: August 3
  3. Post Z. Due: August 4
  4. Post A.
  5. Post B.

Thoughts?

Post Views: 1

Related posts

Leave a Reply

3 comments 

  1. "ORDER BY CASE WHEN CAST( $wpdb->postmeta.meta_value AS DATE ) '' THEN 1 ELSE 0             
 END,CAST( $wpdb->postmeta.meta_value AS DATE )ASC, $wpdb->posts.ID ASC";

  2. You can add the function isnull to your order by clause:

    $sql .= " ORDER BY $wpdb->posts.ID ASC, ISNULL($wpdb->postmeta.meta_value),CAST( $wpdb->postmeta.meta_value AS DATE ) ASC";