WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 3 years ago

#27344 closed enhancement (wontfix)

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

Reported by: digitty Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.8
Component: Query Keywords: has-patch needs-refresh needs-unit-tests close
Focuses: Cc:

Description

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.

Attachments (1)

meta.27344.patch (2.5 KB) - added by digitty 4 years ago.

Download all attachments as: .zip

Change History (6)

@digitty
4 years ago

#1 @digitty
4 years ago

  • Keywords meta-query has-patch added

#2 @SergeyBiryukov
4 years ago

  • Version changed from trunk to 3.8

#3 @wonderboymusic
4 years ago

  • Keywords needs-refresh needs-unit-tests added
  • Milestone changed from Awaiting Review to Future Release

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

#4 @boonebgorges
4 years ago

  • Keywords close added; meta-query removed

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.

#5 @boonebgorges
3 years ago

  • Milestone Future Release deleted
  • Resolution set to wontfix
  • Status changed from new to closed

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

Note: See TracTickets for help on using tickets.