WordPress.org

Ready to get started?Download WordPress

Forums

Exports and Reports
[resolved] Complex query (with subqueries). Bug? (2 posts)

  1. lthevenet
    Member
    Posted 2 years ago #

    First, congrats. Fantastic plugin. Very good to provide clients with custom backend stats and reports quickly.

    I had to execute the following query:

    SELECT
     t.name AS name,
     (
      SELECT count(*) AS reviews
      FROM mr_term_taxonomy tt2
       JOIN mr_term_relationships tr
        ON (tt2.term_taxonomy_id = tr.term_taxonomy_id)
       JOIN mr_posts p
        ON (tr.object_id = p.ID)
       JOIN mr_comments c
        ON (p.ID = c.comment_post_ID)
      WHERE c.comment_approved = 1
       AND c.comment_parent = 0
       AND p.post_type = 'module'
       AND tt2.parent = t.term_id
      ) AS reviews
    FROM mr_terms t
     JOIN mr_term_taxonomy tt
      ON (t.term_id = tt.term_id)
    WHERE tt.parent = (SELECT t2.term_id
                      FROM mr_terms t2
                      WHERE t2.name = 'NUS')
    GROUP BY name

    And while not getting any results in the reports itself (but getting the expected results in phpmyadmin at the same time), i went to check the server log and had the following PHP error:

    [05-Dec-2011 03:15:11] WordPress database error Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS' for query SELECT SQL_CALC_FOUND_ROWS t.name AS name, ( SELECT SQL_CALC_FOUND_ROWS count(*) AS reviews FROM mr_term_taxonomy tt2 JOIN mr_term_relationships tr ON (tt2.term_taxonomy_id = tr.term_taxonomy_id) JOIN mr_posts p ON (tr.object_id = p.ID) JOIN mr_comments c ON (p.ID = c.comment_post_ID) WHERE c.comment_approved = 1 AND c.comment_parent = 0 AND p.post_type = 'module' AND tt2.parent = t.term_id ) AS reviews FROM mr_terms t JOIN mr_term_taxonomy tt ON (t.term_id = tt.term_id) WHERE tt.parent = (SELECT t2.term_id FROM mr_terms t2 WHERE t2.name = 'NUS') GROUP BY name ORDER BY 1 LIMIT 0,25 made by do_action, call_user_func_array, exports_reports_view, WP_Admin_UI->go, WP_Admin_UI->manage, WP_Admin_UI->get_data

    I solved the issue by hacking Admin.class.php in the wp-admin-ui/ folder of the plugin (changing the line 1696 to $calc_found_sql = ''; /* 'SQL_CALC_FOUND_ROWS'; */ but i am worried about the client updating the plugin on its side later on and breaking the thing. Do you guys have any idea what the issue is?

    http://wordpress.org/extend/plugins/exports-and-reports/

  2. Scott Kingsley Clark
    Member
    Plugin Author

    Posted 1 year ago #

    Because there are more than one SELECT, it's messing with the way that it includes the SQL_CALC_FOUND_ROWS portion. I've fixed this in 0.5.2

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic