Month: May 2017

In my client project I have defined custom field “_featnumber” (aka “meta key”) for “product” post type. The key indicates if a post is “featured” or not.

The _featnumber key can have any integer value starting from 1, which is used to set the position in the view. However, typically the key is set empty. In those cases I have a piece of code that remove the key entirely from the database.

This type of SQL query is pretty easy if you simply want to show those posts that contain the key:

        $args = array(
            'post_type' => 'product',
            'posts_per_page' => 16,
            'meta_key' => '_featnumber',
            'orderby' => 'meta_value',
            'order' => 'ASC',
        );
        
        $loop = new WP_Query($args);

This will generate “INNER JOIN” SQL query (joining a post and the key), which will ensure that only those posts are returned which has _featnumber key set.

However, the requirement is to show 16 posts all times. If there are less (or none!) featured items, the view should be filled with other posts – ordered descending by published date. So, we should first show featured posts in ascending order and then other posts in descending order.

Such a SQL query is possible to create but it’s not straight forward, since WordPress does not natively support handling two set of queries with different ORDER BY clause. I did try that, and it worked, but it required using a filter and manually entering ORDER BY statement. I also had a gut feeling that the performance of the query was not best possible.

I ended up thing of having two simple queries and merging them afterwards. It took only some minutes to figure out how to do that and the end result was perfect!

Find the working code below. First we grab max 16 products with _featnumber. In the second round we query all products that don’t have _featnumber and in ‘posts_per_page’ ensure that the total number of results will be 16 (‘posts_per_page’ => 16 – $loop1->post_count).

Finally we merge the arrrays and set a correct “post_count” for the final set. Note that for merging we need to create an empty WP_Query object.

That’s it! Now you can use $loop array in a normal way, have a “while ($loop->have_posts()” loop and use all WordPress functions to grab the content. Enjoy 😉

        $args1 = array(
            'post_type' => 'product',
            'posts_per_page' => 16,
            'meta_key' => '_featnumber',
            'orderby' => 'meta_value',
            'order' => 'ASC'
        );
        
        $loop1 = new WP_Query($args1);

        $args2 = array(
            'post_type' => 'product',
            'posts_per_page' => 16 - $loop1->post_count,
            'meta_query' => array(
                array(
                 'key' => '_featnumber',
                 'compare' => 'NOT EXISTS'
                ),
            )
        );
        
        $loop2 = new WP_Query($args2);
        
        $loop = new WP_Query();
        $loop->posts = array_merge($loop1->posts, $loop2->posts);
        $loop->post_count = $loop1->post_count + $loop2->post_count;