editor server side selection options based on secondary table

itram

In server side editor Fields I need to add a selection/options feature where in the drop down I want to have the rows from a secondary table groups, but only those rows where groups.groupId matches the current row field books.groupId

I tried

Field::inst('books.bookId')
            ->options(
                Options::inst()
                    ->table('groups')
                    ->value('bookId')
                    ->label(['bookId', 'bookName', 'groupId'])
                    ->render(function ($row){
                        return $row['bookId'] . ' - ' . $row['bookName'] . ' - ' . $row['groupId'];
                    })
            )

which returns a dropdown will all the rows in the groups table, but I nedd only the options list with the rows of the secondary table groups for which column 'groupId' matches the main table books column named also 'groupId'.
Is it possible to order the drop down elements by bookId ASC?

I tried also :

            Field::inst('books.bookId')
            ->options(function () use ($db) {
                $stmt = ('SELECT CONCAT(`bookId`, \' - \', `bookName`, \' - \', `groupId`) 
                    FROM `books` WHERE `groupId`= :groupId
                      ORDER BY groupId ASC');
                $result = $db->raw()
                    ->bind(':groupId', 'books.bookId', PDO::PARAM_INT)
                    ->exec($stmt);

                return $result->fetchAll(PDO::FETCH_ASSOC);
            })

In this case the bind(':groupId', 'books.bookId', PDO::PARAM_INT) is wrong because I don't know how to recover the current row books.bookId, so how to do it?
But it should be an additional mistake, because in this case if I use a fixed value like ->bind(':groupId', 67, PDO::PARAM_INT) I get a drop down of the correct length (number of elements) but the select rows are empty/blank.

How to achieve the required behaviour?

rf1234

You need to post that field to the server using "ajax.data" for example.

Here is an example from my own coding:

var copyCtrAcctDataEditor = new $.fn.dataTable.Editor( {
    ajax: {
        url: 'actions.php?action=tblCopyCtrAcctData',
        data: function ( d ) {
            var selected = ctrTable.row( {selected: true} ); 
            if (selected.any()) {
                d.ctr_id = selected.data().ctr.id;
            }
        }
    },
    table: "#tblCopyCtrAcctData",

Now in PHP you can use $_POST['ctr_id']. Here is an example of an options instance that uses pretty much all of what's possible.

Editor::inst( $db, 'ctr' )
->field(
    Field::inst( 'ctr.id' )->set( false )
        ->options( Options::inst()
            ->table('ctr')
            ->value('id')
            ->label( array('serial', 'ctr_name', 'ctr_partner', 'number') )
            //render serial, instrument, type, number
                ->render( function ( $row ) {    
                    $cp = $row["ctr_partner"];
                    if ( strlen($cp) > 30 ) {
                        $cp = mb_substr( $cp, 0, 26 ) . ' ...';
                    }
                    return  $cp.' / # '.$row['serial']
                            .' / '.$row['ctr_name']
                            .' ('.$row['number'].')';
                } )
            ->order( 'ctr_name, serial desc' )
            //where clause MUST be a closure function in Options!!!
            ->where( function($q) {
                $q ->where( function($r) {
                    $r ->where('id', $_POST['ctr_id'], '!=');
                    //ony contracts that have accounting data
                    $r ->where('id',  
                        '( SELECT DISTINCT ctr_id   
                             FROM ctr_acct_data
                          )', 'IN', false);
                    $r  ->where( 'id',  
                        '( SELECT DISTINCT a.ctr_id
                            FROM ctr_has_ctr_govdept a
                      INNER JOIN ctr_govdept b                   ON a.ctr_govdept_id = b.id    
                      INNER JOIN ctr_govdept_has_user_complete c ON b.id = c.ctr_govdept_id 
                      INNER JOIN user_has_selected_ctr_govdept d ON b.id = d.ctr_govdept_id    
                           WHERE c.user_id = :id 
                             AND d.user_id = :id 
                           )', 'IN', false);
                    $r  ->bind( ':id', $_SESSION['id'] );
                });
            } )
        ),

itram

Thanks rf1234. I may be wrong but it seems not to be my case.

I have two tables, main table which has the rows and fields to be shown in the datatable.

The relevant columns for my case are main.secondaryGroupId and main.itemNr.

I have a secondary table , with secondary.groupId field and secondary.itemNr
In the secondary table, it may be, in fact it will be multiple rows with the same groupId an different itemNr.

The goal, is that once is set main.secondaryGroupId value in one row, or when loading the stored value, and user clicks on main.itemNr field of the row, it appears a drop down with the valid values of main.itemNr which are the rows of the secondary table which matches main.secondaryGroupId = secondary.groupId

rf1234

I am not sure whether I really understand this: Are the options that you want to show context dependent? Meaning they are different for each row of the main table worked on?

If that is the case, the standard Editor options instance does not work. Editor will only retrieve ONE set of options, not multiple.

You would need to retrieve the options individually after selecting one table record (edit case) or a generic set of options (create case).

I have done that before but I will only post it here if you can confirm that those are your requirements

itram

````
var copyCtrAcctDataEditor = new $.fn.dataTable.Editor( {
ajax: {
url: 'actions.php?action=tblCopyCtrAcctData',
data: function ( d ) {
var selected = ctrTable.row( {selected: true} );
if (selected.any()) {
d.ctr_id = selected.data().ctr.id;
}
}
},
table: "#tblCopyCtrAcctData",
```
posts the field on field edition, but I need to add the dropdown options on all client side rows to allow the user to choose only a value within a list of values of another field in the same row. is there any alternative to do it else than doing ajax request for each row in the client side and then fill the select drop down with js?

itram

I am not sure whether I really understand this: Are the options that you want to show context dependent? Meaning they are different for each row of the main table worked on? <<< yes, that's correct.
I am thinking the only way will be to fill the select option in the client side. Am I right, or there is a better option?

allan

If the options are different per row, you need to update them based on the row being edited. The easiest way of doing that is to use dependent(). You can use it to make an Ajax call to the server to get the options for the row being edited and display them.

Have a look at this blog post which details how that might be done.

Allan

rf1234

@allan's advice is really good, I think.

Here is something from my own coding where I need to load context specific options. Since I am using a "selectize" field, and not a built-in "select" field, I cannot simply update the options but I need to clear the respective Editor field and add it back in again.

On "open" I load the options from the server with an ajax call. In "edit" mode I have the respective "parentId". In "create" mode I simply submit 0 to the server.

This is the Javascript:

editor
    .on('open', function (e, mode, action) {
        var that = this;
        $.ajax({
            type: "POST",
            url: 'actions.php?action=getCtrLinkOptions',
            data: {
                ctr_id: action === "create" ? 0 : parentId
            },
            dataType: "json",
            success: function (data) {
                that.clear( "ctr_has_ctr[].linked_ctr_id" );
                that.add( {
                    label: lang === 'de' ? 
                                'Wählen Sie einen oder mehrere zu verknüpfende Verträge:' : 
                                'Please select one ore more contracts to be linked:',
                    name: "ctr_has_ctr[].linked_ctr_id", //render serial, name
                    type: "selectize",
                    options: data,
                    opts: {
                            create: false,
                            maxItems: null,
                            openOnFocus: true,
                            allowEmptyOption: true,
                            placeholder: lang === 'de' ? 
                                'Keine verknüpften Verträge vorhanden' : 'No linked contracts exist'
                            }
                }, "ctr.expired" );
            }
        });
    })

On the server side: Make sure you return "label - value" pairs!

Here is an example

$dbh->query('SELECT DISTINCT a.label_text AS label, a.id AS value 
               FROM ctr_label a
         INNER JOIN ctr_has_ctr_label b         ON a.id = b.ctr_label_id
              WHERE b.ctr_id = :ctrId 
                    UNION
            SELECT DISTINCT a.label_text AS label, a.id AS value 
               FROM ctr_label a
         INNER JOIN user_has_available_label b  ON a.id = b.ctr_label_id
              WHERE b.user_id = :userId
                AND b.is_subsidy_register  = :isSubReg
           ORDER BY 1 ASC');
$dbh->bind(':ctrId', $ctrId); 
$dbh->bind(':isSubReg', isset($_SESSION['subRegAnyPage']) ? 1 : 0); 
$dbh->bind(':userId', $_SESSION['id']); 

echo json_encode( $dbh->resultsetAssoc() ); //array of label - value pair objects

If you are using the built-in "select" field you can use
https://editor.datatables.net/reference/api/field().update()
That makes the Javascript a little simpler because you don't need to clear the field and add it back in again, but you can simply update the options of the existing field.

In my example it would look like this:

editor
    .on('open', function (e, mode, action) {
        var that = this;
        $.ajax({
            type: "POST",
            url: 'actions.php?action=getCtrLinkOptions',
            data: {
                ctr_id: action === "create" ? 0 : parentId
            },
            dataType: "json",
            success: function (data) {
                that.field("ctr_has_ctr[].linked_ctr_id").update(data);
            }
        });
    })

itram

Thanks Allan,
Should dependent().api work for inline edition?
In inline edition I get a drop down with the correct values, so it looks fine, but when I click on the field, the editor.dependent( 'continent', '/api/countries' ); it is making continous requests to the server, making the page unresponsive.
I can't figure out the reason. Any idea where I have to search?

in editor controler, should I define the options in this field?

allan

Should dependent().api work for inline edition?

Generally I'd say no since it usually changes other fields, which is obviously a bit pointless if you just have a single field showing. You could do whole row inline editing where it would make more sense though.

Allan

itram

I am almost there, in the server side I get the options with:
$query = ('SELECT chNr, name FROM channels WHERE bouquetId= :bouquetId AND enable = 1 ORDER BY chNr ASC');

$result = $db->raw()
->bind(':bouquetId', $_REQUEST['values']['players.bouquetId'])
->exec($query);

$channels = $result->fetchAll();

$channelsArray = array_map(function ($item) {
return [
"table" => "channels",
"value" => "players.playingCh",
"label" => $item["chNr"] . " - " . $item["name"]
];
}, $channels);

echo json_encode( [
'options' => [
'players.playingCh' => $channelsArray
]
] );

In the drop down I get correctly the bouquet channels
If I change the bouquet, I get a new list correctly.
But the value (ch number which is an integer), which appears correctly in the table view:

does not appear in the modal edition.
Also, if I choose a chNr in the dropdown, then the editor validation which expects and integer for that value, drops an error because a text is being submitted.

So it seems the value is not correctly set in the options. How to set the value of chNr?

allan

I'd need a link to a page showing the issue to be able to trace it through.

Allan

itram

The server is on a local network and is not accessible from the outside. If there is a confidential way to share my anydesk ID , I can give access to you.