Date range Filter with Join - server side - throws errors

rbyrns

OK I use this to add the min and max date from my inputs.

    "ajax":{
        url: "./php/table.vert_test.php",
        type: "POST",
            "data": function (d){
            d.min = $('#min').val();
            d.max = $('#max').val();
            }

I check the on the server side and deal with it like so:

$exWhere = "('vert_test.uccsn','0','!=')";  //bogus where that will not fail?  there are no blanks and no just 0
if(( $_POST['min'] != '')AND ($_POST['max']!='')){
    $min = $_POST['min'];
    $max = $_POST['max'];
    $exWhere = "('vert_test.un25_test','$min', '>'),('vert_test.un25_test' < '$max')";  ///  not right  -> testing with $exWhere above

this is where the problem comes in if I add a where:

    ->leftJoin('notes','notes.uid','=','vert_test.uid')
   ->join(
        Join::inst('notes', 'array')
        ->where('notes.phase','testing')  //confirmed
        //->where($exWhere)  //<<<*******----------If I uncomment this errors occur.
 
        ->join('uid','uid')
       ->fields( 
        Field::inst('uid'),
        Field::inst('note')
            ->validator('Validate::required')
            ->set(false),
        Field::inst('type')
            ->validator('Validate::required')
           ->set(false),
        Field::inst('phase')
            ->validator('Validate::required')
           ->set(false)
        )
    ) 
    ->process( $_POST )
    ->json();
}

I get "DataTables warning: table id=vert_test - SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 3 column(s)"

I can totally switch the join stuff out I guess (I was hoping to eventually figure out how to edit a child row). I can get the child row built from ajax. My other problem is that really don't know how this query has to look. sudo code would look like
"select from vert_test, notes left join where vert_test.uid = notes.uid (sub select * from notes where date < $min and date > $max.
dataTables debug is at - http://debug.datatables.net/ibeziy

rbyrns

I had to throw a little markdown at that mess.

allan

Hi,

Currently there isn't an option to use a nested select in the Editor methods. Editor has to understand the options given to it, which is why there is a limited range of options available. Having said that, I am of course keen that it should support as wide a range of options as practically possible!

I'm not familiar with the SQL syntax in the sub select - does its result get assigned to notes.uid? It doesn't look like it would be, but I can't quite see what it would be doing?

Thanks,
Allan

rbyrns

$sql = "select *\n"
. "from vert_test \n"
. "left outer join notes on vert_test.uid = notes.uid\n"
. "where \n"
. "un25_test > \'2014-11-01\' \n"
. "AND\n"
. "un25_test < \'2014-11-10\'\n"
. "";

This gets it minus the notes.type = cleaning part that. It I could get this query to work I could client side filter the rest. I have another post about not getting how to use a direct query.

emily008

ah. I would love to learn this.

allan

You can use the where() statement to perform that operation certainly.

->where('un25_test','2014-11-01', '>')
->where('un25_test','2014-11-10', '<') 

For example.

Allan

rbyrns

    ->leftJoin('notes','notes.uid','=','vert_test.uid')
   ->join(
        Join::inst('notes', 'array')
        //->where('notes.phase','testing')  //confirmed
        ->where('un25_test',$min,'>')
        ->where('un25_test',$max,'<')
        ->join('uid','uid') 
       ->fields( 
        Field::inst('uid'),
        Field::inst('note')
            ->validator('Validate::required')
            ->set(false),
        Field::inst('type')
            ->validator('Validate::required')
           ->set(false),
        Field::inst('phase')
            ->validator('Validate::required')
           ->set(false)
        )
    ) 
    ->process( $_POST )
    ->json();
}

This throws no errors, but does not work. I have this at the top of the page to make sure it works.

if(( $_POST['min'] != '')AND ($_POST['max']!='')){
    $min = $_POST['min'];
    $max = $_POST['max'];
}else{
    $min = "1900-01-01";
    $max = date("Y-m-d");
}

allan

Inside the Join() statement, the where condition applies only to the joined data - not to the parent data. Is that what you want and un25_test is a field of the notes table?

Are you able to show me your full PHP and also a dump of the DB schema so I can running it myself?

Allan

rbyrns

sorry, I have been out of town for a funeral and a birth. I will try to work up a full package so we can get this kind of thing to work. I have diverted from joins at the moment and am trying to get an ajax call to get the notes. I posted that question separately.