Advanced query to select products on sale from a specific category and below a specific price

in this video we're going to have a look at getting the sale products only in our slider on the website so here we are on the home page and here we have our products we have products that are with or with art variation and here they appear on the home page of the website so if you wanted to build a slider getting just the sale products from your woocommerce product list let's have a look and see how that's done so i'm going to head over here into the back end um here we are with oxygen loaded and you'll see that i'm using the dynamic slider which is a composite element from [Music] oxygen builder but you don't have to do that the repeater can be applied anywhere and in this case we're just going to have a look at the query to get only the products that are on sale so we've used the advanced query so we're here in advanced and we're just going to edit those query settings and here you'll see the query the first thing we do is we establish the product type so we say post type and we establish product the next thing that we did is we then had a look at a meta query so the fields that we're looking for are meta fields that are included in the woocommerce ecosystem and we wanted to check for products that are on sale so there are there are two items here so what we're saying is it can either be this item or this item or this query we must include those products in the loop so what we did is in the first one we created a relation or then we said create an array now it's a meta query so to have a look at what's included in a meta query i head over to wp query generator and over here you can say you can see that i've selected meta query immediately i have the relation of and or or so we've said or so either of our meta queries need to be true to display the product and then we had a look at the sale price and we said if if the item has a sale price a value of naught or greater than equal to that value of zero then display the product so if i go back here to my query inside oxygen you'll see that we select the key now this key is meta value information that is part of woocommerce so you need to make sure that this is the correct value and in this case we looked at sale price then we said look at a value so we said set the value of zero and compare if the value is greater than or equal to zero then display the product providing there's a value in sale price then we have to look at the items that have variations so what we do there is we have a look at this query so i'm going to head over here and i'm going to add my second meta query which will be min variation sale price and we follow the same process where we enter the information like that now if you look at my query here you'll see that the query we have in this case we have the relation of or so it needs to be in the product category um in this case if we were going to do one for a specific category then we could get the sale products just in the category so there we would say that category and this sale price or this sale price in this in our case here we actually don't have a product category that we targeting we're just targeting any product so we just have the or section so we have the sale price and here we have the min variation sale price and if we look at the query then we have the key put in the key there then we've added the value we've set the value to zero and then we've compared and said if the value is greater than or equal to zero for a variation product or if it's true for a a normal product then display the product so we head over here to oxygen we're going to save that the query doesn't always reflect uh in the slider immediately so sometimes the best way to look at that is just to head over to the website and then you'll see that we have the slider here with those products so if we as we go through these products you'll see that we have quite a few accessories on sale here now i'm not sure if there are any other products on sale but okay and there's hoodies so let's target a specific category now so now i just want to target a specific category so in order to do that i'm going to head over to my query and i'm going to edit the query and now what i'm going to do is i now need to look up a taxonomy query a text query so i'm going to head over here into oxygen and i'm going to add a parameter and i'm going to type in tax taxonomy query so now i've created the query and now i'm going to add the value and in this case it's going to be and so we enter the relation and we say that it must also be in this category and then i'm going to add a value and in that case i'm going to go with the array and in the array i'm going to add the values to identify the categories the first thing that we're going to do is we're going to look at a taxonomy so what are we looking for and we're looking for the product underscore cat for product category the next thing i'm going to look at the field so what kind of field and we're going to look at the field let's call it a slug then we're going to have a look at the terms now the terms are in an array so we'll add one value at a time so i'm going to add hoodies and the last value that we're going to enter then will be the [Music] operator and we'll make that operator in so we're saying if the product is in this category and it's on sale then display the product so let's do that and save and now when we head over to the website and we refresh the page we should get just the hoodie that's on sale so there we have the hoodie there's only one hoodie on sale and you can see there it is only the hoodie is displayed on sale if we wanted to check for anything else on sale let's just do another one so i'm going to head over here to the shop quickly and we know that accessories has a lot of products on sale but maybe we want to have a look at something else so let's have a look at uh is anything on sale in music i don't think so and let's have a look here maybe t-shirts so this is the filter by piatnet grid and it's not really working that well so i'm just going to head back here and i'm going to go into the query and let's make it for t-shirts so if we're not sure what the value is for the slug for t-shirts the easiest way to do that is to head over into your products head over to categories look for the t-shirts category and then look at the slug that's displayed so here we are in categories and t-shirts and the slug is t-shirts so what we'll do then is we'll head over here another slug i'm going to change that to t-shirts all right t-shirts we'll close the query i'm going to save and then what i'm going to do is head back to the front end of the website and scroll down to products and you'll see that there's nothing displayed and that's probably because there are no t-shirts on sale so let's head back and i'm going to edit that query again and from t-shirts let's have a look at we know that there are a lot of accessories and we know that that's the slug save that item head back to the home page refresh and now you'll see that the accessories on sale are now in the slider and as we go through you'll see each one of these items is an accessory so that's how you can quite easily have a look at items that are for sale if you wanted to have a slightly different take on the pricing and you wanted to maybe do the pricing below a certain value so here we've said compared to the value greater or equal to zero so if i go and have a look here i see i've got items for 16 18 55 that's a bit high so i i want to make sure that it's only values below 55 so i'm going to change that compare to smaller than or equal to and just to be safe let's make that fifty dollars so i'm only going to show items that are less than fifty dollars on my home page i'm gonna save that and now when i refresh the page you'll see that the items will refresh but we won't have anything for 50 so everything that i have here now is at 18 and if i uh wanted to change the pricing even more maybe i could even say only for values that are less than or equal to 18 so that's how you can set the price for your slider on the home page and only include products that are on sale below a certain value or in reverse you could do one where the minimum value is greater than a certain minimum value so that's how you can quite easily then create a slider with the products for sale from your shop below a certain value so i hope you found that interesting thank you for watching

Channel: Bruce Young

Views: 168

Keywords: oxygen builder, advanced query, price query, sale products query

Id: 61u91FAuKb8

Length: 10min 32sec (632 seconds)

Published: Fri Dec 03 2021