I was wanting to get some advice of whether a particular complex filter query can be achieved involving multiple post reference fields and some date calculations
For our site, we have a custom search for a list of Seamen. Search fields include a general text search field that searched the Seaman's post title, along with the following additional search parameters:
- a min/max age search (user can enter something like 20 for min age and 60 for max age)
- Citizenship value, which is a Post Reference field to the Citizenship custom post type
- Position value, which is again, a Post Reference to the Seaman Positions post type
I wish to create a wpv_filter_query to do the following:
1) All 4 search parameters (post title, age, citizenship & position) use an OR condition.
2) The query has to somehow include searches on the Seaman's related post reference field for Citizenship or Position
3) Somehow do some on the fly calculation to determine if the Seaman's date of birth is within the age range in years being provided. The DOB field is a Date field (unix timestamp) that is a custom field with the Seaman post type.
I've tried to search for similar scenarios on the Forum and something that resembles the date/age calculation, but can't seem to find anything.
If this can'r be achieved with a single query, I'm happy to look at breaking it down into multiple WP_Query's and bring the resulting posts together at the end, but would prefer it in a single query. Do you know of any example floating our here that may have dealt with the same scenario as above that you point me to or give me a general example on how a query might look for this?
Thank you for contacting us and I'd be happy to assist.
1. For minimum and maximum age filter, the most efficient way would be to add a new numeric type "Age" custom field with the post type "Seaman". This field will hold the numeric age value and you can set it to automatically be calculated from the year in DOB field and the current year.
You can use the "save_post" hook to attach a custom function that can automatically fill the numeric age, every time the "Seaman" post is created or edited from the post edit screen: https://developer.wordpress.org/reference/hooks/save_post/
Additionally, you can also use WordPress Cron to schedule a custom function to periodically update the numeric Age value in all the "Seaman" posts, with respect to the year in the DOB field and the current year: https://developer.wordpress.org/plugins/cron/
Once you have the correct numeric year value in the "Seaman" posts as a custom field, you can use a search filter with a "between" comparison without having to add any customizations to the front-end search fields or the query.
2. Post reference fields are a type of post-relationship as well and a view supports a query filter for only a single post-relationship, at a time.
Have you considered using custom Taxonomies for the "Citizenship" and "Position"? This way you'll be able to use a taxonomy filter for them without having to add any customization to the query.
An important point to remember here is that taking care of complex operations at the data storage phase would bring in significant performance benefits at the front-end search phase since the query and processing would be simpler.
Apologies for the lengthy delay on a response here and thanks for your suggestions. Despite agreeing with you in terms of utilising taxonomies for things like Citizenship and Position, this is not possible as other custom data is currently and will be in future, associated with these custom post types.
At the end of the day I went about it all another way. For each of the search criteria, I conduct separate queries and basically map reduce the results down to a single list of seaman post IDs. I do this using PHP's array_intersect() method and it seems to work well enough for the moment.
It's by no means efficient, especially when it comes to the age calculation. For that I am trying to map reduce the number of seamen I need to query for first, before looping through the final result set and just doing some simple timestamp conversions to a date and date diffing that against the current date. Finally ensuring they have an age between the two age range values given.
