Multiple order specifications in pre_get_posts do not work according to the spec

I think the error message is a bit of a red herring. The order by expression is normally almost never in group by. However the expression does appear to be malformed for what you are trying to do.

At issue I think is the default orderby arg is limited in functionality. I don’t believe it is properly structured to utilize key name elements of “meta_query” args. You basically can only orderby a single key name that is passed as a “meta_key” arg.

If you require more complex ordering, you need to alter the SQL through the “posts_orderby” filter where you can use any valid SQL syntax you like. You will need to examine the overall SQL to determine how to properly specify the desired meta keys to order by.

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID
					FROM wp_posts  LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id AND mt1.meta_key = 'プラン' )
					WHERE 1=1  AND ( 
  wp_postmeta.meta_key = 'プラン' 
  OR 
  mt1.post_id IS NULL
) AND ((wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' 
OR wp_posts.post_status = 'private')))
					GROUP BY wp_posts.ID
					ORDER BY CAST(wp_postmeta.meta_value AS CHAR) DESC, CAST(mt1.meta_value AS CHAR) DESC, wp_posts.post_date DESC
					LIMIT 0, 10
				

• This reply was modified 1 year, 9 months ago by Anon_.

Sorry, the above query, but I posted it once by mistake and have corrected it.

I just realized the exists and not exists keys are the same. I tend to gloss over non-Latin chars. I think I understand what you are trying to accomplish. You’re making it too complicated ?? Instead of a complex meta query, just use the single “meta_key” arg without the usual “meta_value” arg and order by simply “meta_value”. You’ll still get all posts like you would with the complex OR meta query. The posts without a value should naturally fall together, and should be at the end just as you want. In the off chance they all occur first you can just reverse the entire results array.

You can still provide a secondary orderby column so all the posts without are ordered by this secondary key.

Apologies for the inclusion of Japanese.
The word “plan” should be replaced by “plan”.

With the method you have presented, articles that do not have “plan” registered in meta_key will not be retrieved, will they?
Articles that existed before the custom field was created have no custom field registered.

All posts (but possibly limited by “posts_per_page”) will be retrieved regardless of what “meta_key” arg you use, as long as no “meta_value” is specified. The only purpose of the meta_key is for ordering of the results. It does not restrict the query results.

Nothing wrong with using Japanese, my bad for not paying enough attention. At least I got it eventually ??

			$query->set('meta_key', 'プラン');
			$query->set( 'orderby', array('meta_value_num' => 'desc', 'date' => 'desc') );

Change to the above code and the SQL that can be retrieved is as follows.
As expected, articles without custom field keys do not seem to be retrieved.

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID
					FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
					WHERE 1=1  AND ( 
  wp_term_relationships.term_taxonomy_id IN (673)
) AND ( 
  wp_postmeta.meta_key = 'プラン'
) AND ((wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' 
OR wp_posts.post_status = 'private')))
					GROUP BY wp_posts.ID
					ORDER BY wp_postmeta.meta_value+0 DESC, wp_posts.post_date DESC
					LIMIT 0, 10

Oh drat! I could have sworn that query returns all posts. Sorry for leading you down a dead end road. Let’s go back to your original query. Aside from the SQL error, it seems to work as intended AFAICT. I know you said it doesn’t work for you. I tested on my own site using “_thumbnail_id” as the meta key since I have many posts with no featured image. What ever problem you’re encountering, it’s not because of the incompatibility warning.

The incompatibility warning doesn’t appear to affect WP, it’s only within phpMyAdmin that there appears to be an issue. WP isn’t making use of grouped data, so even though it’s technically incorrect, it’s of no consequence. I used $wpdb->show_errors(); in my test code and no errors were shown. I think it’s safe to ignore.

If it still concerns you, you could remove ONLY_FULL_GROUP_BY from the sql_mode variable. In your SQL configuration file add a custom sql_mode value consisting of all the other modes except for ONLY_FULL_GROUP_BY, for example:
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE, ...etc."
Unless you have something using grouped values it should have no impact. Note that prior to v5.7.5 ONLY_FULL_GROUP_BY was not enabled by default anyway.

Or you could fix up the SQL query through the “posts_groupby” filter to include the offending order by args in the GROUP BY clause (wp_postmeta.meta_value, mt1.meta_value).

More information. This article suggest you can resolve the problem by using a derived table (example at the end of the article).

These measures will remove the incompatibility warning, but I suspect the grouped values will still be incorrect. It’s an issue inherent with the post meta schema and keeping it all in a nonaggregated column.

So, if it’s not the incompatibility issue causing your query to not work for you, what is it then? How is it not working? It works as expected for me. I get all posts, _thumbnail_id or not. Those with a thumbnail ID first, those without last. The posts with IDs are sorted in descending order, though as ASCII alphabetically, not numerically, which is expected with cast as char.

If you get results, but they are not ordered correctly, one possible explanation might be the values under the プラン key. Aren’t the values also Japanese? What collation are you using? If it’s not some variant of utf8mb4, that’s likely the problem. It must be mb4 of some sort to properly order multi-byte chars. ( I use utf8mb4_unicode_520_ci FWIW )

You can change collations in phpMyAdmin, but be aware that there are collations per column, per table, and for the DB overall. Mixing collations can cause other errors. If you change one, change them all. N.B: we are talking about collation here, not character set encoding. Collation can be freely altered, but definitely not character set encoding.