Support » Plugin: Relevanssi - A Better Search » Show in which categories it was found

  • Resolved igora100

    (@igora100)


    Dear, Mikko.
    I want to do this:
    I entered the search string, for example, “printer”, pressed the “Search” button and in the results of the search, I want to see that in the category “Laser printers” found 20, in the category “Industrial Equipment” found 7.
    I can get the entire list of the found in this way:

    add_filter( 'relevanssi_hits_filter', 'relevanssi_hits_filter',200);
    function relevanssi_hits_filter ($filter_data){
    	global $__posts_find_relevanssi;
    	$__posts_find_relevanssi = $filter_data[0];
    	return($filter_data);
    }

    But then I need to get all the categories goods (product_cat), to which the goods belong. I can sort out the array of the products found and use get_the_terms get_the_terms( $post->ID, 'product_cat' ), but the get_the_terms function works slowly, if you consider that I have more than 300,000 products in the store.
    Your plugin already has data on categories of goods in cashe.
    I know that the array [posts] is also entered [relevanssi_hits]. to each product.
    Can I somehow in [relavanssi_hits] also contribute to those categories in which found goods found?
    Although … Such an adding array [relavanssi_hits] occurs only to the reflected goods, and not to all ….
    Maybe, Mikko, you have any thoughts? 😉

Viewing 7 replies - 1 through 7 (of 7 total)
  • Plugin Author Mikko Saari

    (@msaari)

    Relevanssi doesn’t know which posts are in which category; it doesn’t need to. If you use a category filter in the search, the filtering is done in the database.

    There are several solutions for this. First of all, in general you don’t need to worry much about having 300,000 products in the database. With the throttle in use, search result sets can’t be more than thousands of posts, which is generally manageable.

    But yes, even with that smaller set you don’t want to use get_the_terms() for each post. Instead, I’d recommend taking the IDs of the posts found and using one database query to get all the categories for those posts directly from the wp_term_relationships database table (maybe split it to few queries, if the query gets too big).

    Thread Starter igora100

    (@igora100)

    Relevanssi doesn’t know which posts are in which category; it doesn’t need to. If you use a category filter in the search, the filtering is done in the database.

    Many thanks, Mikko !
    In this case, relavsis still works with its cashe, but does an additional request in the database or not?

    get all the categories for those posts directly from the wp_term_relationships database table

    Thanks, I’ll try.

    Thread Starter igora100

    (@igora100)

    I fulfilled such a request:

    $args = [
    		'taxonomy' => 'product_cat',
    		'hide_empty' => false,
    		'object_ids' => $arr_find_ID,
    	];	
    	$Query_result = new WP_Term_Query($args);

    OR:

    SELECT DISTINCT t.*, tt.* FROM wp_terms AS t  LEFT JOIN wp_termmeta ON ( t.term_id = wp_termmeta.term_id AND wp_termmeta.meta_key='order') 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 ('product_cat') AND tr.object_id IN (437287, 442247, 437292, 437273 23755, 324303, 329711, 329821, 334945, 335027, 335618, 335945, 336112, 336114, 336508, 338523, 336726, 336791, 336803, 337167, 337168, 337434, 337477, 337502, 337551, 337596, 337772) AND ( 
      ( wp_termmeta.meta_key = 'order' OR wp_termmeta.meta_key IS NULL )
    )

    I very quickly was able to get all the categories in which the product was found, but …. I would also like to see how many products were found in one category or another (i.e. not how many total products in the category, but how many exactly were found!) … But I just can’t figure out how to do it …

    Plugin Author Mikko Saari

    (@msaari)

    If you query wp_term_relationships directly:

    SELECT object_id, term_taxonomy_id FROM wp_term_relationships WHERE object_id IN ( $arr_find_ID )

    Now you have an array of object ID and term taxonomy ID pairs, and from that data you can easily count how many times each term taxonomy ID appears.

    Thread Starter igora100

    (@igora100)

    O, mighty and great Mikko !!!
    I did it with your help!
    It turned out such a request:

    	$stringQ = "SELECT * FROM $wpdb->term_relationships tr ";
    	$stringQ .= " LEFT JOIN {$wpdb->term_taxonomy} tt ON (tr.term_taxonomy_id=tt.term_taxonomy_id AND tt.taxonomy='product_cat') " ;
    	$stringQ .= " LEFT JOIN {$wpdb->terms} t ON (t.term_id=tt.term_id) " ;
    	$stringQ .=	" WHERE tr.object_id IN ( " . implode( ',', $arr_find_ID ) . " )";

    At the output, I get an array of objects:

    [10] => stdClass Object
            (
                [object_id] => 442247
                [term_taxonomy_id] => 23517
                [term_order] => 0
                [term_id] => 23517
                [taxonomy] => product_cat
                [description] => 
                [parent] => 23515
                [count] => 398
                [name] => Смартфоны
                [slug] => smartfony
                [term_group] => 0
            )

    Next, I summarize the objects by term_id and everything is ready!
    The full time on the whole processing cycle (from the time of the formation of the found objects of the objects before the output of the formed table with the number of items found in each category) with 500 products found. It takes 0.01sec, which is very good and right 40 times faster!!! than using get_the_terms ()

    Thread Starter igora100

    (@igora100)

    Now you have an array of object ID and term taxonomy ID pairs, and from that data you can easily count how many times each term taxonomy ID appears.

    This was not enough, because In wp_term_relationships there are not only the product taxonomy terminals “product_cat”, I still had to attach both wp_term_taxonomy and immediately joined the wp_terms to see the names of categories.
    Thank you so much for your help !!! 🙂

    Plugin Author Mikko Saari

    (@msaari)

    If you’re happy with the support, please consider leaving a review. Thanks!

Viewing 7 replies - 1 through 7 (of 7 total)
  • You must be logged in to reply to this topic.