Array support in WP_Meta_Query when compare operator is LIKE or NOT LIKE

[digitty]

Usage scenario

Consider a lead custom post type with several custom fields (including contact name and company name) and a search form (with fields for each of the supported custom fields). Contact name and company name may contain multiple words and it should be possible to search for one or more words. The search form has an extra criteria <select> field for each custom field , allowing to search for ANY or ALL entered words.

Related tickets

Ticket #18707 is related to the enhancement discussed here but I thought of a more general approach: it should be possible to specify the operator between each LIKE / NOT LIKE comparison, to be either AND or OR.

This would allow specifying the ANY or ALL filter mentioned above without needing to edit the SQL clauses directly through posts_clauses filter:

(wp_postmeta.meta_key = 'cf_contact_name' AND (wp_postmeta.meta_value LIKE '%bob%' AND wp_postmeta.meta_value LIKE '%rob%'))
AND
(mt1.meta_key = 'cf_company_name' AND (mt1.meta_value LIKE '%com%' OR mt1.meta_value LIKE '%net%'))

Changes to WP_Meta_Query

The above SQL query excerpt should be possible with the following meta_query syntax:

'meta_query' => array(
    'relation' => 'AND',
    array(
        'key'     => 'cf_contact_name',
        'value'   => array( 'AND' => array('bob', 'rob') ),  // ALL filter
        'compare' => 'LIKE',
        'type'    => 'CHAR',
    ),
    array(
        'key'     => 'cf_company_name',
        'value'   => array( 'OR' => array('com', 'net') ),   // ANY filter
        'compare' => 'LIKE',
        'type'    => 'CHAR',
    ),
),

The patch file for meta.php that will allow this is attached.

[wonderboymusic]

Nice suggestion, digitty - it might be cleaner to have another property in the array for relation or condition:

array(
    'relation' => 'OR',
    'key'     => 'cf_company_name',
    'value'   => array( 'com', 'net' ),
    'compare' => 'LIKE',
    'type'    => 'CHAR',
),

Your idea would be a good way to short-circuit the need to add several related sets of meta_query conditions

[boonebgorges]

digitty - Thanks for the idea. This is an interesting suggestion.

I'm going to suggest that we don't make this change to the syntax of WP_Meta_Query. The main reason is that, as the conversation here suggests, simply allowing an array value for LIKE is ambiguous between AND and OR (and IN/NOT IN, for that matter).

Introducing a new layer of syntax - like 'value' => array( 'AND' => array( 'bob', 'rob' ) ) or another layer of 'relation' - doesn't seem worth it when you consider that tt's already possible to string together multiple LIKE/NOT LIKE clauses when building a meta_query argument. Your 'OR' example can be translated to:

'meta_query' => array(
    'relation' => 'OR',
    array(
        'key' => 'cf_company_name',
        'value' => 'com',
        'compare' => 'LIKE',
    ),
    array(
        'key' => 'cf_company_name',
        'value' => 'net',
        'compare' => 'LIKE',
    ),
)

And since [29887], it's possible to group these however you'd like, so that you're not limited in terms of how you want to combine these grouped LIKE queries with other query clauses. True, it's a bit more verbose than what you've suggested. But it keeps the syntax much simpler, and avoids our having more than one syntactical convention for expressing the same concept.

This is sort of a side note, but your example for AND - (wp_postmeta.meta_key = 'cf_contact_name' AND (wp_postmeta.meta_value LIKE '%bob%' AND wp_postmeta.meta_value LIKE '%rob%')) - will only match rows that have both '%bob%' AND '%rob%'. To get separate rows, you need a table join. So this is a real edge case, and should probably be handled by translating it into: LIKE '%bob%rob%' OR LIKE '%rob%bob%', which can be expressed in the syntax described above.

[boonebgorges]

Closing on the basis of [29887]. Thanks again for the suggestion.