admin管理员组文章数量:1122832
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
*
*
*/
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' );
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' );
1 Answer
Reset to default 0Finally 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' );
本文标签： phpWPML postsjoins on translated items
版权声明：本文标题：php - WPML posts_joins on translated items 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736310917a1934550.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论