Convert query($args) to $wpdb->query WordPress
- SOLVED
Hello,
I'm working on a little database based on wordpress and I'm stuck with my query. I can't search with tax_query (RELATION = AND) and a meta_query (RELATION = OR). If you need the php code which produces this $args_array please let me know. I hope it's possible to implement paged with a direct wpdb->query. I have no idea.
Any help will be appreciated.
Best regards,
Knoobie
query($args)
if (have_posts()) : while (have_posts()) : the_post();
// ...
endwhile;endif;
<strong>$args -></strong>
Array
(
[post_type] => anime
[post_status] => publish
[orderby] => title
[order] => ASC
[showposts] => 10
[posts_per_page] => 10
[paged] => 0
[tax_query] => Array
(
[relation] => AND
[0] => Array
(
[taxonomy] => a_types
[field] => slug
[terms] => Array
(
[0] => serie
)
[operator] => IN
)
[1] => Array
(
[taxonomy] => Genre
[field] => slug
[terms] => Array
(
[0] => action
[1] => fantasy
)
[operator] => IN
)
)
[meta_query] => Array
(
[relation] => OR
[0] => Array
(
[key] => germanname
[value] => name
[compare] => LIKE
)
[1] => Array
(
[key] => englishname
[value] => name
[compare] => LIKE
)
[2] => Array
(
[key] => germanname
[value] => name
[compare] => LIKE
)
)
)
Generate the query:
$post_genre = 'ALL';
$post_types = 'ALL';
$post_name = '';
if (isset($_GET['Genre'])) {
$post_genre = $_GET['Genre'];
}
if (isset($_GET['Type'])) {
$post_types = $_GET['Type'];
}
if (isset($_GET['n'])) {
$post_name = $_GET['n'];
}
$args = kn_anime_query_args(10, 10, $paged);
if ($post_types != 'ALL') {
$types = kn_anime_query_build_singletax('a_types', $post_types);
}
if ($post_genre != 'ALL') {
$genres = kn_anime_query_build_singletax('Genre', $post_genre);
}
$form_tax_fields = array($types, $genres);
$args = kn_anime_query_add_multitval($args, $form_tax_fields, 'tax_query', 'AND');
if (!isEmpty($post_name)) {
$jap = kn_anime_query_build_singlemeta('originalname', $post_name, 'LIKE');
$ger = kn_anime_query_build_singlemeta('germanname', $post_name, 'LIKE');
$eng = kn_anime_query_build_singlemeta('englishname', $post_name, 'LIKE');
$form_name_fields = array($ger, $eng, $jap);
$args = kn_anime_query_add_multitval($args, $form_name_fields, 'meta_query', 'OR');
echo "<pre>";
var_dump($args);
echo "</pre>";
}
query_posts($args);
Helper functions:
function array_push_associative(&$arr) {
$args = func_get_args();
foreach ($args as $arg) {
if (is_array($arg)) {
foreach ($arg as $key => $value) {
$arr[$key] = $value;
$ret++;
}
} else {
$arr[$arg] = "";
}
}
return $ret;
}
function kn_anime_query_args($post_limit = 10, $post_per_page = NULL, $paged = NULL) {
$args = array('post_type' => 'anime', 'post_status' => 'publish', 'orderby' => 'title', 'order' => 'ASC');
if (!is_null($post_limit)) {
$item1 = array('showposts' => $post_limit);
array_push_associative($args, $item1);
}
if (!is_null($paged) && !is_null($post_per_page)) {
$item1 = array('posts_per_page' => $post_per_page);
$item2 = array('paged' => $paged);
array_push_associative($args, $item1, $item2);
}
return $args;
}
function kn_anime_query_build_singlemeta($meta_name = '', $meta_item = '', $operator = 'LIKE'){
$meta = array(
'key' => $meta_name,
'value' => $meta_item,
'compare' => $operator
);
return $meta;
}
function kn_anime_query_build_singletax($tax_name = '', $tax_items = array(), $operator = 'IN'){
$tax = array(
'taxonomy' => $tax_name,
'field' => 'slug',
'terms' => $tax_items,
'operator' => $operator
);
return $tax;
}
function kn_anime_query_add_multitval($args = array(), $items = array(), $val = 'tax_query', $multi_relation = 'AND') {
if ($items == NULL || $args == NULL) {
return $args;
}
$relation = array('relation' => $multi_relation);
foreach ($items as $value) {
array_push($relation, $value);
}
$item1 = array($val => $relation);
array_push_associative($args, $item1);
return $args;
}
function kn_anime_query_add_taxo($args = array(), $tax_name = NULL, $tax_items = NULL, $multi_relation = 'AND') {
if ($tax_name == NULL || $tax_items == NULL) {
return $args;
}
if (!is_array($tax_items)) {
if (!is_null($tax_items) && $tax_items != 'ALL') {
$item1 = array($tax_name => $tax_items);
array_push_associative($args, $item1);
}
} else {
$relation = array('relation' => $multi_relation);
array_push($relation, array(
'taxonomy' => $tax_name,
'field' => 'slug',
'terms' => $tax_items,
'operator' => 'IN'
));
$item1 = array('tax_query' => $relation);
array_push_associative($args, $item1);
}
return $args;
}
Answers (1)
Hariprasad Vijayan answers:
Hello,
Please show the php code used for generate query.
Knoobie comments:
updated with php code.
Hariprasad Vijayan comments:
Are you getting wrong result after executing the query?
Knoobie comments:
No. Nothing happend. Whitescreen of death. Everything works fine when I add
$args = kn_anime_query_add_multitval($args, $form_tax_fields, 'tax_query', 'AND');
$args = kn_anime_query_add_multitval($args, $form_name_fields, 'meta_query', '<strong>AND</strong>');
But I need an <strong>OR</strong> and this isn't possible with the normal query, that's why I'm looking for a direct database query. But sadly I'm not good with direct work on databases.
Hariprasad Vijayan comments:
Hello,
Do you wan't to combine both meta_query and tax_query using "OR"?
Knoobie comments:
I want to combine my "or-meta_query" and my "and-meta_query" by 'AND'.
Thank you!
Knoobie comments:
Edit: "and-tax_query"*
Hariprasad Vijayan comments:
Hi,
Check following query
$custom_qry = "SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) WHERE 1=1 AND 0 = 1 AND wp_posts.post_type = 'anime' AND (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key = 'germanname' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%Your_name%')
OR (mt1.meta_key = 'englishname' AND CAST(mt1.meta_value AS CHAR) LIKE '%Your_name%')
OR (mt2.meta_key = 'germanname' AND CAST(mt2.meta_value AS CHAR) LIKE '%Your_name%') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_title ASC LIMIT 0, 10"
$total_hotels = $wpdb->get_results($custom_qry, OBJECT);
Hariprasad Vijayan comments:
You need to change "Your_name" to $_GET values
Knoobie comments:
var_dump($total_hotels);
=> array(0) { }
... WHERE 1=1 AND 0 = 1 AND..
This works?
Hariprasad Vijayan comments:
hello,
please provide the link that you are working. i just want to check the curreny working
Knoobie comments:
At the moment I'm working on a local installation, I can send you an database dump if you need some test cases.
Knoobie comments:
Database dump: http://www.mediafire.com/view/?rir1som173ta8wr
pw: da12Dmpxaln134!
Hariprasad Vijayan comments:
Are you getting correct result for tax_query (RELATION = AND) and a meta_query (RELATION = OR) while executing it seperatly?
Hariprasad Vijayan comments:
Are you getting correct result for tax_query (RELATION = AND) and a meta_query (RELATION = OR) while executing it separately?
Knoobie comments:
meta_query (RELATION = OR) <-- isn't possible with wordpress_query($args). That's my problem. A tax_query (RELATION = AND) and meta_query (RELATION = AND) separately works great.
Hariprasad Vijayan comments:
Hello,
Have you tried like this?
<?php
$post_genre = 'ALL';
$post_types = 'ALL';
$post_name = '';
if (isset($_GET['Genre'])) {
$post_genre = $_GET['Genre'];
}
if (isset($_GET['Type'])) {
$post_types = $_GET['Type'];
}
if (isset($_GET['n'])) {
$post_name = $_GET['n'];
}
$args = kn_anime_query_args(10, 10, $paged);
if ($post_types != 'ALL') {
$types = kn_anime_query_build_singletax('a_types', $post_types);
}
if ($post_genre != 'ALL') {
$genres = kn_anime_query_build_singletax('Genre', $post_genre);
}
$form_tax_fields = array($types, $genres);
if (!isEmpty($post_name)) {
$jap = kn_anime_query_build_singlemeta('originalname', $post_name, 'LIKE');
$ger = kn_anime_query_build_singlemeta('germanname', $post_name, 'LIKE');
$eng = kn_anime_query_build_singlemeta('englishname', $post_name, 'LIKE');
$form_name_fields = array($ger, $eng, $jap);
$args = kn_anime_query_add_multitval($args, $form_name_fields, 'meta_query', 'OR');
}
$args = kn_anime_query_add_multitval($args, $form_tax_fields, 'tax_query', 'AND');
echo "<pre>";
var_dump($args);
echo "</pre>";
query_posts($args);
?>
Knoobie comments:
You mean change the position of meta and tax? Yes, both ways didn't work.
Knoobie comments:
See: http://wordpress.stackexchange.com/a/35686/32836
Hariprasad Vijayan comments:
Hi,
Check something like this,
<?php
$args = array(
'post_type' => 'post',
'post_status'=> 'publish',
'orderby'=> 'title',
'order'=> 'ASC',
'showposts'=> 10,
'posts_per_page'=> 10,
'meta_query' => array(
'relation' => 'OR',
array(
'key' => 'custom_field1',
'value' => 'test1',
'compare' => 'LIKE'
),
array(
'key' => 'custom_field2',
'value' => 'test2',
'compare' => 'LIKE'
),
),
'tax_query' => array(
'relation' => 'AND',
array(
'taxonomy' => 'test_taxonomy',
'field' => 'slug',
'terms' => array('test_taxonomy1','test_taxonomy2')
),
array(
'taxonomy' => 'test_tax',
'field' => 'slug',
'terms' => array('test_tax1')
)
)
);
$my_query = new WP_Query($args);
if( $my_query->have_posts() ) : while ($my_query->have_posts()) : $my_query->the_post(); ?>
<p><?php the_title(); ?></p>
<?php
endwhile; endif;
?>
Hariprasad Vijayan comments:
Replace meta field name and taxonomy name with your values
Hariprasad Vijayan comments:
I mean replace meta_key name and taxonomy name
Knoobie comments:
Looks like timeout~ the query won't be executed and the footer didn't load. I think it's a timeout and my old method would also work, but timeout kills all? When I change meta_query 'OR' => to 'AND' everything works fine...
Hariprasad Vijayan comments:
Please give me the executed code and the full url. I need the parameters that passed through the url
Knoobie comments:
I've hardcoded it. Localhost ;) I don't have an online version at the moment.
$args = array(
'post_type' => 'anime',
'post_status'=> 'publish',
'orderby'=> 'title',
'order'=> 'ASC',
'showposts'=> 10,
'posts_per_page'=> 10,
'meta_query' => array(
'relation' => 'OR',
array(
'key' => 'germanname',
'value' => 'Clannad',
'compare' => 'LIKE'
),
array(
'key' => 'englishname',
'value' => 'Clannad',
'compare' => 'LIKE'
),
),
'tax_query' => array(
'relation' => 'AND',
array(
'taxonomy' => 'Genre',
'field' => 'slug',
'terms' => array('comedy','drama')
),
array(
'taxonomy' => 'a_types',
'field' => 'slug',
'terms' => array('serie')
)
)
);
Knoobie comments:
Thank you for your help but I think its impossible because of a timeout.
This works great so far. When I change the <strong>AND</strong> to an OR it's broken.
$my_query = "SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID
FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id)
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
WHERE
1=1 AND
( wp_term_relationships.term_taxonomy_id IN (10,15) AND tt1.term_taxonomy_id IN (185) ) AND
wp_posts.post_type = 'anime' AND (wp_posts.post_status = 'publish') AND
( (wp_postmeta.meta_key = 'germanname' AND
CAST(wp_postmeta.meta_value AS CHAR) LIKE '%Clannad%') <strong>AND</strong>
(mt1.meta_key = 'englishname' AND
CAST(mt1.meta_value AS CHAR) LIKE '%Clannad%') )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_title
ASC LIMIT 0, 10";