Where, oh where, are the wheres?

aveak

Say I'm designing a table that lists occupations of rooms in a hotel. The client has asked to filter the table by a specific date, so that she can get a "snapshot" of the occupied rooms in days past. Rooms that are occupied have a null value in the checkout field.

So, I'm trying to do something like:
[code]
$editor->where('checkin',$_GET["atdate"],"<=")
->where('checkout',$_GET["atdate"],">=");
[/code]

This works, but it doesn't capture where rooms are still currently occupied during that time period. I've tried the following, but it doesn't work, as or_where isn't exposed at this level, apparently:
[code]
$editor->where('checkin',$_GET["atdate"],"<=")
->where('checkout',$_GET["atdate"],">=")
->or_where('checkout',null,'is');
[/code]

Does anyone have any suggestions for how I can achieve this?

allan

I'm afraid that at the moment, as you say, Editor doesn't expose the more complex WHERE abilities of the underlaying database libraries. I will look at addressing that in future, but what you could do at the moment is use the `sql()` method to built your own SQL statement to get the data (while still using the Editor libraries for the create, edit and delete):

[code]
if ( !isset($_POST['action']) ) {
// Get data
$data = $db->sql( 'SELECT .... FROM ... WHERE ... ' )->fetchAll();
echo json_encode( array( 'aaData' => $data ) );
}
else {
Editor::inst( $db, 'users' )
->...
->process( $_POST )
->json();
}
[/code]

Regards,
Allan