Help with date range selector

[formulate]

I'm really struggling with crafting a selector that will look for matches between two separate date ranges.

I have a template called "booking" and the following date inputfields: "bookingStartDate" and "bookingEndDate". Pages using the "booking" template specify a start and end date range using the above 2 date inputfields.

On the front-end I have a form that submits dates to the following variables: "frontStartDate" and "frontEndDate".

All dates are using unix timestamp and I have verified that I can craft single date selectors that work with the date formats from both the front and back ends, so I know it's not a timestamp formatting issue.

Basically, I want to find all pages that have any overlap in date ranges. If the front-end start and end dates are entirely outside of any back-end date ranges, then no matches. If even one day is matched, then return the page.

Below is my incredibly gimpy selector that is very broken. I have tried much simpler variations of this without success.

$tmp = $pages->find("template=booking,bookingStartDate>=$frontStartDate,bookingStartDate<=$frontEndDate|bookingEndDate<=$frontEndDate,bookingEndDate>=$frontStartDate");
 

Please help! Thanks!

[Macrura]

you could do it multistage with add-> e.g. start with simple selectors of things that will match and then incrementally add more;

or you could foreach through them and do more advanced checking on each item, but this will be pretty slow;

or see if OR groups would work:

$tmp = $pages->find("template=booking,bookingStartDate>=$frontStartDate,(bookingStartDate<=$frontEndDate, bookingEndDate<=$frontEndDate),bookingEndDate>=$frontStartDate");

https://processwire.com/api/selectors/#or-groups

[formulate]

you could do it multistage with add-> e.g. start with simple selectors of things that will match and then incrementally add more;

or you could foreach through them and do more advanced checking on each item, but this will be pretty slow;

or see if OR groups would work:

$tmp = $pages->find("template=booking,bookingStartDate>=$frontStartDate,(bookingStartDate<=$frontEndDate, bookingEndDate<=$frontEndDate),bookingEndDate>=$frontStartDate");

https://processwire.com/api/selectors/#or-groups

Yeah, this didn't work. Thanks for trying to help though. Maybe I need to completely re-think how I'm storing the data in the first place.

It seems to me there has to be a way to compare two separate date ranges without a ton of separate loops and processing overhead.

Maybe someone else can chime in either with a different selector or advice on a different approach? I'm open to anything.

[LostKobrakai]

Copied this from what I'm using, so you'd need to change some names and variables around. 

$selector = "";

// Start date inside
$selector .= ", range=(";
	$selector .= "datefrom>=".$input->whitelist("datefrom");
	$selector .= ", datefrom<=".$input->whitelist("dateto");
$selector .= ")";
// End date inside
$selector .= ", range=(";
	$selector .= "dateto>=".$input->whitelist("datefrom");
	$selector .= ", dateto<=".$input->whitelist("dateto");
$selector .= ")";

[formulate]

Copied this from what I'm using, so you'd need to change some names and variables around. 

$selector = "";

// Start date inside
$selector .= ", range=(";
	$selector .= "datefrom>=".$input->whitelist("datefrom");
	$selector .= ", datefrom<=".$input->whitelist("dateto");
$selector .= ")";
// End date inside
$selector .= ", range=(";
	$selector .= "dateto>=".$input->whitelist("datefrom");
	$selector .= ", dateto<=".$input->whitelist("dateto");
$selector .= ")";

THIS WORKED! Wow, thank you so much! You have saved me enormous amounts of time and stress. I owe you a beer (or a lot of beers)!

[formulate]

Copied this from what I'm using, so you'd need to change some names and variables around. 

$selector = "";

// Start date inside
$selector .= ", range=(";
	$selector .= "datefrom>=".$input->whitelist("datefrom");
	$selector .= ", datefrom<=".$input->whitelist("dateto");
$selector .= ")";
// End date inside
$selector .= ", range=(";
	$selector .= "dateto>=".$input->whitelist("datefrom");
	$selector .= ", dateto<=".$input->whitelist("dateto");
$selector .= ")";

Ok, so this solution isn't 100% bullet-proof. I found an unusual case scenario where a result will return false when it should be true.

If a back-end datefrom has a date before the front-end datefrom AND the back-end dateto is > front-end dateto, it won't return a result even though it should.

Example:

I have a back-end date range of October 1 to October 5. On the front-end if I specify October 2 as my start and October 4 as my end, the resulting selector will return no result. However, if I change my front-end Oct 4 to something greater than the back-end October 5, it works as expected. OR if I change my front-end October 2 to something before the back-end October 1, it works as expected.

I'm not sure I can wrap my head around a solution to this obscure circumstance. It hurts my brain trying to come up with proper range selectors involving dates. Ugh.

For now I'm just going to live with it as it is, but I figured you should know LostKobrakai as you use this code yourself and may want to fix it. If you come up with something, let me know.

[LostKobrakai]

Just add another option to the selector, where you check for cases, where the start is before the searched date and the end is after the searched date. 

$selector = "";

// Start date inside
$selector .= ", range=(";
	$selector .= "datefrom>=".$input->whitelist("datefrom");
	$selector .= ", datefrom<=".$input->whitelist("dateto");
$selector .= ")";

// End date inside
$selector .= ", range=(";
	$selector .= "dateto>=".$input->whitelist("datefrom");
	$selector .= ", dateto<=".$input->whitelist("dateto");
$selector .= ")";

// range overspans searchrange
$selector .= ", range=(";
	$selector .= "dateto>=".$input->whitelist("dateto");
	$selector .= ", datefrom<=".$input->whitelist("datefrom");
$selector .= ")";

[formulate]

Just add another option to the selector, where you check for cases, where the start is before the searched date and the end is after the searched date. 

$selector = "";

// Start date inside
$selector .= ", range=(";
	$selector .= "datefrom>=".$input->whitelist("datefrom");
	$selector .= ", datefrom<=".$input->whitelist("dateto");
$selector .= ")";

// End date inside
$selector .= ", range=(";
	$selector .= "dateto>=".$input->whitelist("datefrom");
	$selector .= ", dateto<=".$input->whitelist("dateto");
$selector .= ")";

// range overspans searchrange
$selector .= ", range=(";
	$selector .= "dateto>=".$input->whitelist("dateto");
	$selector .= ", datefrom<=".$input->whitelist("datefrom");
$selector .= ")";

This worked perfect! Thanks again!