Advanced Queries - price, attributes, stock status, price ascending or descending or between values

in this video we're going to have a look at some product queries that can be done using the advanced query inside oxygen so here we have a repeater that has been implemented with displaying some product information and you can see that here we have the pagination here we have the products and here in the back end we have the repeater and if i head over to the query you will see that we have an advanced query in place and in that advanced query we've just looked at the post type and we've looked at product so we'll look at some basics first and then we'll get into more advanced so the first thing that i'm going to do here is look at the posts per page so inside the drop down we look for posts per page and in this case i'm going to just put in the value of 8 and then we'll save that now when we head over to the shop page and we refresh you'll see that we are now restricted to eight products in the listing and we still have a pagination here with post per page so if you were doing a layout on the home page and you only wanted eight or four products and you didn't want the pagination to show what you can then do is head over into the query and add a parameter to hide the pagination so we enter no fund rows add value and we say true we'll save that and when you refresh the page now you'll see that that pagination is no longer visible so there you have it the pagination is gone so that's how you can set up the post per page and the pagination so let's have a look now at the sort order for the products and let's look at some basics so we'll start off with the order by so order order by and in this case the value that we'll add for order by will be title and then we're just going to add the parameter order and order looks at ascending or descending so order and we'll add the value and in this case let's look at ascending so we're going to start with the a's and head on through so let's have a look so we'll refresh that and now you'll see that the first product is the a a b and so it goes and if we want to reverse the order quite easily come in and change that to descending save and when we look at the listing now you'll see that the products now start in the reverse order so that's one way to search and that is by say title another way to do it would be by date so this would be from the date the products are added so we'll save that and once again there's no date visible but this is now being sorted by date so this is the most recent added first and that's correct the vouchers were added last heading towards the first product and quite easy then to reverse the order with date by updating this to ascending and now we'll see that the products are in reverse order so the products that are first now probably won't be visible because now we're looking at it in reverse order so that's how you can then adjust the order by date another basic query that you can use is random so r-a-n-d and in that case we don't need an the order serves no purpose so we'll take out that and now we can save and every time the page is refreshed we're going to see a different set of products so there you'll see that each time i refresh the page the random e sequence changes and we have different products loading so that's how we can look at some of the basic ones but one of the things that we might want to do is to sort by price sort by price we need to change the order by from rand in this case to meta value underscore number so that's the metavalue number and what we also need to do now is add the key so we'll add the meta key and the metakin for price is underscore price and then the other options that work the same would then be the order so now we can add the order parameter again and we can have a look at the ascending or descending so order and what we're going to do now is add the value for ascending and we'll close and save and then what we're going to do is refresh and now you'll see that the products are ordered in ascending order so from the two dollar all the way up and if we wanted to reverse that order we can come into the query head down change the ascending to descending so we'll just do that we're going to change the ascending sequence here to descending close we'll save and then what we're going to do is refresh and then you'll see that we can have a look at those prices going from the most expensive to the least expensive product if you wanted to show the products less than a certain amount or greater than a certain amount we'll head over into the query and now we're going to change the query so what we're going to do is we're going to get rid of the meta key and we're going to get rid of the metavalue number and now what we're going to do is we're going to add a parameter and this parameter is a meta query so meta query and when we add a meta query there are two values that we look at the one that we're going to look at is the relation so how does it relate to the rest of the query in this case it can be an and or an or and the second is to look at the array and the array is what makes up the argument for the query so we'll be occurring by the key and the key in this case is going to be underscore price and now we can enter the value for the key so in this case maybe we want everything less than 20 dollars and then we have the compare which is basically indicating what to do and how to compare to that value so let's go with smaller than or equal to the twenty dollar and we'll save that we'll head over to the product listing and now you'll see that only the items less than 20 dollars are displayed some will have maybe 18 to 45 because one of the variations is less than 20. so here we have anything that's less than 20 and the converse is also true if i wanted to only show products that are greater than a certain value then i would change that to greater than or equal to save head back enter and now you'll see that anything greater than 20 than 20 is displayed in the listing nothing less than 20 greater than or equal to 20. so that's how you can do the products either less than or greater than a certain value if you wanted to box the products between two values then what you would do is you would add a second value so in this case let's add 40 and now you would change or compare to between so now anything between those two values will be shown and we'll save that we'll head over to the products now and you'll see that only anything between 20 and 40 dollars is displayed and there are in fact only four items if you want to exclude any products that are out of stock from your results then we also do that with a meta query so we can do that here we can say add value and we're going to add the two values and in this case we'll set the relation to and and what we're also going to do then is set up the array and we're going to add the same three values three items here in the array so the first one that we're going to look at here in the key is going to be underscore stock underscore status so we're looking for the stock status we're then going to enter the value and the value that we're looking for in this case is in stock and then the compare value in this case we're just going to say it must be exactly the same so now only items in our store that are in stock will be listed so if we wanted to test that then let's refresh this page make sure these three items are in stock and they are and then what i'm going to do is head over to the polo i'm going to edit this product and i'm just going to take it out of stock so we'll edit here and then over here under the product information i'm going to go to inventory and i'm going to change the in stock to out of stock and we'll update that then we'll with that updated we'll view the product we'll go back to the shop that we were looking at so in this case we're looking at a special page for the shop so it's not the archive page but it's a custom page so here we are on the custom page and now you'll see that that item isn't listed if i go back into my query and i was to remove this from the inquiry then you'll see that the that the shirt is then visible again on the page so we're just going to refresh that and now you'll see that the polar is listed on the page but we only want to show people what's in stock and for that reason then we're just going to add that value again for another meta query add the two values and we're saying in the relation it must be an and and then the array we're going to add three values the first value is the key that we're going to look at and that is underscore stock underscore status and then we'll look at the value and the value in stock and then the compare in this case will be exactly the same so there we have it that's how we can check for what's in stock when it comes to a product the next thing that we're going to have a look at then is going to be product attributes so how can we list products by their attribute so [Music] that is also a taxonomy query that is not a meta value query that will be a taxonomy query so what i'm going to do is head over into my query and i'm going to remove the bracketing here and what i'm going to say is that i'm going to remove that item and i'm just going to say a value of 0 and i'm going to just change that to so any products with a value greater than 0 can be displayed i'm going to save that and now when i head over to the website you'll see that the a lot more products all the products are visible only those that are in stock are visible and i've taken away the post per page so we can just see all the products now what we want to do is filter by color so what i'm going to do is head over into my query and attributes are considered or looked into as taxonomy so i'm going to leave the pricing query and the stock status and i'm going to add another parameter here and i'm going to call that a taxonomy query now the taxonomy query is similar to the meta query it also has a relation so what is the relation to the rest of the query and in this case it will be and and then we set up the array now the array here in this case has slightly different values because it's a taxonomy query so the first thing that we're going to do is look at the taxonomy now any attribute will be saved as p a underscore and let's go color then we're gonna have a look at the uh field and in this case the field will be a we'll look at the slug we're going to add another parameter then the field then we're going to look at the terms and the terms in this case we're going to add the value of green and then the last value that we're going to add here is going to be the operator and that is how does that relate and it needs to be in this particular query now we'll save and enter and now you'll see that all the products that are green are listed so these items all have something in green if i wanted to expand that and if i wanted to add another color i could add another color here and let's say i want to add red and green so there we have red and green save and now when i refresh the page you'll see that anything with red and green will be listed so those items all have something that is in red or green and i could even go one step further and i can just keep adding the colors and i could say let's include blue as well so that's the taxonomy query in relation to the the color so we'll just refresh that again and let's say i also had a second taxonomy now and the second taxonomy was size so now we're going to add more to our taxonomy query but now also look at the size so we again to add a value we're going to add two values once again we're going to have a look at the first one being the relation and the relation again will be and we could go either or but we're going to go and and then we're going to have the array and in that array we we're going to have the four values again and the first one that we're going to have a look at then is the taxonomy and the taxonomy in this case will be pa underscore size the next thing that we're going to look at then is the field and in this case we'll also say the slug the next item we're going to look at then will be the terms and the terms in this case will be let's start with small and the final item then will be the operator and once again the operator will be in so if the term is in the listing then display the product so we'll close that save and now you'll see that all the products displayed will be the products that have a color and also have the size small in their listing so there's only one item that matches all the parameters let's have a look at adjusting that query again and now what i'm going to do i'm going to add another color here and the color that i'm going to add will be yellow and i'm also going to add another value here and i'm going to add large so we've added two parameters there and let's have a look and see if we have any products that match those parameters enter and now you'll see that the cap has appeared and the cap is large with the color of yellow and then we have the v-neck here and the color and size match the query so that's how we can then build the query on the product attributes if i wanted to go one step further now and include specific categories what i can do then is add another taxonomy query and this in this taxonomy query we're going to look at the category so we're going to look at the relation first of all and the relation in this case will be and again then i'm going to have a look at an array and we're going to add the value once again there'll be four values and the first value is the taxonomy so the taxonomy in this case will be product underscore cat but it could be also be product tag or it could be an attribute so it's the product category then the field that we're going to look at will be a slug it could also be the term id if you wanted the to work by the category id then we're going to have a look at the terms and in this case let's include accessories and the second item was a t-shirt so then instead of adding t-shirts let's add um music the next item that we're going to be looking at is the operator in this case it will be in so anything that's in those categories will be displayed i head over to the product query and i refresh and now only the cap is visible because the other item wasn't included in the categories i'm going to head back now to the query and what i'm going to do is take out the designation here for size well that seems to be holding things up or i'm going to add another value here for the t-shirt other t-shirts or t-shirts let's see how t-shirt does it and then we're going to refresh right it wasn't t-shirt it was t-shirt so we'll edit that query again and we'll just add the s and now we're going to have a look at t-shirts included there the t-shirts are back so that's how easy it is then to do the query looking at the taxonomies and also we must remember that product attributes are also considered a taxonomy so there we looked at the color we looked at the size and then here we looked at the standard categories to check that the items were in fact in one of those categories if you wanted to look at everything that's not in one of those categories then you could also create a query and go not in so i don't want anything that's in that sort nothing in that one and i don't want anything in those colors right so now we're going to reverse the query so everything that we will everything except what we were looking at here and we'll enter and now you'll see that we have a lot more products in our listing and it's anything that doesn't conform with the values in the query so there you can do and either all so the nice thing here is that if i wanted to do a product listing and i didn't want any products from a specific category then i could do that and say don't show me ex so we're not look i don't want accessories i don't want music i don't want shirts [Music] and then you'll see that we have that in our filter here i could even go one step further then so for example i don't want this pennant then i could add the value simply add that decor and now you'll see that the decor is no longer available in the shop or in this listing so there you have it so that's how we can either include items in or exclude items from our product listing we've had a look at how we could filter by price so here we have the pricing and once again if i was to change the meta query here for example the price so anything greater than 0 i could also come in say anything greater than 50 would that apply yes that would apply and then that would then affect the the listing in the shop again anything over 50 dollars that meets all the other parameters will also be shown so that just shows the one product if i wanted to change that i could also use the or parameter you'll see that our relation has been end and and now if we were going to make it an or so what i could do is i could say then that show all the products that are have a price greater than 50 or the products that are in stock so now we'll definitely get the products that are over 50 and we should get a whole lot more products showing the products that are in stock so there we have over 50 or in stock that meet all the parameters in the query so that would have excluded a lot of the items in the different categories which is why we have such a small number of products in the listing here so if i was to now change this and to an or then you'll see that we get a lot more products coming back into our product listing so there we've used the or and of course then a whole lot more products are available back in the shop so that's how you can use the and and the or parameter then with your query so and means everything must be true all means any one of the items need to be true well i hope you found that video interesting looking at just some of the parameters available to your listing of products this is great for listing on the front page and in a future video we're going to have a look at doing the same thing but instead of entering the items manually here in the filter we're going to have a look at extracting data from custom fields filled in from a form on the website that will then determine what products get displayed so i hope you enjoyed that and thank you for watching

Channel: Bruce Young

Views: 395

Keywords: oxygen builder, advanced queries, meta query, taxonomy query, stock status

Id: CGzt6kaTWKs

Length: 24min 51sec (1491 seconds)

Published: Tue Dec 07 2021