Support BETWEEN for term names in WP_Tax_Query/WP_Term_Query

[soulseekah]

This patch adds name__between parameter in WP_Term_Query and between operator in WP_Tax_Query.

[seredniy]

Great work, @soulseekah ! Usefull thing

[pento]

Patch needs testing and a decision.

[gabrielchung1128]

Hello, I am new to WP Core dev. I am not too sure if I am doing it correctly.

I have tested the code with the range between 7 and 7 and in PHPUnit, it returns an error message in HTML form.

Here is my test case:

<?php
    public function test_tax_terms_between_same_number() {
        register_taxonomy( $tax = 'height', 'post' );

        $posts = array();

        foreach ( range( 0, 20 ) as $height ) {
            $t = self::factory()->term->create(
                array(
                    'name'     => strval( $height ),
                    'taxonomy' => $tax,
                )
            );

            $p = self::factory()->post->create();
            wp_set_object_terms( $p, array( $t ), $tax );
            $posts[] = $p;
        }

        $q = new WP_Query(
            array(
                'fields'    => 'ids',
                'tax_query' => array(
                    array(
                        'taxonomy' => $tax,
                        'operator' => 'between',
                        'field'    => 'name',
                        'terms'    => array( 7, 7 )
                    ),
                ),
            )
        );

        $this->assertEqualSets( array_slice( $posts, 7, 0 ), $q->posts );
    }

Here is the error message:

WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 for query SELECT  t.*, tt.* FROM wptests_terms AS t  INNER JOIN wptests_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('height') AND    made by PHPUnit\TextUI\Command::main, PHPUnit\TextUI\Command->run, PHPUnit\TextUI\TestRunner->doRun, PHPUnit\Framework\TestSuite->run, PHPUnit\Framework\TestCase->run, PHPUnit\Framework\TestResult->run, PHPUnit\Framework\TestCase->runBare, PHPUnit\Framework\TestCase->runTest, Tests_HelloWorld->test_tax_terms_between, WP_Query->__construct, WP_Query->query, WP_Query->get_posts, WP_Tax_Query->get_sql, WP_Tax_Query->get_sql_clauses, WP_Tax_Query->get_sql_for_query, WP_Tax_Query->get_sql_for_clause, WP_Tax_Query->clean_query, WP_Tax_Query->transform_query, WP_Term_Query->query, WP_Term_Query->get_terms
F<div id="error"><p class="wpdberror"><strong>WordPress database error:</strong> [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#039;&#039; at line 1]<br /><code>SELECT  t.*, tt.* FROM wptests_terms AS t  INNER JOIN wptests_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN (&#039;height&#039;) AND   </code></p></div>

}}}

[SergeyBiryukov]

Missed the 5.2 Beta 1 deadline, moving to 5.3.

[boonebgorges]

Thanks for the patch.

What is the use case for this? The patch is written in a way that casts term name to SIGNED. But this assumes a situation where term names are integers. This is an edge case at best. A more generic BETWEEN that allows string comparisons (ie alphabetical, based on collation) feels more useful.

If we go forward with this, the find_compatible_table_alias() logic in WP_Tax_Query::get_sql_for_clause() should be broken out into a separate block so as to be reusable for BETWEEN and IN, with the actual SQL construction happening afterward. This'll allow us to avoid the $operator juggling in 44658.diff​.

We might also consider some more robust checking of the terms array in the case of name__between, instead of simply taking the first two values of the array. See eg https://core.trac.wordpress.org/browser/tags/5.2.3/src/wp-includes/date.php#L823.