WordPress.org

Ready to get started?Download WordPress

Forums

Multiple plugins messing up SQL query (3 posts)

  1. CptDondo
    Member
    Posted 9 years ago #

    I've been trying to figure out how to get both private categories and front page categories to play nice... And what I am seeing has me baffled.

    As far as I can figure out the code, each plugin concatenates its own stuff to the SQL query. So far so good - except that each plugin appears to use the same bit of code - like this:

    $query . ' LEFT JOIN ' . $table_prefix .
    'post2cat ON (' . $table_prefix . 'posts.ID = ' .
    $table_prefix . 'post2cat.post_id) '

    If you have two plugins, you get a double LEFT JOIN query like this:

    SELECT COUNT(ID) FROM wp_posts /*index*/ LEFT JOIN wp_post2cat ON wp_posts.ID = wp_post2cat.post_id LEFT JOIN wp_post2cat ON (wp_posts.ID = wp_post2cat.post_id) WHERE 1=1 AND post_date_gmt <= '2005-08-16 13:27:59' AND (post_status = "publish" OR post_author = 1 AND post_status != 'draft' AND post_status != 'static') AND category_id <> 4 AND category_id <> 5

    which pukes.... SQL must reformat that query before spewing forth the error, since the concat statement actually results in something like this:

    SELECT COUNT(ID) FROM wp_posts /*index*/ LEFT JOIN wp_post2cat ON wp_posts.ID = wp_post2cat.post_id WHERE 1=1 AND post_date_gmt <= '2005-08-16 13:27:59' AND (post_status = "publish" OR post_author = 1 AND post_status != 'draft' AND post_status != 'static') LEFT JOIN wp_post2cat ON (wp_posts.ID = wp_post2cat.post_id) WHERE category_id <> 4 AND category_id <> 5

    It seems to me that instead of a simple '.=' there should be a wp_join($new_query) that would check the content of the join statement and correctly add the new search requirements, no?

    If someone is willing to check my SQL syntax, I will make a stab at writing that function....

  2. carthik
    Member
    Posted 9 years ago #

    Chuck the two plugins, after using them both to create the one super plugin that you need. Works all the time!

  3. CptDondo
    Member
    Posted 9 years ago #

    :-) started down that road. It's a pain... Neither plugin is simple....

    So I hit on a simpler sol,ution - add one line to the ecg plugin:

    function ecg_cat_join($query) {
    global $table_prefix;
    /****** new line ******/
    if (strstr($query,'JOIN')) { return $query ;}
    $newquery = $query . ' LEFT JOIN ' . $table_prefix .
    'post2cat ON (' . $table_prefix . 'posts.ID = ' .
    $table_prefix . 'post2cat.post_id) ';
    return $newquery;
    }

    You also have to load the ecg plugin after the private categories plugin....

    But it still seems to me that there should be a standard wp-join function to make sure the plugins create a valid SQL query.....

    --Yan

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.