Select Drop Down List contains all values of parent table. How can I get just child table values?

Select Drop Down List contains all values of parent table. How can I get just child table values?

Ovgmw7Ovgmw7 Posts: 5Questions: 1Answers: 0

Simple database with three tables

suppliers - a master table of suppliers for all users
my_suppliers - a table of suppliers selected by the user with additional data i.e. MySupplierNumber
my_contacts - a table containing multiple contacts for my_suppliers

The business logic /workflow is

User selects a supplier and adds it to my_suppliers

User adds multiple contacts for each of their my_suppliers

suppliers and my_suppliers are linked on SupplierID
my_suppliers and my_contacts are linked on MySupplierID

Foreign Key constraints exist to prevent:-

adding a my_suppliers record if not in suppliers (SupplierID)
adding a my_contacts record if not in my_suppliers (MySupplierID)

Therefore to display the SupplierName in the my_contacts Datatable two links are required. First to the my_suppliers table and then to the suppliers table where the field SupplierName exists.

Link to test case:

Live web pages:-

https://wastesaver.org/DataTablesEditor/suppliers.html
https://wastesaver.org/DataTablesEditor/my_suppliers.html
https://wastesaver.org/DataTablesEditor/my_contacts.html

Description of problem:

Everything works great apart form the NEW and UPDATE function on my_contacts

https://wastesaver.org/DataTablesEditor/my_contacts.html

The Select Drop Down List contains all the SupplierID + SupplierName values from the suppliers table.

I need this Select Drop Down List to be populated with just the MySupplierID + SupplierName values that exist in the my_suppliers table. Otherwise the user can select a supplier that is not present in the my_suppliers table throwing an SQL Foreign Key error and not following the business logic of the application.

I've been trying to solve this for days with no joy. If anybody can point me in the right direction I'd be very grateful!

php code for my_contacts is:-

<?php

/*
 * Editor server script for DB table my_contacts
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
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, 'my_contacts', 'MyContactID' )
    
    ->fields(
        Field::inst( 'my_contacts.MySupplierID' ),
            
        Field::inst( 'my_suppliers.SupplierID' )
            ->options( Options::inst()
                ->table( 'suppliers' )
                ->value( 'SupplierID' )
                ->label( 'SupplierName' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'suppliers.SupplierName' ),
        
        Field::inst( 'my_contacts.MyContactName' )
    )
    ->leftJoin( 'my_suppliers', 'my_suppliers.MySupplierID', '=', 'my_contacts.MySupplierID' )
    ->leftJoin( 'suppliers', 'suppliers.SupplierID', '=', 'my_suppliers.SupplierID' )
    
    ->process( $_POST )
    ->json();

And JS code:-

/*
 * Editor client script for DB table my_suppliers
 * Created by http://editor.datatables.net/generator
 */

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.my_suppliers.php',
        table: '#my_suppliers',
        fields: [
            {
                "label": "SupplierName:",
                "name": "my_suppliers.SupplierID",
                "type": "select",
                "placeholder": "Select a Supplier"
            },
            {
                "label": "MySupplierNumber:",
                "name": "my_suppliers.MySupplierNumber"
            }
        ]
    } );

    var table = $('#my_suppliers').DataTable( {
        ajax: 'php/table.my_suppliers.php',
        columns: [
            {
                "data": "suppliers.SupplierName"
            },
            {
                "data": "my_suppliers.MySupplierNumber"
            }
        ],
        select: true,
        lengthChange: false
    } );

    new $.fn.dataTable.Buttons( table, [
        { extend: "create", editor: editor },
        { extend: "edit",   editor: editor },
        { extend: "remove", editor: editor }
    ] );

    table.buttons().container()
        .appendTo( $('.col-md-6:eq(0)', table.table().container() ) );
} );

}(jQuery));

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    The Select Drop Down List contains all the SupplierID + SupplierName values from the suppliers table.

    Yes that's because your options instance on the server does not contain a join and not a where clause either. Let me search for an example. There are multiple ways to do the options. As far as I remember you can't do a left join though. But let me double check. I'll be right back with some suggestions.

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited December 2022

    This should work:

    Field::inst( 'my_suppliers.SupplierID' )
        ->options( Options::inst()
            ->table( 'suppliers, my_suppliers, my_contacts' )
            ->value( 'suppliers.SupplierID' )
            ->label( 'suppliers.SupplierName' )
            //where clause MUST be a closure function in Options!!!
            ->where( function($q) {
                // there should be a user id in the my_suppliers table!!
                // $q ->where('my_suppliers.user_id', $_SESSION["user_id"] );
                $q ->where('my_suppliers.SupplierID', 'suppliers.SupplierID', '=', false); //inner join
                $q ->where('my_contacts.MySupplierID', 'my_suppliers.MySupplierID', '=', false); //inner join
            } )
        )    
    

    Since you can't do a left join in the options instance I used an implicit 1990's style INNER JOIN through the WERE clause.

    But I see a more fundamental problem with your data model. There is no user table and no foreign key from the user table in the my_suppliers table. How should this work?

    More about options:
    https://editor.datatables.net/manual/php/joins#Options

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
  • Ovgmw7Ovgmw7 Posts: 5Questions: 1Answers: 0

    @rt1234 thank you very much for your response. It almost did what I was looking for...

    ... however, it returns just the my_suppliers already added to the my_contacts table.

    What I was trying to achieve was to return a full list of my_suppliers so I can add a new my_contact.

    How do I need to change the query to achieve this please?

    Also, you are 100% correct about the user table. I created a much simplified version of the database to write this forum question. Just focusing on the 3 tables where I have this problem to solve.

    I've been trying all sorts for a couple of weeks to try to get what I want. I'm sure it can be done within editor.datatables some way... but I'm lost finding it....

    Your help is greatly appreciated.

  • Ovgmw7Ovgmw7 Posts: 5Questions: 1Answers: 0

    @rt1234 i've update the code with your suggestion. As you can see from the live webpage it just returns my_suppliers already added to the my_contacts table

    https://wastesaver.org/DataTablesEditor/my_contacts.html

    https://wastesaver.org/DataTablesEditor/my_suppliers.html

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited December 2022

    I need this Select Drop Down List to be populated with just the MySupplierID + SupplierName values that exist in the my_suppliers table.

    If you want to see all suppliers that are in table my_suppliers you would need to drop the INNER JOIN on table my_contacts.

    Using a custom function to retrieve the options with SQL this should do it:

    Field::inst( 'my_suppliers.SupplierID' )
        ->options( function () use ( $db ) {
             $stmt =   ('SELECT DISTINCT a.SupplierName    AS label, 
                                         b.MySupplierID id AS value
                           FROM suppliers a
                     INNER JOIN my_suppliers b ON a.SupplierID = b.SupplierID
                       ORDER BY 1 ASC'); 
              $result = $db ->raw() ->exec($stmt); 
              return $result->fetchAll(PDO::FETCH_ASSOC);
        } );
    

    Using the Editor options instance this should work (INNER JOIN using the WHERE clause):

    Field::inst( 'my_suppliers.SupplierID' )
        ->options( Options::inst()
            ->table( 'suppliers, my_suppliers' )
            ->value( 'my_suppliers.MySupplierID' )
            ->label( 'suppliers.SupplierName' )
            //where clause MUST be a closure function in Options!!!
            ->where( function($q) {
                // there should be a user id in the my_suppliers table!!
                // $q ->where('my_suppliers.user_id', $_SESSION["user_id"] );
                $q ->where('my_suppliers.SupplierID', 'suppliers.SupplierID', '=', false); //inner join
            } )
        )   
    

    Maybe there is some unclarity on what an INNER JOIN does?!
    This should help: https://www.w3schools.com/sql/sql_join_inner.asp
    https://www.w3schools.com/sql/sql_where.asp

    If you think about it: If you can't use an INNER JOIN (like in Data Table's options instance) it is possible to implement the same thing using a condition in the WHERE clause.

    So this should also work, but it is outdated syntax of course:

    Field::inst( 'my_suppliers.SupplierID' )
        ->options( function () use ( $db ) {
             $stmt =   ('SELECT DISTINCT a.SupplierName  AS label, 
                                         b.MySupplierID id AS value
                           FROM suppliers a, my_suppliers b
                     WHERE a.SupplierID = b.SupplierID
                       ORDER BY 1 ASC'); 
              $result = $db ->raw() ->exec($stmt); 
              return $result->fetchAll(PDO::FETCH_ASSOC);
        } );
    
  • Ovgmw7Ovgmw7 Posts: 5Questions: 1Answers: 0

    @rt1234 Half way there!

    All three code examples populate the select DDL correctly now with a list of my_suppliers. The SQL queries had an extra id field name which needed deleting

    b.MySupplierID **id** AS value
    

    However, when I add or edit a record only the MyContactName field is updated. The MySupplierID field is blank.

    https://wastesaver.org/DataTablesEditor/my_contacts.html

    Maybe this is a problem with the JS file?

    /*
     * Editor client script for DB table my_contacts
     * Created by http://editor.datatables.net/generator
     */
    
    (function($){
    
    $(document).ready(function() {
        var editor = new $.fn.dataTable.Editor( {
            ajax: 'php/table.my_contacts.php',
            table: '#my_contacts',
            fields: [
                {
                    "label": "SupplierName:",
                    "name": "my_suppliers.SupplierID",
                    "type": "select",
                    "placeholder": "Select a Supplier"
                },
                {
                    "label": "MyContactName:",
                    "name": "my_contacts.MyContactName"
                }
            ]
        } );
    
        var table = $('#my_contacts').DataTable( {
            ajax: 'php/table.my_contacts.php',
            columns: [
                {
                    "data": "suppliers.SupplierName"
                },
                {
                    "data": "my_contacts.MyContactName"
                }
            ],
            select: true,
            lengthChange: false
        } );
    
        new $.fn.dataTable.Buttons( table, [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ] );
    
        table.buttons().container()
            .appendTo( $('.col-md-6:eq(0)', table.table().container() ) );
    } );
    
    }(jQuery));
    
  • Ovgmw7Ovgmw7 Posts: 5Questions: 1Answers: 0

    Yeah! I've fixed this. Field names was the problem needed to be my_contacts.MySupplierID many thanks to @rt1234 for sorting out the db queries. Much appreciated!

    PHP code


    <?php /* * Editor server script for DB table my_contacts * Created by http://editor.datatables.net/generator */ // DataTables PHP library and database connection include( "lib/DataTables.php" ); // Alias Editor classes so they are easy to use 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, 'my_contacts', 'MyContactID' ) ->fields( Field::inst( 'my_contacts.MySupplierID' ) ->options( function () use ( $db ) { $stmt = ('SELECT DISTINCT a.SupplierName AS label, b.MySupplierID AS value FROM suppliers a, my_suppliers b WHERE a.SupplierID = b.SupplierID ORDER BY 1 ASC'); $result = $db ->raw() ->exec($stmt); return $result->fetchAll(PDO::FETCH_ASSOC); } ), Field::inst( 'suppliers.SupplierName' ), Field::inst( 'my_contacts.MyContactName' ) ) ->leftJoin( 'my_suppliers', 'my_suppliers.MySupplierID', '=', 'my_contacts.MySupplierID' ) ->leftJoin( 'suppliers', 'suppliers.SupplierID', '=', 'my_suppliers.SupplierID' ) ->process( $_POST ) ->json();

    JS Code


    /* * Editor client script for DB table my_contacts * Created by http://editor.datatables.net/generator */ (function($){ $(document).ready(function() { var editor = new $.fn.dataTable.Editor( { ajax: 'php/table.my_contacts.php', table: '#my_contacts', fields: [ { "label": "SupplierName:", "name": "my_contacts.MySupplierID", "type": "select", "placeholder": "Select a Supplier" }, { "label": "MyContactName:", "name": "my_contacts.MyContactName" } ] } ); var table = $('#my_contacts').DataTable( { ajax: 'php/table.my_contacts.php', columns: [ { "data": "suppliers.SupplierName" }, { "data": "my_contacts.MyContactName" } ], select: true, lengthChange: false } ); new $.fn.dataTable.Buttons( table, [ { extend: "create", editor: editor }, { extend: "edit", editor: editor }, { extend: "remove", editor: editor } ] ); table.buttons().container() .appendTo( $('.col-md-6:eq(0)', table.table().container() ) ); } ); }(jQuery));

    Hope this helps somebody else with the same problem!

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

    Nice one - thanks for sharing your solution with us.

    Allan

Sign In or Register to comment.