Need SearchPanes Filter in the Parent Table

theoneinfo

Link to test case: https://bit.ly/37ICtMy
Debugger code (debug.datatables.net): https://bit.ly/2zHBZto
Error messages shown: bindings":[{"name":":wherein1","value":"1","type":null}
Description of problem:
Dear Datatables Support:

I have a project in which my main page requirement is similar to https://datatables.net/blog/2019-01-11
Main Parent Table: https://bit.ly/37ICtMy

But when the above page loads I also want the Searchpane Filters to be linked to Parent Table, the other columns should be hidden but the searchpanes should have the filters mentioned in the example https://datatables.net/blog/2020-05-12
Filter Table Required: https://bit.ly/2Yc4tow

Now when the page loads, the Parent Table will contains all the users mentioned in the sites, as i have hidden some column but I want to filter it using search pane.

But when the page loads, the value in the searchpane for every column shown "1".

When I directly executed the controllers PHP it shows searchPanes":{"options":[]}

The search pane value does not even generates, even I tried to look from debug it shows null : "name":":wherein1","value":"1","type":null

After that I tried several examples and saw that when ever the "->searchPaneOptions( SearchPaneOptions::inst())" for any field is done after an "MJoin" it does not work. Even if I put "->searchPaneOptions( SearchPaneOptions::inst())" in the blog example https://datatables.net/blog/2019-01-11 after an "Mjoin" the php output for searchPanes":{"options":[]} is empty.

Please help me though the above scenario https://bit.ly/37ICtMy to obtain the search pane as working in (TEST2) https://bit.ly/2Yc4tow

sandy

Hi @theoneinfo ,

Just to make sure that I am understanding you correctly, you want to have a parent / child DataTable setup, where SearchPanes provides filtering for both the parent and children from the parent DataTable?

Would you mind also please posting your controller so that I can see what you are doing there as well? Then hopefully I can try to replicate the issue locally.

Thanks,
Sandy

theoneinfo

Yes, I want the Parent & child filtering from the searchpane with their number shown in individual pane.

TEST1
test_sites.php

Editor::inst( $db, 'sites' )
    ->fields(
        Field::inst( 'id' )->set( false ),
        Field::inst( 'name' )->validator( 'Validate::notEmpty' )
    )
    ->join(
        Mjoin::inst( 'users' )
            ->link( 'sites.id', 'users.site' )
            ->fields(
                Field::inst( 'id' ),
                Field::inst( 'first_name' )
            ->searchPaneOptions(SearchPaneOptions::inst()//These are not working
        ),
        Field::inst( 'last_name' )
            ->searchPaneOptions( SearchPaneOptions::inst()),//These are not working
        Field::inst( 'phone' )
            ->searchPaneOptions( SearchPaneOptions::inst()),//These are not working
        Field::inst( 'site' )
            ->searchPaneOptions( SearchPaneOptions::inst())//These are not working
            )
    )
     ->debug(true)
    ->process( $_POST )

test_users.php

if ( ! isset($_POST['site']) || ! is_numeric($_POST['site']) ) {
    echo json_encode( [ "data" => [] ] );
}
else {
    Editor::inst( $db, 'users' )
        ->field(
            Field::inst( 'users.first_name' ),
            Field::inst( 'users.last_name' ),
            Field::inst( 'users.phone' ),
            Field::inst( 'users.site' )
                ->options( 'sites', 'id', 'name' )
                ->validator( 'Validate::dbValues' ),
            Field::inst( 'sites.name' )
        )
        ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
        ->where( 'site', $_POST['site'] )
        ->process($_POST)
        ->json();
}

TEST2
ss_searchpanes.php

Editor::inst( $db, 'datatables_demo' )
    ->field(
        Field::inst( 'first_name' ),
        Field::inst( 'last_name' ),
        Field::inst( 'position' ),
        Field::inst( 'office' ),
        Field::inst( 'extn' ),
        Field::inst( 'start_date' ),
        Field::inst( 'salary' )
    )
    ->write( false )
    ->process( $_POST )
    ->json();

searchPanes.php

Editor::inst( $db, 'users' )
    ->field( 
        Field::inst( 'users.first_name' )
            ->searchPaneOptions(SearchPaneOptions::inst()
        ),
        Field::inst( 'users.last_name' )
            ->searchPaneOptions( SearchPaneOptions::inst()),
        Field::inst( 'users.phone' )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->table( 'users')
                ->value( 'phone' )
            ),
        Field::inst( 'users.site' )
            ->options( Options::inst()
                ->table( 'sites' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'sites.name' )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->value( 'sites.name')
                ->label( 'sites.name' )
                ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
            )
    )
    ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
    ->debug(true)
    ->process($_POST)
    ->json();
    ->json();

I have one more question as I am new to datatables, I have one more Database table it contains all the sanctioned data of users belonging to particular site nut in TEST1 the data shown in column 3 is number of users posted in particular site, I also want to add another column which fetched data from master table of santioned users at site, so I can get the result of vacant users at particular sites, as mentioned in TEST1.

theoneinfo

Hi @Sandy,
Is there any update regarding the above issue, if you have gone through the above controller code, please provide me some resolution for the same.
Also for your 1st question: Yes I want the parent / child DataTable setup, where SearchPanes provides filtering for both the parent and children from the parent DataTable

sandy

Hi @theoneinfo ,

Sorry not yet. I've spent some time on it this morning trying to replicate the issue, but it is the weekend and I feel this may be longer than a 5 minute job. This will be the first thing that I do when I get back into the office on monday and I will be sure to let you know when I have a solution.

In the meantime, although this is unlikely that this will fix all of the issues immediately, you will need to post your data when using SearchPanes rather than a simple get. This means that this...

ajax: "../controllers/test_sites.php",

will become this

ajax: {
    url: "../controllers/test_sites.php",
    type: 'POST'
}

Thanks,
Sandy

sandy

Hi @theoneinfo ,

I've been looking at this this morning as promised, I've managed to replicate the issue locally. Currently SearchPanes is not compatible with MJoins, sorry.

Currently the best way to use SearchPanes with joins is to use a left join, which won't work for a parent child setup, however you could initialise searchpanes on the child table.

Sorry about this,
Sandy