WP_Query IN operator has limits

[hajakutbudeen]

Hello there,

Recently we found one issue, we display products using woo-commerce short code, we use ids params [products ids=] to pass all ids in string format its about 19000 characters. so when we pass args into WP_Query its generate SQL query for that for

ex:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_wc_product_meta_lookup wc_product_meta_lookup ON wp_posts.ID = wc_product_meta_lookup.product_id WHERE 1=1 AND wp_posts.ID IN (111,222,333,444,555,666......19000) etc..

IN operator has limits i think, we exceed that limit due to that WP_Query response empty. when we run that query in Database its works fine but same query in class-wp-query.php file.
$this->posts = $wpdb->get_col( $this->request );
not working.

please advise

[peterwilsoncc]

Hi @hajakutbudeen and welcome to trac.

I'm not sure how many posts are in the 19,000 character string but even presuming six character post IDs (7 characters with a comma), it would be approximately 2,700.

WordPress is not designed to query that number of posts a single request (no CMS is), nor is using post IDs the recommend approach to return all posts from a particular post_type.

For assistance using the shortcode, I recommend you ask in the ​Woocommerce support forum.

I suspect the number of posts queried is causing WordPress to run out of memory somewhere so I am going to recommend this ticket be closed but will ask another person to review it before doing so.

[hajakutbudeen]

Hey @peterwilsoncc,

Thanks for your replay. Really appreciated.

Example Shortcode:
$id_list = array(2321,2232,3333......19517);
[products ids='".implode(',',$id_list)."' columns="3" limit="9" pagination="true" prdctfltr="yes"]

STAGE 1 - Create args pass into WP_QUERY:
file name: woocommerce/include/shotcodes/class-wc-shortcode-products.php
function name: get_query_results()
code: $query = new WP_Query( $this->query_args );

STAGE 2 - Create SQL query pass into $wpdb->get_col:
file name: wp-includes/class-wp-query.php
function name: get_posts()
code: $this->posts = $wpdb->get_col( $this->request );

Example $this->request:
"SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_wc_product_meta_lookup wc_product_meta_lookup ON wp_posts.ID = wc_product_meta_lookup.product_id WHERE 1=1 AND wp_posts.ID IN (...............................LIST OF IDS.............................) AND ( \n wp_posts.ID NOT IN (\n\t\t\t\tSELECT object_id\n\t\t\t\tFROM wp_term_relationships\n\t\t\t\tWHERE term_taxonomy_id IN (9)\n\t\t\t) \n AND \n wp_posts.ID NOT IN (\n\t\t\t\tSELECT object_id\n\t\t\t\tFROM wp_term_relationships\n\t\t\t\tWHERE term_taxonomy_id IN (7)\n\t\t\t) \n AND \n wp_posts.ID NOT IN (\n\t\t\t\tSELECT object_id\n\t\t\t\tFROM wp_term_relationships\n\t\t\t\tWHERE term_taxonomy_id IN (9)\n\t\t\t) \n AND \n wp_posts.ID NOT IN (\n\t\t\t\tSELECT object_id\n\t\t\t\tFROM wp_term_relationships\n\t\t\t\tWHERE term_taxonomy_id IN (7)\n\t\t\t)\n) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wc_product_meta_lookup.min_price ASC, wc_product_meta_lookup.product_id ASC LIMIT 0, 12"

IDS count:
i paste all ids into MS-Word check character count

If i remove around 3000+ ids from id list then it work. I checked already there's no duplicate ids in that list. I dig into that then i found this,
​https://stackoverflow.com/questions/4275640/mysql-in-condition-limit

Please advise, Thank you

[SergeyBiryukov]

Replying to hajakutbudeen:

If i remove around 3000+ ids from id list then it work. I checked already there's no duplicate ids in that list. I dig into that then i found this,
​https://stackoverflow.com/questions/4275640/mysql-in-condition-limit

As noted in that discussion, you may be running into max_allowed_packet MySQL value limit on your environment:

The number of values in the IN list is only limited by the ​max_allowed_packet value.

I would suggest rewriting the affected code to query the posts in some other way, without passing around a few thousands of IDs.

[hajakutbudeen]

Replying to SergeyBiryukov:

Yes i need your team support for that, it will if use SQL query instead of ids, its there any way i can pass sql query into ids param ? or any other suggestion please ?

[SergeyBiryukov]

Please note that this Trac is used for enhancements and bug reporting for the WordPress core software, not for individual support questions.

I would suggest trying the support forums to get help with your site: ​https://wordpress.org/support/forums/.

For assistance with the [products] shortcode, I would recommend the ​WooCommerce support forum.

[johnbillion]

I'm going to close this off as this is such an extraordinary use case of this parameter it just isn't reasonable to spend time either investigating or addressing the problem. As Sergey mentioned above, your time would be better spent rearchitecting your approach.

Best of luck!

[hajakutbudeen]

Hello @SergeyBiryukov & @johnbillion,

Thanks for your time and response.
I fix that issue. I pass SQL Query instead of ids
Its working fine now thank you.