Ticket #17264: unit-meta-query.php

<?php

add_action('template_redirect', function() {
        global $wpdb;

        assert_options(ASSERT_ACTIVE, 1);
        assert_options(ASSERT_QUIET_EVAL, 1);
        assert_options(ASSERT_WARNING, 0);
        assert_options(ASSERT_CALLBACK, function ($file, $line, $code) use ( &$failed ) {
                echo "Failed: $file (line $line)<hr />";
        });

        $mq = new WP_Meta_Query;

        // empty query
        $qv = array();

        $expected = array();

        $mq->parse_query_vars( $qv );

        assert( $mq->queries == $expected );

        $expected = array( 'where' => '', 'join' => '' );

        $clauses = $mq->get_sql( 'post', $wpdb->posts, 'ID' );

        assert( $clauses == $expected );

        // invalid query
        $queries = array(
                array(
                        'compare' => 'like',
                )
        );

        $clauses = get_meta_sql( $queries, 'post', $wpdb->posts, 'ID' );

        $expected = array( 'where' => '', 'join' => '' );

        assert( $clauses == $expected );

        // 'value' only
        $queries = array(
                array(
                        'value' => 'foo',
                        'compare' => 'like',
                )
        );

        $clauses = get_meta_sql( $queries, 'post', $wpdb->posts, 'ID' );

        $expected = array(
                'join' => " INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)",
                'where' => " AND ( (CAST(wp_postmeta.meta_value AS CHAR) LIKE '%foo%') )"
        );

        assert( $clauses == $expected );

        // 'meta_key' & 'meta_value' = 0
        $qv = array(
                'meta_key' => 'foo',
                'meta_value' => 0,
        );

        $expected = array(
                array(
                        'key' => 'foo',
                        'value' => '0'
                )
        );

        $mq->parse_query_vars( $qv );

        assert( $mq->queries == $expected );


        $expected = array(
                'join' => " INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)",
                'where' => " AND ( (wp_postmeta.meta_key = 'foo' AND CAST(wp_postmeta.meta_value AS CHAR) = '0') )"
        );

        $clauses = $mq->get_sql( 'post', $wpdb->posts, 'ID' );

        assert( $clauses == $expected );

        // Only 'meta_key' (meta_value should be ignored)
        $qv = array(
                'meta_key' => 'foo',
                'meta_value' => '',
        );

        $expected = array(
                array(
                        'key' => 'foo',
                )
        );

        $mq->parse_query_vars( $qv );
        assert( $mq->queries == $expected );

        $expected = array(
                'join' => " INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)",
                'where' => " AND (wp_postmeta.meta_key = 'foo' )"
        );

        $clauses = $mq->get_sql( 'post', $wpdb->posts, 'ID' );

        assert( $clauses == $expected );

        // 'key' and 'value' (value should _not_ be ignored)
        $meta_query = array(
                array(
                        'key' => 'foo',
                        'value' => '',
                        'compare' => '!='
                )
        );

        $expected = array(
                'join' => " INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)",
                'where' => " AND ( (wp_postmeta.meta_key = 'foo' AND CAST(wp_postmeta.meta_value AS CHAR) != '') )"
        );

        $clauses = get_meta_sql( $meta_query, 'post', $wpdb->posts, 'ID' );

        assert( $clauses == $expected );

        // empty 'value' with 'IN' comparison ('value' should not be ignored)
        $meta_query = array(
                array(
                        'key' => 'foo',
                        'value' => '0',
                        'compare' => 'NOT IN',
                )
        );

        $expected = array(
                'join' => " INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)",
                'where' => " AND ( (wp_postmeta.meta_key = 'foo' AND CAST(wp_postmeta.meta_value AS CHAR) NOT IN ('0')) )"
        );

        $clauses = get_meta_sql( $meta_query, 'post', $wpdb->posts, 'ID' );

        assert( $clauses == $expected );

        // 'relation' => 'AND' (default)
        $meta_query = array(
                array(
                        'key' => 'foo',
                        'value' => '0',
                        'compare' => 'NOT IN',
                ),

                array(
                        'key' => 'bar',
                ),
        );

        $expected = array(
                'join' => " INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)",
                'where' => " AND ( (wp_postmeta.meta_key = 'foo' AND CAST(wp_postmeta.meta_value AS CHAR) NOT IN ('0'))
AND mt1.meta_key = 'bar' )"
        );

        $clauses = get_meta_sql( $meta_query, 'post', $wpdb->posts, 'ID' );

        assert( $clauses == $expected );

        // 'relation' => 'OR'
        $meta_query = array(
                'relation' => 'or',
                array(
                        'key' => 'foo',
                        'value' => '0',
                        'compare' => 'NOT IN',
                ),

                array(
                        'key' => 'bar',
                ),

                array(
                        'value' => array(5, 6),
                        'compare' => 'BETWEEN',
                        'type' => 'NUMERIC'
                ),
        );

        $expected = array(
                'join' => " INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)",
                'where' => " AND ( (wp_postmeta.meta_key = 'foo' AND CAST(wp_postmeta.meta_value AS CHAR) NOT IN ('0'))
OR mt1.meta_key = 'bar'
OR  (CAST(mt2.meta_value AS SIGNED) BETWEEN '5' AND '6') )"
        );

        $clauses = get_meta_sql( $meta_query, 'post', $wpdb->posts, 'ID' );

        assert( $clauses == $expected );
});