Ask your WordPress questions! Pay money and get answers fast! (more info)

Exclude scheduled sale items from Woocommerce category sale page

  • SOLVED

Hi, I've created a sale category page in WooCommerce. I created a custom archive-product page, pulled from a custom taxonomy-product-cat page. The code I'm using to call the sale products is:

<?php
$args = array(
'posts_per_page' => 99,
'post_status' => 'publish',
'post_type' => 'product',
'meta_query' => array(
'relation' => 'OR',
array( // Simple products type
'key' => '_sale_price',
'value' => 0,
'compare' => '>',
'type' => 'numeric'
),
array( // Variable products type
'key' => '_min_variation_sale_price',
'value' => 0,
'compare' => '>',
'type' => 'numeric'
)

)
);

query_posts( $args );
?>

Placed before <?php if ( have_posts() ) : ?>

All is fine except it is pulling in future scheduled sale items that are not yet on sale. How do I filter these out? Additionally, pagination produces a 404 if I limit posts_per_page.

Note, I did try using WC sale shortcode but in a category it places the content after the loop and loses category formatting.

Answers (2)

2016-03-14

Reigel Gallarde answers:

is using sale_products shortcode not an option? I mean you could just do [sale_products columns="3" per_page="12"] in a page... or echo do_shortcode('[sale_products columns="3" per_page="12"]'); in a php file...


Reigel Gallarde comments:

Oppss... I have not read your note.. too quick to give answer...


B comments:

no worries, it lost all formatting and seemed like even more work when I tried the shortcode, but I'm willing to try anything at this point as long as it filters out future scheduled sales.


Reigel Gallarde comments:

What do you mean by it's losing the formats?

Rempty's answer by the ways is what the shortcode sale_products is using, so it's the correct args..


B comments:

Because the shortcode was in the category description, it put it after the loop and it went to one column etc. It was pulling in different css. I'm trying Rempty's. I had tried that on my own, but am trying again in case I missed something.


Reigel Gallarde comments:

one question though, you are creating this product category "sale"? and viewing the page like site.com/product-category/sale/ ?


B comments:

Yes, as I want it to be integrated into the category menu on sidenav and work like any other category for my client


B comments:

PS... when I add the shortcode to a page, I get the same result as my initial code. The sale products, plus the scheduled sale items, however pagination works.


Reigel Gallarde comments:

if that's the case... then might as well just use pre_get_posts... and change the query when you are viewing a sale category page...

function custom_search_filter($q) {
if (isset($q->query['product_cat']) && ($q->query['product_cat'] == 'sale')) {
$q->set('post__in', array_merge( array( 0 ), wc_get_product_ids_on_sale() ) );
$q->set('meta_query', WC()->query->get_meta_query() );
$q->set('product_cat', '' );
}
return $q;
}


add_filter( 'pre_get_posts', 'custom_search_filter', 999 );


with this you don't have to create another archive page... just make sure you have a slug "sale" on the product category.... then you can view the result on site.com/product-category/sale/


Reigel Gallarde comments:

paste the code above in functions.php


Reigel Gallarde comments:

let me know if this works for you...


B comments:

This is much easier, thank you. Unfortunately I'm getting the same three products included that are not on sale. Crazy. I've gone into each individually and made sure there are no characters (even spaces) in the sale price etc. There is nothing in the scheduled date fields. I'm completely stumped.


Reigel Gallarde comments:

I have tried this on my localhost and works fine... I have created sale products... few without schedule and some with schedule... it works as expected.. do you have this on a live site? maybe a link for us to see?


B comments:

Thanks for all the effort Reigel, I do have it on a live site:
http://www.rocknrolloutfitters.com/product-category/on-sale/
(I changed your code to on-sale btw as that was the slug)
Items 974, 953, and 969 are not on sale, nor are they scheduled. I'm trying to find a common thread in them that could be causing the error.


Reigel Gallarde comments:

I can see them as sold.. how are you coding this? we can filter this out if we know how this was coded? I mean how it got the sold status?


B comments:

two of them are sold/out of stock, the other is not. It's done with the inventory tracking in WooCommerce. When the in stock is zero, it says sold (instead of out of stock). Many of these items are made one of a kind as they are custom.


Reigel Gallarde comments:

ok... let's edit the function... please use this...

function custom_search_filter($q) {
if ( isset($q->query['product_cat']) && ($q->query['product_cat'] == 'sale')) {
$q->set('post__in', array_merge( array( 0 ), wc_get_product_ids_on_sale() ) );
$meta_query = WC()->query->get_meta_query();
$meta_query[] = array(
'key' => '_stock_status',
'value' => 'instock',
'compare' => '='
);
$q->set('meta_query', $meta_query );
$q->set('product_cat', '' );
}
return $q;
}


B comments:

that produces "No products found which match your selection. "


Reigel Gallarde comments:

sale to on-sale I guess


B comments:

whoops, helps if I add add_filter( 'pre_get_posts', 'custom_search_filter', 999 );, sorry


B comments:

okay, it now just has the one product that is not on sale. Not sure what to do. Filter just that one out and watch it in the future to see if other products do this? I can't find any links between those three products. All look setup correctly. They were published in different months, etc.


Reigel Gallarde comments:

I can't find a reason why that product is there... I can confirm that it's not on sale should not be there...

can you try this out...

under $q->set('product_cat', '' ); add this, print_r($q->query_vars);

then visit the on-sale page... then give me the result of what it prints...


B comments:

it prints:

Array ( [product_cat] => [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] => [title] => [fields] => [menu_order] => [category__in] => Array ( ) [category__not_in] => Array ( ) [category__and] => Array ( ) [post__in] => Array ( [0] => 0 [1] => 8 [2] => 15 [3] => 204 [4] => 244 [5] => 284 [6] => 285 [7] => 286 [8] => 287 [9] => 293 [10] => 427 [11] => 654 [12] => 655 [13] => 656 [14] => 657 [15] => 658 [16] => 659 [17] => 807 [18] => 808 [19] => 809 [20] => 810 [21] => 811 [22] => 819 [23] => 820 [24] => 821 [25] => 822 [26] => 823 [27] => 847 [28] => 865 [29] => 866 [30] => 886 [31] => 887 [32] => 888 [33] => 889 [34] => 894 [35] => 895 [36] => 896 [37] => 897 [38] => 905 [39] => 917 [40] => 925 [41] => 928 [42] => 940 [43] => 941 [44] => 942 [45] => 943 [46] => 1000 [47] => 1206 [48] => 1207 [49] => 1345 [50] => 1346 [51] => 1347 [52] => 1348 [53] => 1349 [54] => 1350 [55] => 1389 [56] => 1390 [57] => 1391 [58] => 1392 [59] => 1393 [60] => 1394 [61] => 1397 [62] => 1398 [63] => 1399 [64] => 1400 [65] => 1401 [66] => 1402 [67] => 1516 [68] => 1517 [69] => 1518 [70] => 1519 [71] => 1520 [72] => 1521 [73] => 77 [74] => 243 [75] => 281 [76] => 653 [77] => 806 [78] => 818 [79] => 885 [80] => 893 [81] => 939 [82] => 999 [83] => 1337 [84] => 1388 [85] => 1396 [86] => 1515 ) [post__not_in] => Array ( ) [post_name__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 ( ) [orderby] => menu_order title [order] => ASC [meta_query] => Array ( [0] => Array ( [key] => _visibility [value] => Array ( [0] => visible [1] => catalog ) [compare] => IN ) [1] => Array ( [key] => _stock_status [value] => instock [compare] => = ) ) [posts_per_page] => 24 [wc_query] => product_query )


Reigel Gallarde comments:

then the query is correct... as you can see there's no 969 in post__in... maybe some other plugins are also using pre_get_posts filter... try increasing the number 999 in add_filter( 'pre_get_posts', 'custom_search_filter', 999 ); to something bigger... try 5 digit 99999...


B comments:

it's still there with the 99999... baffled. Is there an easy way to filter out product ID 653 (this is product 969). Then I'll have to keep an eye out and/or hope theme or plugin updates solve the issue. It's Barberry theme with no significant customization.


Reigel Gallarde comments:

now try 1 instead of 99999... I'm still looking for a fix... currently the only thing I could think is this priority number....


Reigel Gallarde comments:

for a quick solution we could try to remove the id.. like this...

replace
$q->set('post__in', array_merge( array( 0 ), wc_get_product_ids_on_sale() ) );

with
$ids = wc_get_product_ids_on_sale();
foreach($ids as $key => $id ) {
if ($id == 653) {
unset($ids[ $key ]);
}
}
$q->set('post__in', array_merge( array( 0 ), $ids ) );


this will remove 653


B comments:

worked like a charm Reigel. Thank you so very much. I know it's a bit of a hack for now, but I will continue to try to track down the actual issue in the future. I'm glad I found WPQuestions and your help. Goodnight.


Reigel Gallarde comments:

don't forget to close this question by clicking [[LINK href="http://www.wpquestions.com/question/pickAWinner/id/16446"]]Vote to award prize[[/LINK]]... and vote for me...

cheers!

2016-03-14

Rempty answers:

Maybe you can use this args

$args = array(
'posts_per_page' => 8,
'no_found_rows' => 1,
'post_status' => 'publish',
'post_type' => 'product',
'meta_query' => WC()->query->get_meta_query(),
'post__in' => array_merge( array( 0 ), wc_get_product_ids_on_sale() )
);


B comments:

This produced a different result, but still not working. I changed the "8" to 99 in order to pull all sale products. There are now 3 products on the page that aren't on sale at all. I've checked each product individually. They are all simple products and show no content in the sale price or scheduled sale fields. I do however see a simple product with a scheduled future date NOT showing up, so that's good. Progress anyway...


Rempty comments:

You can check all id products on sale with this

print_r(wc_get_product_ids_on_sale());

If not appear all the ids, check if your on sale products are good configured


B comments:

That produced a large array (86 items below). There are a total of 70 products in the store, only 19 are on sale, one of which is private. That leaves 18 on sale, which your code does produce, but it also mixes in three items that are not on sale or scheduled to be on sale. My original code also produced the 18 items, but it added scheduled items.

For reference, here is the array produced with the above:

Array ( [0] => 8 [1] => 15 [2] => 204 [3] => 244 [4] => 284 [5] => 285 [6] => 286 [7] => 287 [8] => 293 [9] => 427 [10] => 654 [11] => 655 [12] => 656 [13] => 657 [14] => 658 [15] => 659 [16] => 807 [17] => 808 [18] => 809 [19] => 810 [20] => 811 [21] => 819 [22] => 820 [23] => 821 [24] => 822 [25] => 823 [26] => 847 [27] => 865 [28] => 866 [29] => 886 [30] => 887 [31] => 888 [32] => 889 [33] => 894 [34] => 895 [35] => 896 [36] => 897 [37] => 905 [38] => 917 [39] => 925 [40] => 928 [41] => 940 [42] => 941 [43] => 942 [44] => 943 [45] => 1000 [46] => 1206 [47] => 1207 [48] => 1345 [49] => 1346 [50] => 1347 [51] => 1348 [52] => 1349 [53] => 1350 [54] => 1389 [55] => 1390 [56] => 1391 [57] => 1392 [58] => 1393 [59] => 1394 [60] => 1397 [61] => 1398 [62] => 1399 [63] => 1400 [64] => 1401 [65] => 1402 [66] => 1516 [67] => 1517 [68] => 1518 [69] => 1519 [70] => 1520 [71] => 1521 [73] => 77 [74] => 243 [79] => 281 [80] => 653 [86] => 806 [91] => 818 [96] => 885 [100] => 893 [104] => 939 [108] => 999 [111] => 1337 [117] => 1388 [123] => 1396 [129] => 1515 )


Rempty comments:

If you want use your query, there is 2 meta fields _sale_price_dates_from and _sale_price_dates_to
You must compare the date the First is > cuarrent date and the secnd is < current date.
I can write all the code now, i am writing from the phone and is late for me, maybe Reigel can help you.


B comments:

Thanks Rempty, I Googled for hours trying to find code to compare those metas but had no luck. I'm not advanced enough to figure it out myself. I failed miserably, lol. Goodnight. I appreciate your help.