Expanded meta key comparison operators in 5.3

WordPress 5.1 introduced the compare_key parameter for WP_Meta_Query, allowing developers to perform LIKE queries against postmeta keys. (See #42409 and the related dev note.) WordPress 5.3 expands the options available to compare_key, so that developers have access to metaMeta Meta is a term that refers to the inside workings of a group. For us, this is the team that works on internal WordPress sites like WordCamp Central and Make WordPress.-key comparison operators similar to those available for meta values. See #43446 and [46188].

After this change, compare_key accepts the following operators: =, LIKE, !=, IN,NOT IN,NOT LIKE,RLIKE,REGEXP,NOT REGEXP,EXISTS, andNOT EXISTS. Usage notes:

  • For parity with value operators, we’ve added support for EXISTS and NOT EXISTS. In the case of compare_key, these map to = and !=, respectively.
  • MySQLMySQL MySQL is a relational database management system. A database is a structured collection of data where content, configuration and other options are stored. https://www.mysql.com/. regular expression comparison operators (RLIKE, REGEXP, NOT REGEXP) are case-insensitive by default. To perform case-sensitive regular expression matches, it’s necessary to cast to BINARY. To support this, WP_Meta_Query now accepts a type_key parameter; pass 'BINARY' to force case-sensitive comparisons. (This directly parallels the use of type when using regular expressions to match meta value.)
  • As is the case with other WP_Meta_Query-related parameters, the parameters discussed here are available to WP_Query using the meta_ prefix: meta_compare_key and meta_type_key.

#5-3, #dev-notes, #query

LIKE support for meta keys in 5.1

WordPress 5.1 introduces limited LIKEsupport for metaMeta Meta is a term that refers to the inside workings of a group. For us, this is the team that works on internal WordPress sites like WordCamp Central and Make WordPress. keys when using WP_Meta_Query. The new compare_key parameter (meta_compare_key as a top-level WP_Query query arg) accepts a value of LIKE in addition to the default =. See #42409 and [42768]. Here’s an example of how the new parameter is used:

$q = new WP_Query(
  array(
    'post_type'  => 'my_custom_post_type',
    'meta_query' => array(
      array(
        'compare_key' => 'LIKE',
        'key'         => 'foo',
      ),
    ),
  )
);

This will generate a query of the form ... AND meta_key LIKE '%foo%' ....

This enhancementenhancement Enhancements are simple improvements to WordPress, such as the addition of a hook, a new feature, or an improvement to an existing feature. follows from changes in WordPress 4.8.3 to the way that MySQLMySQL MySQL is a relational database management system. A database is a structured collection of data where content, configuration and other options are stored. https://www.mysql.com/. wildcard characters are handled when building queries. The changes in 4.8.3 broke compatibility for some plugins that passed wildcards into the key parameter of meta queries. The new compare_keyfeature in WP 5.1 is not a full replacement for the previous behavior, but it should allow for most use cases. See #43445 for discussion.

#5-1, #dev-notes, #query

Query component bug scrub – May 24

A 90-minute bugbug A bug is an error or unexpected result. Performance improvements, code optimization, and are considered enhancements, not defects. After feature freeze, only bugs are dealt with, with regressions (adverse changes from the previous version) being the highest priority. scrub for the Query component will be held at Tuesday 24 May 2016, 18:00 UTC in the #core channel on SlackSlack Slack is a Collaborative Group Chat Platform https://slack.com/. The WordPress community has its own Slack Channel at https://make.wordpress.org/chat/.. We’ll spend some time looking through the Awaiting Review milestone, and we’ll have a glance at any specific tickets that attendees might interested in talking about.

#bug-scrub, #query

Update on Query improvements in 4.1

A few weeks ago, I posted about planned Query improvements for WP 4.1. Most of the proposed improvements (and more!) are currently in trunktrunk A directory in Subversion containing the latest development code in preparation for the next major release cycle. If you are running "trunk", then you are on the latest revision. and are ready for testing by developers and others who are familiar with these query classes. A summary of changes can be found below.

WP_Meta_Query

WP_Meta_Query has been pretty much rewritten, resulting in extensive improvements.

  • Improved unit testunit test Code written to test a small piece of code or functionality within a larger application. Everything from themes to WordPress core have a series of unit tests. Also see regression. coverage #29560
  • Support for nested queries #29642. Let’s say you have posts with ‘city’ and ‘state’ metadata, and you want to fetch all items that match either city=Miami&state=Ohio or city=Augusta&state=Maine. The syntax looks like this:
    $query = new WP_Query( array(
    	'meta_query' => array(
    		'relation' => 'OR',
    		array(
    			'relation' => 'AND',
    			array(
    				'key' => 'city',
    				'value' => 'Miami',
    			),
    			array(
    				'key' => 'state',
    				'value' => 'Ohio',
    			),
    
    		),
    		array(
    			'relation' => 'AND',
    			array(
    				'key' => 'city',
    				'value' => 'Augusta',
    			),
    			array(
    				'key' => 'state',
    				'value' => 'Maine',
    			),
    
    		),
    	),
    ) );
    		
  • Avoid redundant JOINs when using relation=OR #24093. In cases where you’re doing a particularly complex meta_query, this change could result in significant performance gains. This particular fix enabled a complete rewrite (and radical simplification) of the way WP_Meta_Query generates its SQL queries.
  • Fixed ‘orderby=meta_value’ in WP_Query when passing a ‘meta_query’ with relation=OR #25538
  • Fixed a bugbug A bug is an error or unexpected result. Performance improvements, code optimization, and are considered enhancements, not defects. After feature freeze, only bugs are dealt with, with regressions (adverse changes from the previous version) being the highest priority. that caused ‘NOT EXISTS’ queries to miss posts that have no metadata at all #29062

WP_Tax_Query

  • Improved unit test coverage #29718
  • Support for nested queries #29738. Syntax is the same as WP_Meta_Query above.
  • Avoid redundant JOINs when using relation=OR #18105
  • Added support for ‘EXISTS’ and ‘NOT EXISTS’ #29181. This means, for example, that you can fetch all posts that have no post_tags.

WP_Date_Query

  • Improved unit test coverage #29781
  • Support for nested queries #29822. Syntax is the same as WP_Meta_Query above.
  • Fix a bug that caused queries to fail when a date_query was used along with a tax_query or meta_query, due to a missed table join #25775. A side effect of this change is that the wily developer can also use WP_Date_Query to generate WHERE SQL for cross-table queries; see #29823
  • Throw _doing_it_wrong() errors when passing invalidinvalid A resolution on the bug tracker (and generally common in software development, sometimes also notabug) that indicates the ticket is not a bug, is a support request, or is generally invalid. dates #25834
  • Add support for querying by the ‘user_registered’ column in WP_User_Query #27283
  • Improve ‘inclusive’ logic when using string values for ‘before’ and ‘after’ #29908

WP_Comment_Query

  • Introduce 'include_unapproved' argument, and use it, rather than a direct SQL query, to fetch comments in comments_template() #19623
  • 'comment__in', 'comment__not_in', 'post__in', 'post__not_in' params #25386, #29189
  • 'author__in', 'author__not_in', 'post_author__in', 'post_author__not_in' params #29885

#4-1, #query

A more powerful ORDER BY in WordPress 4.0

orderby is the argument passed to WP_Query to tell it what column to sort on when it is creating the ORDER BY clause for its generated SQL. The default value for orderby is post_date.

The default sort order for a column in MySQLMySQL MySQL is a relational database management system. A database is a structured collection of data where content, configuration and other options are stored. https://www.mysql.com/. is ASC (ascending), with smallest values first. For the reverse, DESC is used. You can sort on multiple columns, and each column can have its own sort order.

The default value for the order argument inside WP_Query is DESC. ~23% of the internet automatically queries posts in reverse chronological order because of this. order can only be one of 2 values: DESC or ASC.

orderby accepts a string, representing a column on which to sort:

$q = new WP_Query( array( 'orderby' => 'post_title' ) );

// or an alias
$q = new WP_Query( array( 'orderby' => 'title' ) );

Both will produce an ORDER BY clause like:

ORDER BY post_title DESC

orderby will also parse a space-delimited set of columns:

$q = new WP_Query( array( 'orderby' => 'title author' ) );

Prior to 4.0, there was a problem: the value for order would only be applied to the last value that you passed in that space-delimited list, producing an ORDER BY clause like:

ORDER BY post_title, post_author DESC

Remember that the default sort order for a column in MySQL is ASC, so queries like that can get weird real fast and produce unexpected/unpredictable results. If no value is passed for order for a column in the generated SQL, the column will be sorted in ASC order. This was not so clear to all developers. #26042 was a joy to debug.

In 4.0, when you pass a space-delimited set of values, your sole value for order will be applied to all of your values that are parsed for orderby. This was fixed in [28541].

So that’s pretty good, but it doesn’t allow you granular control over the sort order for each column. The syntax doesn’t allow itself much room for extending.

Enter [29027].

In 4.0, you can now pass an array to WP_Query as the value for orderby. The syntax looks like:

$q = new WP_Query( array( 'orderby' => array( 'title' => 'DESC', 'menu_order' => 'ASC' ) ) );

This allows you to control the generation of the ORDER BY clause with more specificity:

ORDER BY post_title DESC, menu_order ASC

Pre-4.0, you would have had to use some gnarly filters on the SQL statement or a specific clause. No bueno.

To see the internals, check out the new protected methods in WP_Query: ->parse_order() and ->parse_orderby.

Happy WP_Querying!

#4-0, #dev-notes, #query

like_escape() is Deprecated in WordPress 4.0

@miqrogroove has written a blog post on his personal blogblog (versus network, site) explaining why like_escape() has been deprecated in WordPress 4.0. It has been reposted below.

PluginPlugin A plugin is a piece of software containing a group of functions that can be added to a WordPress website. They can extend functionality or add new features to your WordPress websites. WordPress plugins are written in the PHP programming language and integrate seamlessly with WordPress. These can be free in the WordPress.org Plugin Directory https://wordpress.org/plugins/ or can be cost-based plugin from a third-party authors and website developers who work with WordPress database queries should notice an important change coming in WordPress 4.0.

The function like_escape() is no longer used in WordPress coreCore Core is the set of software required to run WordPress. The Core Development Team builds WordPress. code. It is still available as a deprecated function, so it still works in any existing plugins that rely on it. However, a new and different function is available that should be used in all new code.

Deprecated means that anyone using code that calls like_escape() with WP_DEBUG enabled will see an error message. If WP_DEBUG_LOG is also enabled, the error message will appear in the /wp-content/debug.log file.

Let’s look at an example of core code where I removed like_escape() and implemented the new function $wpdb->esc_like().

3.9 Old Style

$search_orderby_s = like_escape( esc_sql( $q['s'] ) );
$search_orderby .= "WHEN $wpdb->posts.post_title LIKE '%{$search_orderby_s}%' THEN 1 ";

What did this do? It was an old snippet from /wp-includes/query.php that set up a search for post titles. The input $q['s'] was escaped using two functions before it was added to the post_title LIKE expression. Now let’s see how I replaced that snippet in the next version.

4.0 New Style

$like = '%' . $wpdb->esc_like( $q['s'] ) . '%';
$search_orderby .= $wpdb->prepare( "WHEN $wpdb->posts.post_title LIKE %s THEN 1 ", $like );

There are two important differences to notice.

  • I changed the like_escape() call to $wpdb->esc_like().
  • I changed the esc_sql() call to $wpdb->prepare().

The second change is important because esc_sql() is not secure if it is called before, or inside, the call to the new function $wpdb->esc_like(). By relying on the preferred style of the function prepare(), I can easily see that each instance of $wpdb->esc_like() will run first instead of last.

4.0 Alternative Style

Here is something that still works, but I avoided using it. Notice the old query is unchanged. It is critically important to call the two escaping functions in the correct order when using $wpdb->esc_like().

$search_orderby_s = esc_sql( $wpdb->esc_like( $q['s'] ) ); // This is the correct order.
$search_orderby .= "WHEN $wpdb->posts.post_title LIKE '%{$search_orderby_s}%' THEN 1 ";

How Should I Get My Code Ready for 4.0?

The nice thing about deprecated functions is that you can still use them and they don’t change. Your existing code should work fine.

When you write new code, remember that using $wpdb->esc_like() is not compatible with WordPress 3.9. This should be avoided if you need compatibility with old versions. When you are ready to adopt 4.0 as your minimum version, consider using the new function.

If you have a specific need for the new function in old versions of WordPress, I suggest copying the new function into your plugin under a different name. This would be the simplest solution, but rarely necessary.

Why Did like_escape() Change to $wpdb->esc_like()?

There were several problems with the old function that could not be fixed.

  • Documentation said the function’s output was safe to use in SQL queries. That was not correct.
  • The function’s output was not fully compatible with LIKE expressions in MySQLMySQL MySQL is a relational database management system. A database is a structured collection of data where content, configuration and other options are stored. https://www.mysql.com/..
  • The function had been used many different ways in core code, some of which were incompatible with the desired output.
  • Changing the old function instead of creating a new one would have caused many security problems in plugins.
  • The function was related to $wpdb because of its MySQL syntax, which does not work on other databases.

Is There a Security Problem with like_escape()?

The old function like_escape() was not intended to be used in any security sensitive context. There are no security problems when it is used correctly.

With that said, I am concerned that plugin authors frequently confused the old function like_escape() with esc_sql(), which was used for security. The documentation for like_escape() was misleading and very confusing about this point.

Just remember, like_escape() does not provide any security for your database!

So What Does $wpdb->esc_like() Do Anyway?

Whenever user input or other raw data are copied into a WordPress query, the data must be escaped using $wpdb->prepare() or esc_sql(). This prevents certain characters, such as quotes, from getting confused with SQL commands.

In a LIKE expression, there are additional special characters that are used as wildcards to search for partial matches in the database. Those wildcard characters have to be escaped from the user input so that they are not confused with the wildcards added by the programmer.

Before adding user input to this type of search query, $wpdb->esc_like() should be called for compatibility, and then $wpdb->prepare() must be called for security, in that order.

How to Use $wpdb in Functions

It is very rare to use $wpdb->esc_like() without also running a query. But just in case you want to …

function my_search( $input ) {
    global $wpdb;
    $escaped = $wpdb->esc_like( $input );
    ...
}

… remember to reference $wpdb as a global variable.

#4-0, #database, #dev-notes, #query, #security

Looking at http://trac.wordpress.org/tic …

Looking at #2959

#query