WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#34474 closed defect (bug) (invalid)

sql error with wp_query and posts_per_page param

Reported by: nicmare Owned by:
Milestone: Priority: normal
Severity: normal Version: 4.3.1
Component: Query Keywords:
Focuses: Cc:
PR Number:

Description

i have a simple query array:

query_posts( $args );

print_r of $args:

Array
(
    [post_type] => product
    [funktion] => gerauschpegelanzeige
    [error] => 
    [m] => 
    [p] => 0
    [post_parent] => 
    [subpost] => 
    [subpost_id] => 
    [attachment] => 
    [attachment_id] => 0
    [name] => 
    [static] => 
    [pagename] => 
    [page_id] => 0
    [second] => 
    [minute] => 
    [hour] => 
    [day] => 0
    [monthnum] => 0
    [year] => 0
    [w] => 0
    [category_name] => 
    [tag] => 
    [cat] => 
    [tag_id] => 
    [author] => 
    [author_name] => 
    [feed] => 
    [tb] => 
    [paged] => 0
    [comments_popup] => 
    [meta_key] => 
    [meta_value] => 
    [preview] => 
    [s] => 
    [sentence] => 
    [fields] => 
    [menu_order] => 
    [category__in] => Array
        (
        )

    [category__not_in] => Array
        (
        )

    [category__and] => Array
        (
        )

    [post__in] => Array
        (
        )

    [post__not_in] => Array
        (
        )

    [tag__in] => Array
        (
        )

    [tag__not_in] => Array
        (
        )

    [tag__and] => Array
        (
        )

    [tag_slug__in] => Array
        (
        )

    [tag_slug__and] => Array
        (
        )

    [post_parent__in] => Array
        (
        )

    [post_parent__not_in] => Array
        (
        )

    [author__in] => Array
        (
        )

    [author__not_in] => Array
        (
        )

    [ignore_sticky_posts] => 
    [suppress_filters] => 
    [cache_results] => 1
    [update_post_term_cache] => 1
    [update_post_meta_cache] => 1
    [posts_per_page] => -1
    [nopaging] => 
    [comments_per_page] => 50
    [no_found_rows] => 
    [order] => DESC
    [meta_query] => Array
        (
            [0] => Array
                (
                    [key] => gerauschpegelanzeige
                    [value] => 1
                )

        )

)

for some reason i get this error:
WordPress-Datenbank-Fehler: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 3]
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'gerauschpegelanzeige' AND CAST(wp_postmeta.meta_value AS CHAR) = '1' ) ) AND wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, -1

when i change "posts_per_page" to a high number (i.e. 100), then it works.

Change History (3)

#1 @boonebgorges
4 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to invalid
  • Status changed from new to closed

Hi nicmare - Thanks for the ticket, and welcome to WordPress Trac!

It looks like you're confusing WP_Query by passing this enormous $args array. Typically, if you want to query for all posts matching a meta query (which I think is what you're trying to do), you'll do the following:

$q = new WP_Query( array(
    'post_type' => 'product',
    'posts_per_page' => -1,
    'meta_query' => $meta_query,
) );

WP_Query then calculates the rest of the necessary query vars, based on the explicit ones you've passed.

What seems to be breaking the query in your case is that you're passing 'nopaging' => false along with 'posts_per_page' => -1. Generally, if WP_Query sees 'posts_per_page' => -1, it'll set nopaging to true.

I don't think there's really anything we should do in WP_Query to account for this case, where you've passed contradictory parameters. (How would we decide which one you "really" meant?)

Thanks again for the report!

#2 @nicmare
4 years ago

Alright. Thanks boonebgorges. this solves the issue. but just to let you know… i read your codex and used that snippet https://codex.wordpress.org/Function_Reference/query_posts#Preserving_Existing_Query_Parameters
so i merged my $args with the existing one on an archive page. therefore the query is that big (i just added meta_query and posts_per_page)

#3 @boonebgorges
4 years ago

Yeah, there's also a big warning at the top of that codex page that says you should almost always avoid using query_posts() in plugins and themes :) I strongly recommend that you use a proper WP_Query for your purposes (or the 'pre_get_posts' filter). If you must use query_posts() in the way you've described, make sure you unset( $args['nopaging'] ) or $args['nopaging'] = true before passing them to query_posts(). This'll ensure that your arguments are internally consistent.

Note: See TracTickets for help on using tickets.