Query posts with numeric meta values within a given range

I ended up finding two solutions within WordPress’ capabilities.

Storing available rather than unavailable dates

The first solution would be to have the agency select all available dates rather than selecting unavailable dates. Our example listing would then have a custom field listing_available:

update_post_meta($post_id, "listing_available", "/20140101/20140102/20140103/20140104/");

I would then be able to run the following LIKE query on the field:

$dates = [];
for($i = $arrival_date; $i <= $departure_date; $i++)
    $dates[] = $i;

$meta = array(
    "key"     => "listing_available",
    "value"   => "https://wordpress.stackexchange.com/".implode("https://wordpress.stackexchange.com/", $dates)."https://wordpress.stackexchange.com/",
    "compare" => "LIKE"
);

All dates between arrival and departure would have to be present in the listing_available field for the listing to show up. For the example case presented in the question, the query would as intended not find the listing, because it is not available during all given dates.

Though this works, it means the agency has to update all posts regularly since posts are now set to unavailable by default. Also the dates have to be ordered for this to work.

Using the REGEXP comparison type

While digging into the core code of WP_Meta_Query (the backbone for meta queries) I found two additional comparison types that have not yet been documented: REGEXP and NOT REGEXP.

These comparison types solved my issue instantly as I could now look for different values in a single meta field without having to mess with the actual query. Using the $dates array from the code above:

$regexp = "(/".implode("https://wordpress.stackexchange.com/"https://wordpress.stackexchange.com/", $dates)."/)";

$meta = array(
    "key"     => "listing_unavailable",
    "value"   => $regexp,
    "compare" => "NOT REGEXP"
);

Now I can regex for any date within the given range and (by using NOT) drop the result if a date matches.

If WP_Meta_Query would support setting an OR relationship for a single meta query with multiple values, I would’ve been able to do the same using LIKE.

I’m still wondering whether this is the most efficient way to filter available rentals without heavy SQL queries or complex query-altering functions. If anyone knows of a more efficient way to solve the issue I’ll pick it as a ‘best anwer’.

Related Posts:

  1. How to handle optional end date in compare clause in meta_query
  2. Arrange Posts By Date In Order Of Closest To The Current Date
  3. Meta Query Based on Month Range
  4. wordpress meta value compare between two date
  5. Ordering custom posts by meta field date
  6. How to compare date in post meta value
  7. Meta query terribly slow
  8. meta_query sorting by 2 keys
  9. Query between two meta keys
  10. How to check a single number value against an array of meta_key values?
  11. Meta query with multiple logic (AND / OR)
  12. Best practice – Meta Query vs. post_clauses for “left join” ordering
  13. Different page parameters inside query
  14. LIKE %…% Meta Query
  15. Multiple Values stored as array in Meta Query
  16. Documentation for post meta
  17. Advanced orderby with multiple meta_query
  18. Ordering posts by anniversary using only day and month
  19. pre_get_posts filter using numeric meta_query comparison (from dates)
  20. Very slow query with meta_query on large database
  21. How to use meta_query to check if a meta field has something set?
  22. Displaying posts with only upcoming dates according their custom field date value
  23. Use meta_query to display events by date in custom field
  24. meta_query results not the same with and without spaces
  25. How to show recent and upcoming birthdays using meta_query
  26. meta_query all posts between to ages
  27. How to make a WP REST API query with meta_query in WP4.7?
  28. Nested Queries using meta_query
  29. Wildcard with LIKE operator in Meta query
  30. Query posts only if meta_value is not empty
  31. WP_Query meta_query >= date
  32. How to pass user meta_key and meta_value (values as array)
  33. WP_Query ordered by meta_value_num doesn’t fetch posts without this meta field
  34. Query Multiple values for same key
  35. Custom post type archive with rand and meta_value
  36. Comparing a field with several values at once with meta_query
  37. Meta query with a sub query group possible?
  38. meta_query compare=’!=’ with multiple custom fields
  39. meta_query not working on live site
  40. How do you get Posts by multiple meta_keys and meta_values with the Rest API V2?
  41. Query custom field with date
  42. Meta query with regular expression does not work
  43. Searching in title or meta, with a tax query too!
  44. Meta Query posts not showing on ending date of custom field
  45. WP User Query Issues
  46. Pre Get Posts / Multiple Meta Keys / Orderby Single Key
  47. Help on multiple meta_query
  48. Meta query – How to display a null meta key
  49. pre_get_posts and set
  50. WordPress Screwing Up ‘orderby’ => ‘meta_value_num’ in ‘pre_get_posts’
  51. How to query wp_postmeta table for any specific meta_value?
  52. User meta query results in PHP notice: only variables should be passed by reference
  53. meta_query with multiple values optimization
  54. Get a single post ID based on an exact match of 1 or more meta values
  55. meta_query with multiple key
  56. Search by meta_query
  57. last_name + first_name orderby with meta_query [solved]
  58. meta query based on month regardless of year
  59. Extend meta query arguments
  60. get_posts( $args ) return empty data
  61. meta_query in WP_User_Query not working – returns every user, not just users where key & value matches
  62. how to get post meta where value is an array of key value pairs
  63. meta_query array in ‘values’ returns an error
  64. how to search all user meta data that have value like “vivek”
  65. WP JSON API meta_query not working
  66. Show single posts date, in a page of posts
  67. Using WP meta query to show custom post types by a start and finish date
  68. How to properly escape values in meta query
  69. Fetch custom post related to a User
  70. How to Ordering by user meta
  71. Use meta_query in args for relationship
  72. Order resultset by configured value and then list all the rest
  73. How to send variable to wp meta_query value?
  74. Querying terms with calculations based on term meta data, sql vs. get_terms
  75. get_user can’t read variable
  76. meta_query check multiple date formats
  77. WordPress meta query not working
  78. Get meta_value from GDRating
  79. Why does this incorrect pre_get_posts meta_query work for sort order?
  80. Meta_Query with multi select values
  81. How to sort custom posts including null meta-values ​in admin columns with meta_query?
  82. Get Posts from Custom Meta Query
  83. get posts meta query returning the latest post if no meta key found
  84. Search using specific meta fields only (excluding post title and content)
  85. How to fetch WP_User_Query with multiple role arguments [duplicate]
  86. How to get meta value based on latest post id with group by term name
  87. Meta query not returning results even though the sql contains the information
  88. Display Users from two roles in one list
  89. Multiple Queries with meta_query
  90. Get foreach for meta_query value
  91. meta_query and strange orderby behaviour
  92. Sorting not working with get_posts
  93. order by meta_key [duplicate]
  94. WordPress Meta Query very slow with NOT EXISTS comparison
  95. ACF meta_query field treated as “0” resulting in false negative search when testing ranges
  96. WP_Query() displaying past post / event
  97. Is it possible to customize meta query in this way?
  98. Ordering WP_Query results by second meta_key
  99. How making a new search box in admin post table working on a specific column
  100. wp_user_query with meta_value_num for leaderboard