[Resolved] WooCommerce: Primary and Secondary sorting by ACF custom fields in functions php

[Author]

Posts

[ellenB]

Hi,

I am successfully sorting my WooCommerce products by one of my ACF custom fields using this code in my functions.php document:

add_action('pre_get_posts', 'sort_book_archive_loop');
function sort_book_archive_loop($query) {
	if ( ! is_admin() && $query->is_main_query() ) {
		if( is_shop() || is_product_category() ||  is_product_tag() || is_tax('authors') || is_tax('illustrator') || is_tax('translator') ){		
			$query->set('order', 'DESC');
			$query->set('meta_key', 'paperback_pub_date');
			$query->set('orderby', 'meta_value');
		}
	}
}

I would like to add secondary sorting to this function. I have modified my function as follows:

add_action('pre_get_posts', 'sort_book_archive_loop');
function sort_book_archive_loop($query) {
	if ( ! is_admin() && $query->is_main_query() ) {
		if( is_shop() || is_product_category() ||  is_product_tag() || is_tax('authors') || is_tax('illustrator') || is_tax('translator') ){		
			$query->set('order', 'DESC');
			$query->set('meta_key', array(
				'paperback_pub_date',
				'hardback_pub_date')
			);
			$query->set('orderby', 'meta_value');
		}
	}
}

The function is working to sort primarily by paperback_pub_date and secondarily by hardback_pub_date but it is generating an error that shows up:

Warning: preg_match() expects parameter 2 to be string, array given in /home/aviellc8/public_html/cincopuntosDEV/wp-content/plugins/wp-views/vendor/toolset/toolset-common/inc/autoloaded/wp_query_adjustments/m2m.php on line 588

Can you help me fix my function so that it continues to do the primary and secondary sorting that I need but doesn't generate an error?

My site is hidden link

[Nigel]

Although this is triggering a PHP warning in Views it's not really a Toolset issue, you are using the WP API to modify WC-based query to incorporate ACF fields.

meta_key accepts a string value, you cannot pass it an array.

For more complex queries with multiple meta components you need to use the meta_query argument instead of the meta_key and meta_value arguments:

https://developer.wordpress.org/reference/classes/wp_query/#custom-field-post-meta-parameters

And when you want to order by such multiple meta keys you need to combine the meta_query argument with an orderby argument that includes the meta arguments as an array.

There is an example of that on the same page. I can't link to it directly, but it is in the "‘orderby’ with multiple ‘meta_key’s" section at the end of https://developer.wordpress.org/reference/classes/wp_query/#order-orderby-parameters

[ellenB]

Thank you for your response, Nigel!

I have this working with the following code:

add_action('pre_get_posts', 'sort_book_archive_loop');
function sort_book_archive_loop($query) {
	if ( ! is_admin() && $query->is_main_query() ) {
		if( is_shop() || is_product_category() ||  is_product_tag() || is_tax('authors') || is_tax('illustrator') || is_tax('translator') ){
			$query->set('meta_query', array(
                'paperback_pub_date' => array(
                    'key' => 'paperback_pub_date',
                ),
                'hardback_pub_date' => array(
                    'key' => 'hardback_pub_date',
                )                    
            ));
            $query->set('orderby',array(
                'paperback_pub_date' => 'DESC', 
                'hardback_pub_date' => 'DESC'
            ));    
		}
	}
}

Reference links for the above:
https://wordpress.stackexchange.com/questions/225533/order-by-multiple-meta-keys-in-pre-get-posts
https://make.wordpress.org/core/2014/08/29/a-more-powerful-order-by-in-wordpress-4-0/

This results in a query that returns all of the paperback books in descending order and then all of the hardback books in descending order.

This may be as good as I can get it but my end goal is to translate the below SQL query into php. It compares the paperback_pub_date and hardback_pub_date for each record and orders the results based on the higher of the two. This allows for the ordering to be a mix of results (e.g. a paperback book and then a hardback that was published just after the paperback).

select *
case when isnull(paperback_pub_date, '1/1/1776') > isnull(hardback_pub_date, '1/1/1776')
then
  isnull(paperback_pub_date, '1/1/1776')
else
  isnull(hardback_pub_date, '1/1/1776')
end as maxdate,
*
from products
order by maxdate desc

(The 1/1/1776 value is to assign a value if the default value is null).

I know this is outside of scope Toolset support but thought I would throw it out there in case you were aware of what WP functions to use to create this in php.

[Nigel]

I'm not sure from your description whether a book that has both a paperback and a hardback publication date should appear twice in the results or whether it should appear once using the latest of the two dates, but in any case WP_Query (which is the WordPress class responsible for retrieving posts from the database) doesn't provide for any kind of dynamic meta query parameters where you would specify something like "the highest of these two fields".

For more complex cases like this you would actually need to manipulate the SQL query (which WP_Query generates) directly, or write your own SQL queries from scratch.

See this link, for example, about a filter you can use to modify the WHERE SQL clause in an archive query: https://developer.wordpress.org/reference/hooks/getarchives_where/

The $wpdb class can be used to interact directly with the database: https://developer.wordpress.org/reference/classes/wpdb/

I don't really have much more for you, I'm afraid.

[ellenB]

I appreciate your detailed response and links, Nigel. I’ll check them out - thank you!