Add REGEXP to 'compare' values in meta_query

[FlashUK]

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!

[wonderboymusic]

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.

[wonderboymusic]

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();

[donwilson]

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

[MikeSchinkel]

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

[wonderboymusic]

is anyone scared by this?

[pento]

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

[nacin]

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.