Nerd Corner

Tech talk about Wordpress and web development

WP_Query: Showing two set of results with different sort order in one view

by

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;

Adding multiselect custom field in Woocommerce product form

by

Here’s a quick tip if you are wondering of how to add multiselect fields in your Woocommerce product form.

Woocommerce has a great support for adding custom fields on a product page. There are some plugins available which help you to get things done, but it’s not really that complicated to write the code by yourself – and eliminate a need to install yet another plugin for your site. Here’s a great article of how to add fields: Mastering WooCommerce Products Custom Fields.

What if you want to add a multiselect field? It’s not that difficult either – create a function to populate multiselect field and save selection when product page is saved. The key difference with a single select field is that instead of saving single value to WordPress meta data, you save an array of values.

First, creating function to output and populate multiselect field, we can simply copy Woocommerce function for single select field and modify that to our needs. You can find it in woocommerce/includes/admin/wc-mate-box-function.php file. To save your time, here’s a modified function. Include it to your functions.php file:

function woocommerce_wp_select_multiple( $field ) {
    global $thepostid, $post;

    $thepostid              = empty( $thepostid ) ? $post->ID : $thepostid;
    $field['class']         = isset( $field['class'] ) ? $field['class'] : 'select short';
    $field['wrapper_class'] = isset( $field['wrapper_class'] ) ? $field['wrapper_class'] : '';
    $field['name']          = isset( $field['name'] ) ? $field['name'] : $field['id'];
    $field['value']         = isset( $field['value'] ) ? $field['value'] : ( get_post_meta( $thepostid, $field['id'], true ) ? get_post_meta( $thepostid, $field['id'], true ) : array() );

    echo '<p class="form-field ' . esc_attr( $field['id'] ) . '_field ' . esc_attr( $field['wrapper_class'] ) . '"><label for="' . esc_attr( $field['id'] ) . '">' . wp_kses_post( $field['label'] ) . '</label><select id="' . esc_attr( $field['id'] ) . '" name="' . esc_attr( $field['name'] ) . '" class="' . esc_attr( $field['class'] ) . '" multiple="multiple">';

    foreach ( $field['options'] as $key => $value ) {
        echo '<option value="' . esc_attr( $key ) . '" ' . ( in_array( $key, $field['value'] ) ? 'selected="selected"' : '' ) . '>' . esc_html( $value ) . '</option>';
    }

    echo '</select> ';

    if ( ! empty( $field['description'] ) ) {
        if ( isset( $field['desc_tip'] ) && false !== $field['desc_tip'] ) {
            echo '<img class="help_tip" data-tip="' . esc_attr( $field['description'] ) . '" src="' . esc_url( WC()->plugin_url() ) . '/assets/images/help.png" height="16" width="16" />';
        } else {
            echo '<span class="description">' . wp_kses_post( $field['description'] ) . '</span>';
        }
    }
    echo '</p>';
}

At this point you probably have a function in functions.php that handles adding your custom fields to the product form. Place the following call there:

woocommerce_wp_select_multiple(
    array(
        'id' => '_myfield',
        'name' => '_myfield[]',
        'label' => 'My Multiselect Field',
        'options' => ['key1' => 'First Item', 'key2' => 'Second Item', 'key3' => 'Third Item']
    )
);

Last but not least, place the following line to a function where you save your fields:

update_post_meta($post_id, '_myfield', $_POST['_myfield']);

See, you don’t even have to serialize the array data, good old WordPress does that for you inside update_post_meta function.

That should be enough. This was a very simplified example and you may need to add some validation logic and other stuff based on your needs.