Check on which page specific result exist

Question

I have a $term_id = 123 and I want to find out on which page this term appears in the pagination. For example, we have:

$args = array(
    'taxonomy' => 'hello',
    'offset'       => $offset,
    'number'       => 10, // total per page
    'hide_empty' => false,
);

$term_query = new WP_Term_Query( $args );

And we get 1000 returned terms with 10 being displayed on each page. How do I find out on which page $term_id exists?

Obviously, I can loop through all results and do a $term->term_id == $term_id match and make a note of the iteration key, but I am hoping there is a more viable approach...?

While my case is specific to WP_Term_Query(), this should apply to querying other object like posts etc.

Answer 1

You can select this term_id from the DB and add row number column in SQL Query and then you can calculate in which page it should be.

For example:

global $wpdb;
$wanted_term = $wpdb->get_row("SELECT terms.* FROM (
    SELECT @rownum := @rownum + 1 AS rownum, t.*, tt.taxonomy
    FROM wp_terms AS t
    INNER JOIN wp_term_taxonomy AS tt
    ON t.term_id = tt.term_id, (SELECT @rownum := 0) AS r
    WHERE tt.taxonomy IN ('hello') /* replace taxonomy */
) AS terms WHERE terms.term_id=123 /* replace term_id */ ORDER BY terms.name ASC");

And calculate with PHP.

$terms_per_page = 10;
$term_in_page = ceil($wanted_term->rownum / $terms_per_page);

Update SQL Query to have meta key, object_ids and order by meta value

SELECT allterms.* FROM (
    SELECT @rownum := @rownum + 1 AS rownum, terms.* FROM (
        SELECT DISTINCT t.*, tt.taxonomy, tt.term_taxonomy_id, tm.meta_key, tm.meta_value, tr.object_id
        FROM wp_terms AS t
        INNER JOIN wp_termmeta AS tm ON ( t.term_id = tm.term_id )
        INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
        INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
        WHERE tt.taxonomy IN ('fn_release') /* replace taxonomy */
        AND tr.object_id IN (108) /* replace object_ids */
        AND ( tm.meta_key = 'ma_tsms' )
        ORDER BY tm.meta_value+0 DESC
    ) AS terms, (SELECT @rownum := 0) AS r
) AS allterms WHERE allterms.term_id=2543 /* replace term_id */

Query took 0.0020 seconds.