Advanced Query on a custom post type and ACF custom fields. Repeater, date, text, numeric and list

in this video we're going to look at using the advanced query to query a custom post in that custom post we have several fields so we have a date from a day 2 field then we have a choice of select which returns choices such as choice b choice c then we have a selling price which is a numerical value and then we have a repeater field and what we're going to do now is have a look and see how to use the advanced filter to query these various items so to make up this filter that you or this query that you see here i'm going to head over into oxygen i'm going to edit my query and you'll see that i've generated one big meta query that then queries all those values so we first start with product type and because this is just on a standard page we've said the post type in this case i called it product test and then we have the meta query now just to run through the queries the first query that we have then is a query which is a date query so the type is date and then we look at the date from field so we've said if the date from is greater than the 30th of november then include the um post and if the date is also less than the 25th of the 12th include the post so if we go across here and have a look you'll see that they're within that parameter now what i'm going to do is change the to date to less than 24. so now i head over here and i change the two day to less than 24. and i'm going to save that and now when i refresh the list of posts you'll see that there's only one visible i'm going to head back into the query and now what i'm going to do is i'm going to return that to 30 and then i'm going to do a date from change so the date from then we'll say let's get the other post back first and then we'll have a look so we'll save that right so now we have both back and now will say from the 12th so we eliminate that first post so we'll head over into edit query and now i'm going to make that the 12th save head over to the front end and you'll see now the reason it's still there is because we didn't change the month we just changed the end date so let's make that the 12th of december and now it will filter for the month of december and now you'll see that when we refresh only the second post is visible so that's how the date query works and the format that you need to return from your custom field setup is 2021-12-12 so if we go and have a look at the field group and we look at the date from date or the date to this value over here in the return format must be capital y dash m d and that will then return the correct format for the query the next thing that we're going to look at then is going to be the options and this is the [Music] option select and the option select is the choices select field here so you'll see a choice c choice d choice e so pretty much what we've done here now just to show you how we generate the key is the key is the name of the custom field so date from day to the option select these are all the names of the custom fields so if i close here you'll see that there we have our date from and our day two there we have the we have a selling price we have an options which is the repeater which we'll come to later so we pretty much use that name as the key so when we look at our query and we want to know what key to use it's the name not the title but the database name for the custom field so it's not the label it's the name right so that's how we work with the key and now we have choice c but let's have a look and see what we have in choices so what i'm going to do now is just reset that query so what we'll do now we'll go dash 11-30 so we get the posts visible again head over to the front end enter right so there we have the post back now what we're going to do is have a look to see how the choices work now here we have choice b and choice c selected and here we have choice c choice d and choice e so i'm just gonna indicate that we want anything with the choice b selected so if i go into my query and now what i'm going to do is the choice c i'm going to change to a choice b save and when i head over to the website you'll see that the first post is visible and the second one isn't and if i go into my query again and now what i'm going to do is change the choice b to a choice e and not let's just save that and now when we have a look at the results you'll see now that the second post is visible and the first one isn't visible what i can also do is have a look and request that more than one item is available in the result so let's head over here and you'll see that we've just said choice e but then i can add a second value and quite easily say that choice e and choice e and choice d must be visible so let's save that and now when we head over to the website and we refresh you'll see that ah we get an error the error is due to the fact that we when filtering by the select field we actually need to create a new meta query for the second value so to do that what we'll do is we'll head over back to our query and now what we're going to do is instead of including both choices in the same query we're now going to create a second query and we're going to include it in that one so we add the two values scroll down first one the relation we say and and then we set up the array and in the array now we'll use the same key so i'm just going to copy that key there we have the key now we're going to enter the value and the value for this one we're going to make choice d choice d and then we're going to put in the compare and the compare will also be like right so there we have the same query but now we're looking at a different value and now when we refresh you'll see that the query runs correctly and only the second post shows now we're going to do the same thing but we're going to just change it to include the choice b so we're going to edit that query scroll down and now i'm going to change the choice e to b choice b and that should then filter the results and we should see the other post visible now in the listing now let's just go back and have a look see what values are available there so here i am in the query i'm just going to edit that query again and i'm going to change the values so we're going to say then choice b and choice c so there we have choice b and choice c save head over to the website enter and there you see we have the first post with choice b and c and we had a look and we saw how we could retrieve the second one with choice d and e so that's how you can search or do the query by the select field the select field that we used is this one here that we have in our field group here it was option select and you'll see we just have choice a choice b choice c choice d choice e and in the custom post type itself it's just the standard select item right now what we need to do is have a look at the next item on the list here and that is with the selling price which is a number so i'm going to go into my query now and you'll see that i have a value set up here by selling price which is the name of the custom field and the value that we have there is 2500 and i'm going to now just remove the second query here and i'm also going to now just select let's see what was that choice d and that should then show both posts again so i'm just going to save that head over to the front end and enter still not so i'm going to take away the that query completely just so we can see both posts again so remove that and remove that right so now if i go and have a look on the front end we should see both posts and now we're going to do that numerical filter so the common field between the two is choice c so we'll put that back but before we do that i'm just going to show you on the number that we have here so here we have the key which is the selling price we have a value and we've said greater than 2 500 and what i'm going to do now is say greater than 3 so that we only see the second value so i'm going to head over here and we're going to say greater than three close and save and now when i look at the values here you'll see that only the second post is visible in a similar way if i head over to the query and i say only show values less than and we make that less than 5000 you will see now that only the post with a value of 3000 will be visible in the results the next one that we're going to look at then so we just wait for that to refresh still showing let me just go back to my query so in the query uh that compare was uh missing head over to the front end of the website and now you'll see that it shows the 3000 if we wanted to look at values between two values we can do that we can head over then to the selling price i'm now going to say let's go to 500 and 5 500 and now i'm going to say between and close save and now when we have a look you'll see that both posts are visible again and if i wanted to bracket just the first one i will edit the query and what i'm going to do now is change the second value there to 3 500 close save and now when we run the query you'll see that only the first post is visible so there because it falls between those two parameters so that's how we can work with numbers and what i'm going to do now is just say in fact we can do the bracketing between and i'm just going to change that to 5 500 and what i'm also going to do is add the query back for the choices so i'm going to add the query back here and we're going to make that an array and we're going to add the value and now quickly head back to our custom fields here we have our custom select and so we're going to choose the option select custom field as the key so we go to the key enter the key now we're going to put in the value and the value here was choice c which means that both posts will be visible and we look at the compare value and select like close that save and now when we refresh you'll see that that is now also part of the query and that both posts are visible the next thing that we're going to do is have a look at the repeater field now the repeater field works slightly differently because it's a subfield and not a main field so in order to access those values there's a function that we need to add as a snippet to the website so that the filter will work correctly so what i'm going to do is just show you how to set that up so the first thing to do is when you head over to advanced custom fields you'll see that on their website they have a reference here on how to set up a post in a subfield and in order to do that we need to add this function over here to the website and the reason for doing this is to access then the subfields and to be able to do a query on the subfields there's more information here about the technical reasons for it but all we need to do to get it to work is put this function on the website and the way that it works is as follows here is the snippet that i've used on this website so you'll see here it looks at the meta key options underscore and the dollar sign acts as a wild card representing all the subfields that are in the options repeater so when i go and have a look at my custom fields and here's my repeater you'll see that my repeater's field name is options so what you need to do is take your the name of your custom field and enter that here into the first piece underscore and then the dollar sign that's all you need to do is replace those values and then this function will ensure that the repeater is queried when we do the query inside the advanced query now what i'm going to do is head over to my to my query and show you how to input that value into the query so what we have here is you'll see there's the options underscore dollar sign and if i scroll a little bit to the right you'll see then that we have option title so what i've done there now is i've included the name of the subfield in the next portion so if i go back here you'll see that we have the option title so here in my oxygen query i've said the option options underscore dollar sign and then option underscore title is the name of the subfield that we've used in the example given here on advanced custom fields you'll see that they have a repeater called locations and in that repeater they have a subfield called city and that's what they've used here for these two queries so what we've done is we've taken the name of the repeater which is underscore options underscore dollar underscore and after that underscore the name of the custom field and in that we've entered option 1 and you'll see here that on the on both these posts we have option one so what i'm going to do now is change the option on one of those to option two so here i'm updating that subfield in my repeater to option two i'm going to update the post and now when i run the query you'll see that that field that particular post is no longer coming up in the query results i'm going to head now back to the back to my um editor here the advanced query i'm going to change that now to option 2. save that and now you'll see that the second item is now visible so what i'll do now is just change it back to option one so that's how you can query the repeater field and what we're going to do now is look at option price so when i go and have a look at the website now you'll see that [Music] that's not working let me just check that i've got the right values here so option two and we changed back to option one so back to option one so that's why nothing was displayed and now i'm going to enter and both are back and now you'll see here that we have option one and option two now what i'm going to do in the query now is actually adding the price for those items so here in my repeater i'm going to add a another field so add we're going to add a text value and now in this text value in the repeater we're going to add the field which had the option price so here i can see that i'm looking for option price so here i'm going to go to the repeater field and i'm going to look for option price we're going to insert that so now we have the option price displayed in the results as well we'll save that and now we'll head over to the website and now you'll see that the option price is the price right at the bottom and we have one of 200 and one of a thousand and now we're going to query that in the query so i head back to the repeater head back to the query advanced query and now what i'm going to do is just copy this value here which is the key and what i'm going to do now is add the two values scroll down the relation is and and then we enter the array and we'll add the three values and now we're going to add the key so the key in this case is going to be the option no not the option title now what we need to look at is the option price so we go into the editor and now so we keep the option underscore dollar underscore after that underscore we're going to delete everything right so everything deleted and now i'm pasting in option price then i'm going to put in the value and i'm going to add a value and we know that it's between 200 and a thousand so what i'm going to do in the first instance is i'm going to say greater than 300. so we're just going to see the second post so we make that a 300 then i'm going to put in compare and i'm going to say greater than so now we're going to query a numerical value in the repeater save head over to the website and now i should only see the post with the custom option value of a thousand so let's just refresh that again so now i'm going to head over to my query again i'm going to scroll down and just make sure that i have everything correct so we have the value of 300 we have the compare of greater than and we have options underscore option price and let's add a type and let's tell it that it's a numeric field save and refresh and now you'll see that it's working so when you're doing the subfield you need to tell it that it is a numeric field and what i'm going to do now is edit that query again and i'm going to change that so we said greater than 300 now what we're going to do is say smaller than that value and that should automatically then bring up the 200 and now we can also bracketed between two values so i'm going to head over here and i'm going to add a second value here and i'm going to make the second value 100 and i'm going to change the compare to between so it now knows it needs to look for a value between the 300 and the 100 and i'm going to [Music] and just have a look at that refresh again and edit that query right so the between value is not working when looking at the subfield so another way of doing that would be then to um uh let's say the value um in this case would be the type is numeric and we need to add the compare so we're going to add the compare here and say that the value is greater than and we'll make that 100 and then we'll add another value here and in that value what we're going to do then relation and we'll make that and again and we're going to put in the array and in the array once again the key we're going to do is going to be the same as that previous key so i'm going to put in key then what i'm going to do is add in the value and the value in this case will be 300 and the type will be new merrick and then finally the compare is going to be smaller than so we're basically saying great greater than 100 smaller than 300 numerical value save and now we should have the first post visible with the 200 and now what we'll do is we'll do that for the second one now we know that that has a value of a thousand so we'll edit the query and scroll down and now what we'll do is we'll make that greater than 900 and smaller than 3000 save and now you will see that we have the second one so the other thing that we can do now is if i was to change the value of this post with a thousand and i changed the option here to option 2 and i update and i head over here to the query and i refresh you'll see that nothing is visible so i head back into my query and so now i'm actually doing the filter by the main fields and i'm also doing it by the subfields in the repeater and i change that option value to option 2. and i refresh the page you will see then that that field that that particular post is back so to get them both back now maybe i want to do option one and option two so what i'm going to do now is so here we have the options by [Music] option two i'm now going to create the query again so that we can include option 2. so that was if you wanted two specific values or specific values returned so now we go relation and head over to array and now we're going to add the values again so here we go to the key and the key is the same as the key for option 1 which was option title then i'm going to look at the value so the value is going to be option one so there we have option two now we have option one and we're going to look at compare which is equal and we'll close that save that and now we'll have both posts no post visible let's have a look at the query so there we have option the key option title so what we can do now is let's try the option one here so we'll just delete this one so now we've included both text values in the same query and that will give us the error which happened the same with the select so we're now going to edit that query and we're going to go to option one and then let's add that again as a value and the relation will be and and the array let's have a look we'll enter the key and so that's the option title and the value and the reason why nothing is showing is because none of them have the value of option one and option two so when we use the and relation here it's impossible because none of the posts have that value so what was returned is in fact correct there are no posts with those two values so to make that work i would have to say or so it would have to be option one or option two so there we have option one and now we have option two and we need to use or as the relation now when i save and i refresh now when we refresh we'll see both posts displayed just a tip when dealing with the queries in oxygen builder you'll see that this query is rather large you'll see that i initially included all the queries in one meta query however that's not a requirement and what i found is that when this query this meta query got too big oxygen wouldn't load and probably to do with the size of this meta query so by removing just the options underscore dollar underscore option title which was the query for the title of the repeater just by removing that into a separate meta query oxygen loaded again is working fine so i think it's important not to make your meta queries too big trying to put everything into one meta query you can split it up you can have more than one meta query in your advanced query so that however is now working you'll see the two options there and everything is working fine so that's how you can query your custom posts by custom fields and then also how you can query the repeater fields and manipulate the values numbers or text as you would a normal field so i hope you enjoyed this video and thank you for watching

Channel: Bruce Young

Views: 219

Keywords:

Id: gqnFQZUZN90

Length: 31min 45sec (1905 seconds)

Published: Thu Dec 09 2021