How do I “orderby” a column without losing posts that don’t have the column?

by

You can do it like so:

  1. Use the meta_query parameter to query both posts that have the metadata _my_column (or whatever is the meta key) and posts that do not have it (i.e. does not exist in the database).

  2. Use a custom name (i.e. array key) with the above meta query clauses and then use the name in the orderby parameter.

See Query improvements in WP 4.2: ‘orderby’ and ‘meta_query’ for further details.

So instead of using the meta_key parameter:

Note: I see you used meta_value_num, so I used the 'type' => 'NUMERIC' to make sure the meta value is treated as a (signed) integer.

  • If you don’t want/need to keep existing meta queries (in the $query object):

    $query->set( 'meta_query', array(
    'relation'             => 'OR',

    // Clause 1, named my_column_exists:
    // Query posts that do have the metadata _my_column.
    'my_column_exists'     => array(
        'key'     => '_my_column', // meta key
        'compare' => 'EXISTS',
        'type'    => 'NUMERIC',
    ),

    // Clause 2, named my_column_not_exists:
    // OR that do NOT have the metadata.
    'my_column_not_exists' => array(
        'key'     => '_my_column', // meta key
        'compare' => 'NOT EXISTS',
        'type'    => 'NUMERIC',
    ),
) );

$query->set( 'orderby', array(
    // Sort by the _my_column metadata first.
    'my_column_not_exists' => 'DESC',

    // Then if you want, by the post date, title, etc.
    'date'                 => 'ASC',
) );

  • Otherwise (to keep existing meta queries), you can do something like:

    $query->set( 'meta_query', array(
    // Note: Here the 'relation' defaults to AND.

    // Clause 1, unnamed.
    array(
        'relation'             => 'OR',

        // Sub-clause 1, named my_column_exists:
        // Query posts that do have the metadata _my_column.
        'my_column_exists'     => array(
            'key'     => '_my_column', // meta key
            'compare' => 'EXISTS',
            'type'    => 'NUMERIC',
        ),

        // Sub-clause 2, named my_column_not_exists:
        // OR that do NOT have the metadata.
        'my_column_not_exists' => array(
            'key'     => '_my_column', // meta key
            'compare' => 'NOT EXISTS',
            'type'    => 'NUMERIC',
        ),
    ),

    // Clause 2, unnamed.
    // Include the existing meta queries.
    (array) $query->get( 'meta_query' ),
) );

$query->set( 'orderby', array(
    // Sort by the _my_column metadata first.
    'my_column_not_exists' => 'DESC',

    // Then if you want, by the post date, title, etc.
    'date'                 => 'ASC',
) );

Leave a Comment