WordPress.org

Forums

Posts 2 Posts
error Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS' (10 posts)

  1. maorb
    Member
    Posted 2 years ago #

    Hi,

    I have a CPT named question and and connection named q_to_q which connects question to question.

    In my home.php I would like to display some questions and their related questions.

    When I try to use the function p2p_type I get a sql error to the errorlog.

    (This is partial code, just with the p2p_type function usage, without looping it).
    If I remvoe the p2p_type function - there are no errors.

    <?php
    $args = array(	'post_type' => 'question',
    			  'posts_per_page' => 5,
    			  'order' => 'DESC',
    		);
    $questions = new WP_Query($args);
    p2p_type( 'q_to_q' )->each_connected( $questions,array(),'ques_var' );  
    
    if ($questions->have_posts()) :
    	while ($questions->have_posts()) : $questions->the_post(); ?>
    
    	<article id="post-<?php the_ID(); ?>">
    	<h2><a href="<?php echo get_permalink($questions->post->ID) ?>"><?php echo get_the_title($questions->post->ID); ?></a></h2>
    	</article>
    
    <?php endwhile;
    else :
    endif;
    ?>

    But when I just add the p2p_type function, this is what I get in the errorlog:

    WordPress database error Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS' for query SELECT   tcmb_posts.*, tcmb_p2p.* FROM tcmb_posts  INNER JOIN tcmb_p2p WHERE 1=1  AND tcmb_posts.post_type IN ('question') AND (tcmb_posts.post_status = 'publish' OR tcmb_posts.post_author = 1 AND tcmb_posts.post_status = 'private') AND (tcmb_p2p.p2p_type = 'q_to_q' AND (\n\t\t\t\t\t(tcmb_posts.ID = tcmb_p2p.p2p_to AND tcmb_p2p.p2p_from IN (SELECT SQL_CALC_FOUND_ROWS  tcmb_posts.ID FROM tcmb_posts  WHERE 1=1  AND tcmb_posts.ID IN (22) AND tcmb_posts.post_type IN ('question') AND (tcmb_posts.post_status = 'publish' OR tcmb_posts.post_author = 1 AND tcmb_posts.post_status = 'private')  ORDER BY tcmb_posts.post_date DESC LIMIT 0, 1)) OR\n\t\t\t\t\t(tcmb_posts.ID = tcmb_p2p.p2p_from AND tcmb_p2p.p2p_to IN (SELECT SQL_CALC_FOUND_ROWS  tcmb_posts.ID FROM tcmb_posts  WHERE 1=1  AND tcmb_posts.ID IN (22) AND tcmb_posts.post_type IN ('question') AND (tcmb_posts.post_status = 'publish' OR tcmb_posts.post_author = 1 AND tcmb_posts.post_status = 'private')  ORDER BY tcmb_posts.post_date DESC LIMIT 0, 1))\n\t\t\t\t))  ORDER BY tcmb_posts.post_date DESC  made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/gisha_mediaproduct/home.php'), P2P_Connection_Type->each_connected, P2P_Directed_Connection_Type->get_connected, P2P_Directed_Connection_Type->abstract_query, P2P_Side_Post->do_query, WP_Query->__construct, WP_Query->query, WP_Query->get_posts, referer: http://gisha-qanda.local/question/22

    I already have another site using sort of the same logic, and it works, I just can't figure out why I get this error here.
    Maybe the error caused because the connection is between the same post type? (question to question)? I'd really appreciate your help solving this one, what am I missing here?
    Thanks

    http://wordpress.org/extend/plugins/posts-to-posts/

  2. maorb
    Member
    Posted 2 years ago #

    I also tried the other approach, without p2p_type (since in my home I show only 1 post so I don't mind using the "slow way")

    The code in this situation is this:

    <?php
    $args = array(	'post_type' => 'question',
    		  'posts_per_page' => 1,
    		  'order' => 'DESC',
    	);
    $questions = new WP_Query($args);
    
    if ($questions->have_posts()) :
    while ($questions->have_posts()) : $questions->the_post(); ?>
    
    <article id="post-<?php the_ID(); ?>">
    	<h2><a href="<?php echo get_permalink($questions->post->ID) ?>"><?php echo get_the_title($questions->post->ID); ?></a></h2>
    </article>
    
    <?php echo tc_list_p2p_related($questions->post->ID, 'q_to_q', 3);?>
    
    <?php endwhile;
    else :
    endif;

    And the function tc_list_p2p_related is this:

    function tc_list_p2p_related($post_id, $connected_type, $limit = 2){
    
    	$query_args = array(
    	    'connected_type' => $connected_type,
    	    'connected_items' => intval($post_id),
    		'posts_per_page' => $limit,
    		'orderby' => 'rand'
    
    	);
    
    	$related = new WP_Query($query_args);	
    
    	if($related->have_posts()):	
    
    		while($related->have_posts()):
    			$related->the_post(); ?>
    			<a href="<?php the_permalink();?>"><?php the_title();?></a>,
      <?php endwhile;		
    
    	endif;
    	wp_reset_postdata();
    
    	return true;
    }

    But I also get database error:

    WordPress database error Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS' for query SELECT SQL_CALC_FOUND_ROWS  tcmb_posts.*, tcmb_p2p.* FROM tcmb_posts  INNER JOIN tcmb_p2p WHERE 1=1  AND tcmb_posts.post_type IN ('question') AND (tcmb_posts.post_status = 'publish' OR tcmb_posts.post_author = 1 AND tcmb_posts.post_status = 'private') AND (tcmb_p2p.p2p_type = 'q_to_q' AND tcmb_posts.ID = tcmb_p2p.p2p_to AND tcmb_p2p.p2p_from IN (SELECT SQL_CALC_FOUND_ROWS  tcmb_posts.ID FROM tcmb_posts  WHERE 1=1  AND tcmb_posts.ID IN (22) AND tcmb_posts.post_type IN ('question') AND (tcmb_posts.post_status = 'publish' OR tcmb_posts.post_author = 1 AND tcmb_posts.post_status = 'private')  ORDER BY tcmb_posts.post_date DESC LIMIT 0, 1))  ORDER BY RAND() DESC LIMIT 0, 1 made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/gisha_mediaproduct/home.php'), tc_list_p2p_related, WP_Query->__construct, WP_Query->query, WP_Query->get_posts, referer: http://gisha-qanda.local/question/22

    The same code above is working well in my single.php for single question postype, showing the connected posts of type question. I can't figure out why this is not working in home.php

    Thanks

  3. maorb
    Member
    Posted 2 years ago #

    I tried to downgrade p2p, but in v1.4.x the same error occurs.
    In 1.3.x it doesn't work at all (no errors but no found related posts returned).
    Again, in the latest p2p 1.5.2 it works well in single.php, but the error occurs on homepage of site (using home.php)
    I use the latest wp3.5.1

    Is there any idea what could have gone wrong with this?
    Thanks

  4. maorb
    Member
    Posted 2 years ago #

    @scribu,
    It appears that maybe using SQL_CALC_FOUND_ROWS in a subquery generates the error.
    I found these references that might be helpful to fix the issue:
    http://stackoverflow.com/questions/7314392/using-sql-calc-found-rows-in-a-subquery
    http://stackoverflow.com/questions/6083322/sql-calc-found-rows-return-error

  5. scribu
    Member
    Plugin Author

    Posted 2 years ago #

    Yeah, I was under the impression that P2P took care of removing SQL_CALC_FOUND_ROWS from subqueries, but apparently not.

    Opened a ticket: https://github.com/scribu/wp-posts-to-posts/issues/353

  6. maorb
    Member
    Posted 2 years ago #

    Thanks. Could you please estimate how much time it will take to fix this bug?

  7. scribu
    Member
    Plugin Author

    Posted 2 years ago #

  8. maorb
    Member
    Posted 2 years ago #

    :-) Many 10x.

  9. scribu
    Member
    Plugin Author

    Posted 2 years ago #

    I can't reproduce the error. Even if I set 'posts_per_page' => 1, The subquery doesn't contain SQL_CALC_FOUND_ROWS.

    Go through the troubleshooting flowchart to make sure that it's not caused by some other code.

  10. Once I Was A Developer
    Member
    Posted 2 years ago #

    @maorb:

    I had same issue, but in different context.

    Try to see if your code of in any other plugin code there's a filter on 'pre_get_posts'.

    In my case the filter on pre_get_posts just set - for each query of some custom_post_type - the 'posts_per_page' parameter and that, for some reason I didn't dig, broke the SQL query with that SQL_CALC_FOUND_ROWS related error.

    My fix was to avoid that the needed filter had effects on p2p queries, adding some "if" in the filter's code (... the BAD code was mine, so it's been easy to fix ;-) )

Topic Closed

This topic has been closed to new replies.

About this Plugin

  • Posts 2 Posts
  • Frequently Asked Questions
  • Support Threads
  • Reviews

About this Topic

Tags

No tags yet.