0

SQL REQUEST

SELECT post.ID, month.meta_value, year.meta_value FROM wp_posts post LEFT OUTER JOIN wp_postmeta month ON month.post_id= post.ID AND month.meta_key='mcv_end_month' LEFT OUTER JOIN wp_postmeta year ON year.post_id= post.ID AND year.meta_key='mcv_end_year' WHERE post.post_type='work_experience' ORDER BY (CASE WHEN year.meta_value IS NULL THEN 1 ELSE 0 END) DESC, (CASE WHEN month.meta_value IS NULL THEN 1 ELSE 0 END) DESC, year.meta_valueDESC, month.meta_valueDESC

RESULT

result of the request

function custom_columns_orderby( $query ) {
    $prefix = 'mcv_';
    $orderby = $query->get( 'orderby');
    $order = $query->get( 'order');
    if ( 'End date' == $orderby ) {

        $query->set('meta_query', array(
            'relation' => 'OR',
            array(
                'end_year' => array(
                    'key' => $prefix . 'end_year',
                ),
                'end_month' => array(
                    'key' => $prefix . 'end_month',
                ),
            ),
            array(
                'relation' => 'OR',
                'end_year' => array(
                    'key' => $prefix . 'end_year',
                    'compare' => 'NOT EXISTS',
                ),
                'end_month' => array(
                    'key' => $prefix . 'end_month',
                    'compare' => 'NOT EXISTS',

                ),
            ),                     
        ));
        $query->set('orderby',array(
                    'end_year' => $order,
                    'end_month' => $order,
                )
        );
    }
}
2
  • And i say to myself : RTFM PHIL !!! Commented May 31, 2018 at 8:55
  • In fact, it's not exactly the same request. The meta_query generates 4 LEFT JOIN. but it works Commented May 31, 2018 at 12:31

0

Browse other questions tagged or ask your own question.