0

I found a way to filter the search query so it goes though the product_cat title and description to look for a term and then display all the connected product.

I am now looking for a way to make it work with the WPML plugin.

I know that the relationship table is wp_icl_translations where you have the element_id (= term_taxonomy_id or term_id) and trid (the translation id, used to connect the original post to the other) but I can't figure out how to write a good INNER JOIN for this.

For example : if I look for the term "cat", it find it in the wp_terms table with cat in its name and its ID 1. In the wp_icl_translations table, I'll find one row with element_id 1 and it's trid with the value 9. If I search in the wp_icl_translations table for all rows with trid 9, I'll find another row with the element_id 2 which is the one I need to connect to term_relationship table and select all the connected posts.

My actual code is this one (and doesn't work). The code I figured out works great but only on the default language (since it doesn't connect languages yet).

The inner join are probably not great, that's the problem. It makes me want to tear my hair out..

function cf_search_join( $join ) {
    global $wpdb;

    $wpml_default_lang  = apply_filters('wpml_default_language', NULL );
    $wpml_current_lang  = apply_filters('wpml_current_language', NULL );
    $is_default_lang    = ($wpml_default_lang === $wpml_current_lang ? true : false);
    $wpml_table         = $wpdb->prefix . 'icl';


    if ( is_search() ) {

        if($is_default_lang) {
          $join .= "
          INNER JOIN {$wpdb->term_relationships} ON {$wpdb->posts}.ID = {$wpdb->term_relationships}.object_id
          INNER JOIN {$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_relationships}.term_taxonomy_id
          INNER JOIN {$wpdb->terms} ON {$wpdb->terms}.term_id = {$wpdb->term_taxonomy}.term_id
          ";
        } else {
          $join .= "
          INNER JOIN {$wpdb->term_relationships} ON {$wpdb->posts}.ID = {$wpdb->term_relationships}.object_id
          INNER JOIN {$wpdb->term_taxonomy} ON (
            {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_relationships}.term_taxonomy_id
            AND
            {$wpdb->term_taxonomy}.term_taxonomy_id = trans_origin.element_id
          )
          INNER JOIN {$wpdb->terms} ON (
            {$wpdb->terms}.term_id = {$wpdb->term_taxonomy}.term_id
            AND
            {$wpdb->terms}.term_id = trans_origin.element_id
          )

          INNER JOIN {$wpdb->term_taxonomy} term_tax_search ON trans_search.element_id = term_tax_search.term_id
          INNER JOIN {$wpdb->terms} term_search ON trans_search.element_id = trans_search.term_id

          INNER JOIN {$wpml_table}_translations trans_search ON trans_search.element_id = term_tax_search.term_id
          INNER JOIN {$wpml_table}_translations trans_origin ON trans_search.element_id = trans_search.element_id
          ";
        }

        echo '<!-- '.($is_default_lang ? 'default' : 'not default').' – '.$join.' -->';
    }

    return $join;
}
add_filter('posts_join', 'cf_search_join' );

/**
 * Modify the search query with posts_where
 *
 * http://codex.wordpress.org/Plugin_API/Filter_Reference/posts_where
 */
function cf_search_where( $where ) {
    global $pagenow, $wpdb;

    $wpml_default_lang  = apply_filters('wpml_default_language', NULL );
    $wpml_current_lang  = apply_filters('wpml_current_language', NULL );
    $is_default_lang    = ($wpml_default_lang === $wpml_current_lang ? true : false);
    $wpml_table         = $wpdb->prefix . 'icl';

    if ( is_search()) {


        if($is_default_lang) {
          $where = preg_replace(
            "/\(\s*".$wpdb->posts.".post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
            "
              (".$wpdb->posts.".post_title LIKE $1)
              OR
              (".$wpdb->posts.".post_content LIKE $1)
              OR
              (".$wpdb->terms.".name LIKE $1)
              OR
              (".$wpdb->term_taxonomy.".description LIKE $1)
            ", $where );
        } else {
          $where = preg_replace(
            "/\(\s*".$wpdb->term_taxonomy.".description\s+LIKE\s*(\'[^\']+\')\s*\)/",
            "
              (".$wpdb->posts.".post_title LIKE $1)
              OR
              (".$wpdb->posts.".post_content LIKE $1)
              OR
              (term_search.name LIKE $1)
              OR
              (term_tax_search.description LIKE $1)
            ", $where );
        }


    }

    return $where;
}
add_filter( 'posts_where', 'cf_search_where' );
Improve this question

1 Answer 1

Reset to default
0

Finally able to find out a working solution. The inner join has to join term_taxonomy (where we find the term) to icl_translations with current language. And then from icl_translation to the origin language to get the element_id that then this element_id to term_relationships (where all the product from the origin product_cat are listed).

If that helps anyone, here it is :

function cf_search_join( $join ) {
    global $wpdb;

    $wpml_default_lang  = apply_filters('wpml_default_language', NULL );
    $wpml_current_lang  = apply_filters('wpml_current_language', NULL );
    $is_default_lang    = ($wpml_default_lang === $wpml_current_lang ? true : false);
    $wpml_table         = $wpdb->prefix . 'icl';


    if ( is_search() ) {

      $join .= "INNER JOIN {$wpdb->term_relationships} ON {$wpdb->posts}.ID = {$wpdb->term_relationships}.object_id ";

        if($is_default_lang) {
          $join .= "INNER JOIN {$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_relationships}.term_taxonomy_id ";
        } else {
          $join .= "
          INNER JOIN {$wpml_table}_translations trans_default ON (
            trans_default.element_id = {$wpdb->term_relationships}.term_taxonomy_id
            AND
            trans_default.language_code = '".$wpml_default_lang."'
          )

          INNER JOIN {$wpml_table}_translations trans_current ON (
            trans_default.trid = trans_current.trid
            AND
            trans_current.language_code = '".$wpml_current_lang."'
          )

          INNER JOIN {$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_taxonomy_id = trans_current.element_id
          ";
        }

        $join .= "INNER JOIN {$wpdb->terms} ON {$wpdb->terms}.term_id = {$wpdb->term_taxonomy}.term_id ";
    }

    return $join;
}
add_filter('posts_join', 'cf_search_join' );

/**
 * Modify the search query with posts_where
 *
 * http://codex.wordpress.org/Plugin_API/Filter_Reference/posts_where
 */
function cf_search_where( $where ) {
    global $pagenow, $wpdb;

    $wpml_default_lang  = apply_filters('wpml_default_language', NULL );
    $wpml_current_lang  = apply_filters('wpml_current_language', NULL );
    $is_default_lang    = ($wpml_default_lang === $wpml_current_lang ? true : false);
    $wpml_table         = $wpdb->prefix . 'icl';

    if ( is_search()) {


       $where = preg_replace(
       "/\(\s*".$wpdb->posts.".post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
       "
         (".$wpdb->posts.".post_title LIKE $1)
         OR
         (".$wpdb->posts.".post_content LIKE $1)
         OR
         (".$wpdb->terms.".name LIKE $1)
         OR
         (".$wpdb->term_taxonomy.".description LIKE $1)
       ", $where );


    }

    return $where;
}
add_filter( 'posts_where', 'cf_search_where' );
Improve this answer

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