Add LIKE to meta_compare

[joehoyle]

Currently, you can not query for posts with a meta_key LIKE "%star". To easily allow things such as search posts by meta key I have created a patch which adds an SQL LIKE to meta_compare.

to use, query for:

meta_key=url&meta_value=http://www%&meta_compare=LIKE

Notice the % is included in the meta_value, so you can also do:

meta_value=%www%

To get anything with a www in the meta_key. Patch attached

[joehoyle]

Add LIKE to meta_compare

[dd32]

Due to 2.9's impending release, Setting to 3.0 based on the fact this has a patch.

See also:[BR]
#9124: enhancement: meta_compare needs to allow "in" as well (new)[BR]
#10800: enhancement: Allow LIKE comparison for $qmeta_compare? (new)[BR]

#10800 is a duplicate of this, However this has a patch, so not closing.

[joehoyle]

From #10800 :

NOT LIKE and IN would be handy too

anyone in favour of adding in a "NOT LIKE" to the allowed params?

[Denis-de-Bernardy]

If we add LIKE, we might as well add NOT LIKE.

The like_escape() function is buggy, however, so I'm a bit suspicious of the whole idea. A plugin dev who actually needs this kind of stuff should probably be using the posts_join filter instead.

[joehoyle]

If it used like_escape, there would need to be a different way of passing the "%"s instead of meta_value, as if it does like_escape( $meta_value ) the "%" will be escaped.

In terms of not doing it in favour of using posts_join, I feel is a bit of a workaround, hooking into posts_join, checking if the join has been added, then hooking into posts_where and inserting the LIKE statement seems like a lot of work compared to a proper implementation of using LIKE as a meta_compare. Especially as currently the posts_join and posts_where filters don't give you access to the WP_Query object it is a pain to check for query vars etc (global $wp_query not included).

[Denis-de-Bernardy]

Replying to joehoyle:

In terms of not doing it in favour of using posts_join, I feel is a bit of a workaround, hooking into posts_join, checking if the join has been added, then hooking into posts_where and inserting the LIKE statement seems like a lot of work compared to a proper implementation of using LIKE as a meta_compare. Especially as currently the posts_join and posts_where filters don't give you access to the WP_Query object it is a pain to check for query vars etc (global $wp_query not included).

Just in case you're not aware that you can put AND clauses in a JOIN statement, your example would be:

JOIN $wpdb->postmeta as my_plugin_meta
ON my_plugin_meta.post_id = $wpdb->posts.ID
AND my_plugin_meta.meta_key = 'url'
AND my_plugin_meta.meta_value LIKE '$my_sanitized_like_statement'

you can check the WP query in your plugin's function:

function posts_join($join) {
  global $wp_query;
  global $wp_the_query;
  # ignore if it's not the main loop
  if ( $wp_query !== $wp_the_query )
    return $join;

  // do stuff...
}

[joehoyle]

Just in case you're not aware that you can put AND clauses in a JOIN statement, your example would be:

Ahh I was not aware you could do that, thanks!

you can check the WP query in your plugin's function:

Again, learned another thing here, didn't know the current query was stored in $wp_the_query (as I presume from the example it is)

Even so, not included LIKE in meta_compare on the grounds that there is currently no function to easily sanitize the string seems wrong. Surely WP_Query should be as powerful as possible (within reason, and not getting in the way of ease of use) as WordPress is being used for more abstract uses, especially when it comes to post_meta. Adding less than 20 characters to the query class to allow this, potentially very useful enhancement (escape_like aside, as it seems that needs attention regardless).

Just thinking out loud, but it just seems like a natural progression for the class.

[Denis-de-Bernardy]

Replying to joehoyle:

you're welcome.

Even so, not included LIKE in meta_compare on the grounds that there is currently no function to easily sanitize the string seems wrong.

sure. basically, what's needed is to make sure than LIKE and NOT LIKE properly handle comparisons that include \_ or \% in the url. these would catch literal _ and % in strings without being treated as wildcards. Since we're using single quotes, the \ might need to be double-escaped to work (i.e.

).

[westi]

like_escape should be fixed by #10041 which will go into early 3.0 unless there are any extra issues which are not detailed on that ticket.

[Denis-de-Bernardy]

I've uploaded a patch that also adds <> and NOT LIKE to the list.

[hakre]

It should be documented wether or not escaping does apply for the wpdb::prepare() function. Just in case.

[Denis-de-Bernardy]

closed #11929 as dup

[Denis-de-Bernardy]

punting pending #10041 getting fixed, else we'll be introducing potential for SQL injections.

[scribu]

This was fixed in #9124