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

axel_tsys

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

allan

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_tsys

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

allan

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_tsys

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();


<?php
>
?>

allan

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_tsys

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.

allan

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_tsys

Thanks Alan