Query posts with numeric meta values within a given range

Question

I'm building a rental agency's website with WordPress. I'm having difficulties finding a good way to store an apartment's availability in meta fields. Specifically, enabling visitors to find apartments available within a given date range.

I figured I would have the agency select which dates an apartment is unavailable in the post editor. My first guess was to store the dates in a single string. For an example listing that is unavailable from 5 January through 7 January I would keep a single meta record like so:

update_post_meta($post_id, "listing_unavailable", "/20140105/20140106/20140107/");

Now when I would look for a listing available from 6 January through 11 January...

$arrival_date   = 20140106;
$departure_date = 20140111;

...I would run the following meta query:

$meta = [];

for($i = $arrival_date; $i <= $departure_date; $i++)
    $meta[] = array(
        "key"     => "listing_unavailable",
        "value"   => "/".$i."/",
        "compare" => "NOT LIKE"
    );

$query->set("meta_query", $meta);

The meta query will return any post which has no unavailable dates within the given range. Perfect! However, WordPress adds one JOIN for each meta query, so now we're very limited in date range. I think SQL supports up to 61 JOINs and I have a lot of other fields visitors can filter on. So I would only be able to select a range of 2 or 3 weeks before I'd get a JOIN limit error.

So then I figured, maybe I should add the unavailable dates each as a separate value. For example, again from the 5th up to the 7th:

delete_post_meta($post_id, "listing_unavailable");
add_post_meta($post_id, "listing_unavailable", 20140105);
add_post_meta($post_id, "listing_unavailable", 20140106);
add_post_meta($post_id, "listing_unavailable", 20140107);

Now I imagine a meta query for 6 January through 11 January would look something like this:

$meta = array(
    "key"     => "listing_unavailable",
    "value"   => array($arrival_date, $departure_date),
    "compare" => "NOT BETWEEN",
    "type"    => "NUMERIC"
);

This doesn't work though. The visitor will now still find the example listing because 5 January is outside of the "NOT BETWEEN" range, deeming the example listing a valid result.

I can't seem to think of the right setup to tackle this issue using standard WordPress functions, without making the SQL query too heavy. Any ideas or suggestions?

Answer 1

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"   => "/".implode("/", $dates)."/",
    "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("/|/", $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'.