Support » Plugin: SQLite Integration » Multi meta queries returns no results

  • Hi again.

    After your answer in my last post (http://wordpress.org/support/topic/wordpress-meta-query-not-working) and your last update the between operator is working fine.

    A scenario came up where I need to use multi meta queries combined with tax queries. I noticed that when I append another meta query the query is being executed but it returns empty results (the post array is empty).

    Having the same code, same data on a mysql server the query returns the expected results.

    With multi tax queries and one meta query the query is working fine and the results also.

    A var_dump of my query (the same var_dump on mysql also)

    array(9) {
      ["posts_per_page"]=>
      int(20)
      ["offset"]=>
      int(0)
      ["orderby"]=>
      string(2) "id"
      ["order"]=>
      string(3) "ASC"
      ["post_type"]=>
      string(7) "learner"
      ["post_status"]=>
      string(7) "publish"
      ["suppress_filters"]=>
      bool(false)
      ["meta_query"]=>
      array(3) {
        [0]=>
        array(4) {
          ["key"]=>
          string(15) "rlp_learner_age"
          ["value"]=>
          array(2) {
            [0]=>
            string(1) "0"
            [1]=>
            string(2) "59"
          }
          ["type"]=>
          string(7) "numeric"
          ["compare"]=>
          string(7) "BETWEEN"
        }
        [1]=>
        array(4) {
          ["key"]=>
          string(29) "rlp_learner_placement_percent"
          ["value"]=>
          array(2) {
            [0]=>
            string(2) "14"
            [1]=>
            string(3) "100"
          }
          ["type"]=>
          string(7) "numeric"
          ["compare"]=>
          string(7) "BETWEEN"
        }
        [2]=>
        array(4) {
          ["key"]=>
          string(31) "rlp_learner_age_started_spanish"
          ["value"]=>
          array(2) {
            [0]=>
            string(1) "0"
            [1]=>
            string(2) "49"
          }
          ["type"]=>
          string(7) "numeric"
          ["compare"]=>
          string(7) "BETWEEN"
        }
      }
      ["tax_query"]=>
      array(1) {
        [0]=>
        array(3) {
          ["taxonomy"]=>
          string(3) "sex"
          ["field"]=>
          string(2) "id"
          ["terms"]=>
          array(1) {
            [0]=>
            string(2) "71"
          }
        }
      }
    }

    My code :

    function rlp_get_posts($fields, $post_type = 'post', $num = 10, $offset = 0){
    
    	$args = array(
    		'posts_per_page'   => $num,
    		'offset'           => $offset,
    		'orderby'          => 'id',
    		'order'            => 'ASC',
    		'post_type'        => $post_type,
    		'post_status'      => 'publish',
    		'suppress_filters' => false
    	);
    
    	foreach($fields as $var){
    
    		$qv = get_query_var($var->slug);
    		$field = 'id';
    
    		if (!empty($qv) ) {
    
    			if(!is_array($qv))
    				$qv = explode(",", $qv);
    
    			if($var->type == 'tax'){
    
    				$tax_query_args[] =
    					array(
    						'taxonomy' => $var->slug,
    						'field' => $field,
    						'terms' => $qv
    					);
    
    			}else if($var->type == 'meta'){
    
    				$meta_query_args[] =
    					array(
    						'key' => $var->id,
    						'value' => $qv,
    						'type' => $var->meta_type,
    						'compare' => $var->meta_compare
    					);
    
    			}
    
    		}
    
    	}
    
    	if(!empty($meta_query_args))
    		$args['meta_query'] = $meta_query_args;
    
    	if(!empty($tax_query_args))
    		$args['tax_query'] = $tax_query_args;
    
    	return new WP_Query( $args );
    
    }

    Thanks in advance
    https://wordpress.org/plugins/sqlite-integration/

Viewing 15 replies - 1 through 15 (of 15 total)
  • Plugin Author kjmtsh

    (@kjmtsh)

    Hello, laxmana

    Thank you for the report. It helped me a lot to make sure what to be fixed.

    Will you try the developer version? Access the address below:

    http://plugins.svn.wordpress.org/sqlite-integration/trunk/

    Download query.class.php and replace sqlite-integration/query.class.php with it.

    I hope it will help.

    Hello!

    You also help me a lot accomplish my project with your great support. I try it and I get :

    [16-Jun-2014 15:46:12] PHP Fatal error: Maximum execution time of 30 seconds exceeded in /disk11/home/myuser/public_html/cedel2/wp-content/plugins/sqlite-integration/pdoengine.class.php on line 625

    Source : debug.log

    If you need me to provide anything else please tell me.

    Thanks again!

    Plugin Author kjmtsh

    (@kjmtsh)

    I tried another fix and committed just now. Will you try the new file again?

    Same url ( http://plugins.svn.wordpress.org/sqlite-integration/trunk/ ) and only to replace the query.class.php ?

    Plugin Author kjmtsh

    (@kjmtsh)

    Oh, yes. I committed another fixed version of that file.

    Same error :

    Fatal error: Maximum execution time of 30 seconds exceeded in /disk11/home/myuser/public_html/cedel2/wp-content/plugins/sqlite-integration/pdoengine.class.php on line 625

    Is anything I can do to help you more ?

    Plugin Author kjmtsh

    (@kjmtsh)

    I committed yet another fixed version of query.class.php. Will you try the new file again?

    Thank you for your patience, laxmana.

    Again the same error 🙁

    With one meta query everything is working fine.

    This is the page with the problem : http://wdb.ugr.es/~zanos/cedel2/learners/

    System info :

    http://istoreimg.com/i/53a15fa966dc6.html

    http://istoreimg.com/i/53a15faa5b14b.html

    Plugin Author kjmtsh

    (@kjmtsh)

    Hmm… strange. I couldn’t reproduce that error.

    Will you help me with doing tests?

    Test 1: I’d like to know what query WordPres is trying to execute.

    Change the last line of your code from

    return new WP_Query( $args );

    to

    $another_query = new WP_Query( $args );
    echo $another_query->request;
    return $another_query;

    and post the output query. Syntax structure is enough. So if that query contains the data you want to hide, you can change the db prefix or the column name etc.

    Test 2: I’d like to know if PHP could finish executing when given enough time.

    Open pdoengine.class.php and change the constructor function like below:

    function __construct() {
        $this->init();
        ini_set('max_execution_time', 60); // add this line
    }

    When you have the same error, what if change the number to 120?

    Thanks.

    I tried and I noticed that the fatal error is being produced when the WP_Query object is being created so the script won’t go to the next line to echo the request. I tried also to log it to a file, to var_dump but same results. When I execute a query without metaqueries the request is being echo / log. So I think the problem is there on the creation of WP_Query object and the fatal error occurs before the echo so echo won’t be executed.

    I tried to change the max_execution_time to 60 and 120 but it seems the initial time (30) remains.

    Fatal error: Maximum execution time of 30 seconds exceeded – The 30 remains even with max_execution_time set to 60 or 120.

    Maybe a server / php restriction ?

    The url that produce the error

    http://wdb.ugr.es/~zanos/cedel2/learners/?pt=learner&sex=75&age=0,59&grade=14,100&age-started=0,49

    Do you mind turn it to a private conversation ? Do you have an email contact ? The server is on my university so if I take the OK I can give you the FTP and Admin Panel credentials to take a better look.

    I would prefer you don’t go private on this conversation, but if you need to, please post the results of this investigation back to this thread.

    Thanks,

    steady

    Plugin Author kjmtsh

    (@kjmtsh)

    Hi, laxmana.

    Thank you for your testing.

    Maybe a server / php restriction ?

    As for max execution time, that could be. But we can’t make sure so far.

    FTP and Admin Panel credentials

    seems to be the last resort. I hope we can investigate some more details of the problem. You can contact me via email if necessary. Read the copyright notice in sqlite-integration.php. But I ask you not to expect the quick response.

    I reconstructed the SQL statement from your dump data. I’d like to know if SQLite Integration rewrites the statement properly or not. So will you do another test, please?

    Create a PHP script contains the code below and put it to WordPress installation directory. Then access the file with the browser. If it works, you can see if the PHP is set to safe mode or not and you can also see the rewritten query statement with the result of the executed query. The rewritten query statement is the very thing I’d like to know.

    After finishing the test, don’t forget to remove this script from your sever.

    Thanks a lot.

    @steadybright
    Thank you for your advice. I agree with you.

    <?php
    $mode = ini_get('safe_mode') ? 'safe mode' : 'normal mode';
    if (strpos($mode, 'normal') !== false) {
        ini_set('max_execution_time', 60);
    }
    echo 'PHP is set to ' . $mode . '.<br />';
    echo 'The value of the max_execution_time is set to ' . ini_get('max_execution_time');
    echo '.<br /><br />';
    
    // if you put this script to another directory, change the path below.
    require_once('./wp-load');
    require_once('./wp-content/plugins/sqlite-integration/query.class.php');
    
    global $wpdb;
    // reconstructed SQL statement
    $sql =<<<END_OF_SQL
    SELECT SQL_CALC_FOUND_ROWS
        $wpdb->posts.ID
    FROM
        $wpdb->posts
    INNER JOIN
        $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    INNER JOIN
        $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
    INNER JOIN
        $wpdb->postmeta AS mt1 ON ($wpdb->posts.ID = mt1.post_id)
    INNER JOIN
        $wpdb->postmeta AS mt2 ON ($wpdb->posts.ID = mt2.post_id)
    WHERE
        1 = 1
    AND
        ($wpdb->term_relationships.term_taxonomy_id IN (71))
    AND
        $wpdb->posts.post_type = 'learner'
    AND
        ($wpdb->posts.post_status = 'publish')
    AND
        (($wpdb->postmeta.meta_key = 'rlp_learner_age' AND CAST($wpdb->postmeta.meta_value AS SIGNED) BETWEEN '0' AND '59')
        AND
            (mt1.meta_key = 'rlp_learner_placement_percent' AND CAST(mt1.meta_value AS SIGNED) BETWEEN '14' AND '100')
        AND
            (mt2.meta_key = 'rlp_learner_age_started_spanish' AND CAST(mt2.meta_value SIGNED) BETWEEN '0' AND '49'))
    GROUP BY
        $wpdb->posts.ID
    ORDER BY
        $wpdb->posts.ID ASC
    LIMIT 0, 20
    END_OF_SQL;
    
    // check if SQLite Integration do the job properly or no
    $engine = new PDOSQLiteDriver();
    $rewritten_query = $engine->rewrite_query($sql, 'select');
    echo 'This is the SQL statement rewritten by SQLite Integration:<br />';
    echo $rewritten_query . '<br /><br />';
    
    // do execute
    $results = $wpdb->get_results($sql);
    if ($results === false || empty($results)) {
        echo 'Nothing to display';
    } else {
        foreach ($results as $result) {
            echo 'post ID = ' . $result->ID . '<br />';
        }
    }
    ?>

    @steadybright

    Of course. I said it as last resort and to help kjmtsh as much I can.

    @kjmtsh

    You remind me the problems I faced with safe mode. The php on this server is in safe mode. Sorry I didn’t mention it but REALLY I forgot it totally.

    The sql query had a small typo.

    CAST(mt2.meta_value SIGNED) BETWEEN '0' AND '49'))

    so changed it to (I put the AS)

    CAST(mt2.meta_value AS SIGNED) BETWEEN '0' AND '49'))

    and test the query first to mysql server (with phpmyadmin) and everything did well.

    After I run the test.php file and I got only :

    PHP is set to safe mode.
    The value of the max_execution_time is set to 30.

    The script won’t be executed after this line :

    $rewritten_query = $engine->rewrite_query($sql, 'select');

    Thanks a lot for your time!

    Hi again 🙂

    Sorry to bother you again but did you found a solution ?

    I installed locally sqlite3 so now the output of your file is

    PHP is set to normal mode.
    The value of the max_execution_time is set to 60.

    but again the script won’t be executed after this line :

    $rewritten_query = $engine->rewrite_query($sql, ‘select’);

    The server won’t return an error but it keeps loading for a very long time.

    Thanks in advance

    I updated of course to the latest version of the plugin and wordpress

Viewing 15 replies - 1 through 15 (of 15 total)
  • The topic ‘Multi meta queries returns no results’ is closed to new replies.