WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 2 years ago

#18736 closed enhancement (fixed)

Add REGEXP to 'compare' values in meta_query

Reported by: FlashUK Owned by: nacin
Milestone: 3.7 Priority: normal
Severity: normal Version: 3.2.1
Component: Database Keywords: has-patch
Focuses: Cc:

Description

I wish to sort some queries using the alphabet.

My current query:

$getPostIds = $wpdb->get_col("
    SELECT p.ID
    FROM $wpdb->posts p, $wpdb->postmeta pm
    WHERE p.ID = pm.post_id 
    AND pm.meta_key = 'prod-id' 
    AND pm.meta_value
    REGEXP '^" . $wpdb->escape($alpha) . "'
    AND p.post_status = 'publish' 
    AND p.post_type = 'review'
    AND p.post_date < NOW()
    ORDER BY pm.meta_value ASC"
);

$posts = new WP_Query(array(
    'post__in' => $getPostIds,
    'post_type' => 'review',
    'post_status' => 'publish',
    'showposts' => 20,
    'paged' => $paged,
    'caller_get_posts' => 1, // do not order sticky posts at the top
    'orderby' => 'title',
    'order' => 'ASC'
));

It would be much simpler if I could use the equivalent with meta_query:

query_posts(array(
    'post_type' => 'review',
    'post_status' => 'publish',
    'showposts' => 20,
    'paged' => $paged,
    'caller_get_posts' => 1, // do not order sticky posts at the top
    'orderby' => 'metal_value',
    'order' => 'ASC',
    'meta_query' => array(
        array(
            'key'   => 'prod-id',
            'value' => '^'.get_query_var('letter'),
            'compare' => 'REGEXP'
        )
    )
));

I'd love to see it implemented :)
Thanks!

Attachments (1)

meta-regexp.diff (1.0 KB) - added by wonderboymusic 3 years ago.

Download all attachments as: .zip

Change History (10)

@wonderboymusic3 years ago

comment:1 @wonderboymusic3 years ago

  • Keywords has-patch added

This is really easy to implement - not sure if anything more than $wpdb->prepare is needed to escape RegEx, but the whole meta_compare thing is really "you break it, you bought it" anyways. Attached a patch.

comment:2 @wonderboymusic3 years ago

To log SQL:

update_post_meta( 1, 'num_as_longtext', 123 );
update_post_meta( 2, 'num_as_longtext', 99 );

add_filter( 'query', function ( $sql ) { error_log( $sql ); return $sql; } );

new WP_Query( array( 'post_type' => 'any', 'meta_type' => 'UNSIGNED', 'meta_compare' => 'REGEXP', 'meta_key' => 'num_as_longtext', 'meta_value' => '^1' ) );
exit();

comment:3 @donwilson3 years ago

  • Cc donwilson added

comment:4 @donwilson3 years ago

Would like to see this used on meta_query.compare as well.

comment:5 @MikeSchinkel2 years ago

+1 - Just ran into a need for this as well.

comment:6 @MikeSchinkel2 years ago

  • Cc mike@… added

comment:7 @wonderboymusic2 years ago

  • Milestone changed from Awaiting Review to 3.7

is anyone scared by this?

comment:8 @pento2 years ago

REGEXP is usually a terrible idea to use, but I see no reason to disallow it.

comment:9 @nacin2 years ago

  • Owner set to nacin
  • Resolution set to fixed
  • Status changed from new to closed

In 25525:

Add REGEXP to meta queries.

"REGEXP is usually a terrible idea to use, but I see no reason to disallow it."

props wonderboymusic.
fixes #18736.

Note: See TracTickets for help on using tickets.