3

I want to query by 1 meta key, but sort by another key that may or may not exist, and then by post date

So the end result would be something like:

Meta Key A / Date 1
Meta Key A / Date 2
Meta Key B / Date 1
No Meta Key / Date 1
No Meta Key / Date 2

Query args like the following are not returning any posts:

$customer_orders = get_posts( array(
    'numberposts' => $order_count,
    'meta_query' => array(
        array(
            'key'     => '_customer_user',
            'value'   => get_current_user_id(),
            'compare' => '=',
        ),
        array(
            'key'     => '_dealer_number',
            'compare' => 'EXISTS',
        ),
        array(
            'key'     => '_dealer_number',
            'compare' => 'NOT EXISTS',
        ),
        'relation' => 'AND'
    ),
    'orderby'    => 'meta_value date',
    'order'      => 'ASC'
) );

Is this possible with WP_Query args or do I need to filter posts_orderby? If so, what might I use?

  • You are querying posts where _dealer_number exists and at same time not exists: it's pretty impossible :) Maybe using OR as relation may work, but I'm not sure and moreover in that case _customer_user becomes optional too, that doesn't seems what you want. I'm afraid you need to filter posts_orderby, posts_where and posts_join to obtain desired result. – gmazzap Oct 7 '14 at 15:40
  • Yes, I just realized that _dealer_number existing and not existing at the same time is impossible. :) But I still want _customer_user to be required at all times. Is there a way to filter posts_orderby, etc so that the custom orderby clause only effects this get_posts() query? – helgatheviking Oct 7 '14 at 15:46
  • Yes. Set a custom flag to query get_posts( array( 'filter_me' => TRUE and inside the filter chek if flag exists, if not, don't filter. And when you apply the filter, immediately remove it so that it run once. – gmazzap Oct 7 '14 at 17:03
3

I don't think there's a way to do it without using filters. Using posts_clauses you could do:

function wpse163696_posts_clauses( $pieces, $query ) {
    if ( $query->get( 'orderby' ) != 'dealer_date' ) {
        return $pieces;
    }
    global $wpdb;

    $order = $query->get( 'order' );
    $pieces[ 'join' ] .= $wpdb->prepare(
        ' LEFT JOIN ' . $wpdb->postmeta . ' dealer_pm ON dealer_pm.post_id = ' . $wpdb->posts . '.ID AND dealer_pm.meta_key = %s'
        , '_dealer_number' );
    $pieces[ 'orderby' ] = 'ISNULL(MAX(dealer_pm.meta_value)) ' . $order . ', MAX(dealer_pm.meta_value) ' . $order  . ', ' . $wpdb->posts . '.post_date ' . $order;
    return $pieces;
}

Then your query becomes:

add_filter( 'posts_clauses', 'wpse163696_posts_clauses', 10, 2 );
$customer_orders = get_posts( array(
    'numberposts' => $order_count,
    'meta_query' => array(
        array(
            'key'     => '_customer_user',
            'value'   => get_current_user_id(),
            'compare' => '=',
        ),
    ),
    'orderby'    => 'dealer_date',
    'order'      => 'ASC',
    'suppress_filters' => false,
) );
remove_filter( 'posts_clauses', 'wpse163696_posts_clauses', 10 );
| improve this answer | |
  • This is almost there! Awesome! For some reason I can't get the posts with a dealer_number key to come first no matter how I set the orderby parameter. Could it be something in the fields parameter? I don't really understand what is going on there. – helgatheviking Oct 7 '14 at 16:56
  • It could be the fields parameter, it's assuming "_dealer_number" is numeric - is it? (All it's trying to do is negate it so that if it exists (is not NULL) it will sort before 0, ie when it doesn't exist (is NULL). There are a various other possible (probably nicer) methods.) – bonger Oct 7 '14 at 17:01
  • Actually just found this stackoverflow.com/a/4195311/664741 - much nicer solution to NULL sorting (not type dependent), will edit answer... – bonger Oct 7 '14 at 18:09
  • That works great. I had to modify it to $pieces[ 'orderby' ] = 'ISNULL(MAX(dealer_pm.meta_value)) ASC, MAX(dealer_pm.meta_value) ASC, ' . $wpdb->posts . '.post_date DESC'; so that it would be alphabetized and then go by most recent posts. – helgatheviking Oct 7 '14 at 20:28
  • 1
    I see, that makes sense, the zero-length strings are getting sorted differently before the date kicks in - perhaps then targeting the left join to product NULLs in that case - eg ... AND dealer_pm.meta_key = %s AND LENGTH(TRIM(dealer_pm.meta_value)) would work... – bonger Oct 8 '14 at 16:20
1

You have a couple of errors there, I believe.

1) There is no such parameter as 'numberposts'. Perhaps you mean 'posts_per_page'? That is what you should use to determine the number of posts to display.

2) When ordering by meta value, the correct syntax should be:

'orderby' => 'meta_value',
'meta_key' => 'key_name', 

Where key_name is the custom field whose meta_value you are using for the orderby.

So based on your question, your query may look like this:

$customer_orders = get_posts( 
                        array(
                            'posts_per_page' => $order_count,
                            'no_found_rows' => true,
                            'meta_query' => array(
                                                array(
                                                    'key'     => '_customer_user',
                                                    'value'   => get_current_user_id(),
                                                    'compare' => '=',
                                                ),
                                                array(
                                                    'key'     => '_dealer_number',
                                                    'compare' => 'EXISTS',
                                                ),
                                                array(
                                                    'key'     => '_dealer_number',
                                                    'compare' => 'NOT EXISTS',
                                                ),
                                                'relation' => 'AND'
                                            ),
                            'orderby'    => 'meta_value',
                            'meta_key'   => 'date',
                            'order'      => 'ASC'
                        ) 
                    );

(Note that I added 'no_found_rows'. If you know how many posts you are going to query and do not need pagination, then this addition will greatly speed up your query).

| improve this answer | |
  • 2
    According to the codex for get_posts()(which of course can be out of date) numberposts and posts_per_page can be used interchangeably. But that isn't the real problem. I have determined that the EXISTS and NOT EXISTS at the same time (at least with the AND relation returns nothing, at least with the AND relation. But I don't know how to have it AND for the _customer_user. You're answer is overlooking how I would like to sort by the _dealer_number key which is not guaranteed to be in existence and then by post date. – helgatheviking Oct 7 '14 at 15:29
  • Ah, yes, silly me, I missed that EXISTS and NOT EXISTS! Of course, you can't have something exist and not exist at the same time :) As for the orderby -- just change to 'meta_value date' (the meta_key still must exist) – Biranit Goren Oct 8 '14 at 5:03
  • Yeah I missed that too. #facepalm! Anyway, I tried your suggestion and it results in zero posts being found. See Bonger's answer... modifying the posts_join and posts_orderby clauses did it. – helgatheviking Oct 8 '14 at 5:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.