Ask your WordPress questions! Pay money and get answers fast! (more info)

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)

2013-05-15

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";