Use a where filter during creating a 'new' dataset entry

Use a where filter during creating a 'new' dataset entry

axel_tsysaxel_tsys Posts: 19Questions: 5Answers: 0

Hi,

I am struggling with the 'where' function.
There are 2 tables related via leftJoin.

Editor::inst( $db, 'request_network', 'id' )
->leftJoin( 'Firewalls', 'Firewalls.fwid', '=', 'request_network.firewall_id' ) 

Then I have create a filter:

->where('Firewalls.hw','FW-Cluster')

If I open the panel everything is correct and I only see the entries where
Select * WHERE 'Firewalls.hw' = 'FW-Cluster')

But if I want to create a 'NEW' entry then I got all dataset from the related table 'Firewall'
The fields definition :

    var editor = new $.fn.dataTable.Editor( {
     ajax: 'php/table.request_network.php',
     serverSide: false,
     table: '#request_network',
     fields: [
     {
     "label":"Firewall",
      "name":"request_network.firewall_id",
       "type": "datatable",
       "placeholder": "Select a Firewall",
       "config": {
      "paging": false,
      "scrollY": 150,
      "scrollCollapse": true}
      },

Did I have to add a filter also in the "new $.fn.dataTable.Editor" section?

Kind regards

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Based on what you describe here, I would expect the same options to appear for the request_network.firewall_id field in both create and edit modes.

    If that is not the case, can you give me a link to your page so I can check it out and understand what is happening please?

    Thanks,
    Allan

  • axel_tsysaxel_tsys Posts: 19Questions: 5Answers: 0

    Hi Alan,
    thanks for the response but the page is not reachable via internet.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Can you use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is. Also, could you show me the full PHP code you are using for the table? My built in parser will hopefully be able to use the information from those two things to understand what is going on :).

    Allan

  • axel_tsysaxel_tsys Posts: 19Questions: 5Answers: 0

    Hi Alan,
    the upload code is 'ojociz'

    <?php
    
    // DataTables PHP library and database connection
    include( $_SERVER["DOCUMENT_ROOT"] . "DataTables/lib/DataTables.php" );
    require_once $_SERVER["DOCUMENT_ROOT"] . '/lib/functions.php';
    
    use
    DataTables\Editor,
            DataTables\Editor\Field,
            DataTables\Editor\Format,
            DataTables\Editor\Mjoin,
            DataTables\Editor\Options,
            DataTables\Editor\Upload,
            DataTables\Editor\Validate,
            DataTables\Editor\ValidateOptions;
    
    
    
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'request_network', 'id' )
    ->fields(
                    Field::inst( 'request_network.status' ),
            Field::inst( 'request_network.firewall_id' )
                    ->options( Options::inst()
                            ->table( 'AdminFirewalls' )
                            ->value( 'fwid' )
                            ->label( 'name' )
                    )
            ->validator( Validate::dbValues() )
            ->validator ('Validate::notEmpty')
            ,
            Field::inst( 'AdminFirewalls.name' )
    
            )
            ->leftJoin( 'AdminFirewalls', 'AdminFirewalls.fwid', '=', 'request_network.firewall_id' )
    
          ->where('AdminFirewalls.hw','FW-Cluster')
    
            ->debug(true)
            ->process( $_POST )
            ->json();
    
    ?>
    
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Ah! You have ->where('AdminFirewalls.hw','FW-Cluster') on the main table of data, but not on the Options. It doesn't automatically cascade down (since it might not be applicable), so you need to do:

                    ->options( Options::inst()
                            ->table( 'AdminFirewalls' )
                            ->value( 'fwid' )
                            ->label( 'name' )
                            ->where('AdminFirewalls.hw','FW-Cluster')
    

    if you want to limit the list of options as well.

    Allan

  • axel_tsysaxel_tsys Posts: 19Questions: 5Answers: 0

    Hi Alan,
    sorry for the late answer.
    With this update I got a SQL string

    "query":"SELECT DISTINCT  \"fwid\" as \"fwid\", \"name\" as \"name\" FROM  \"AdminFirewalls\" WHERE \"AdminFirewalls\".\"hw\" IS NULL "
    

    So en empty answer.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    edited July 2021 Answer ✓

    Oops - sorry - I forgot the function signature for the where method. I should have used:

    ->options( Options::inst()
            ->table( 'AdminFirewalls' )
            ->value( 'fwid' )
            ->label( 'name' )
            ->where(function ($q) {
                $q->where('AdminFirewalls.hw','FW-Cluster');
            })
    

    The docs for it, include an example with a where condition are available here.

    Allan

  • axel_tsysaxel_tsys Posts: 19Questions: 5Answers: 0

    Thanks Alan :smiley:

Sign In or Register to comment.