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 JOIN
s 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?