1

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.

1 Answer 1

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.

9
  • Thanks. Doesn't this query all terms, making it quite a heavy query?
    – Christine Cooper
    Jun 19, 2018 at 11:06
  • @ChristineCooper Hi, I didn't tested it with a large DB but from what I see its faster than the other option to get all terms and then to filter in php the term_id... Try both options and see what work for you faster. If you are using pagination its mean you already run the get_terms and get all the terms I think you need to check this and maybe use the same results so you could just filter.
    – Shibi
    Jun 19, 2018 at 11:09
  • @ChristineCooper See my answer I added the query that you wanted..
    – Shibi
    Jun 19, 2018 at 12:03
  • Thank you for the updated query (works fine). Although the calculation ceil($wanted_term->rownum / $total_pages); doesn't give correct page number. Note that wp_count_terms() gives total terms and not total pages. I also tried by calculating ceil($wanted_term->rownum / ($total_pages / 10)); since I am showing 10 entries per page, but results are still incorrect. :*(
    – Christine Cooper
    Jun 19, 2018 at 12:17
  • @ChristineCooper Its could be because you need to add the order by meta value to the pagination too... to the wp_count_terms BTW as I said before if you already use the wp_count_terms used get_terms to get all the terms. So you could use get_terms instead and then get variables for the pagination and for the php filter.
    – Shibi
    Jun 19, 2018 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.