Parent Child example, issue with prefixing field .id with table name

TonyR

I have implemented the parent-child-editing example on my own system using the Editor-PHP-2.3.2 libraries and it works perfectly. I have run into an issue that seems to prevent me from adding a leftJoin to my main table.

I'm using MySQL for an app that keeps track of rolls of film (main table) and a secondary table that tracks individual frames for each roll (prints).

The functionality works just fine until I attempt to include a leftJoin, then things break. Interestingly, an error is returned that tells me that you always have to include the table name AND field name, and this makes sense, and it's also plainly visible on your site. However, in your example under Server Site PHP code for the parent table, you're not doing that for the paren's .id field. And indeed without the table name, the parent/child editing functions perfectly for me. When the table name is added, no errors show, but the parent/child functionality disappears. And without the table name, and with my leftJoin, no data appears in the child table. Obviously I"m missing something. Here is the relevant PHP Server Site code:

$editor = Editor::inst( $db, 'tpa' )             // tpa is the parent table
    ->field(
        Field::inst( 'id')->set( false ),            //  <------ issue 

        Field::inst( 'tpa.no')->validator( 'Validate::notEmpty' ),
        Field::inst( 'tpa.year'),
        Field::inst( 'tpa.month'),
        Field::inst( 'tpa.notes'),

        Field::inst( 'tpa.format_no')            
            ->options( Options::inst()
                ->table('format_table')          // Formats, like 35mm, 120, etc.
                ->value('id')
                ->label('format_text') 
            )
            ->setFormatter( Format::ifEmpty( null) )
            ->validator( Validate::dbValues() ),  // Makes a nice pick list
        Field::inst( 'format_table.format_text')   
    
    )       
    ->join(
        MJoin::inst( 'prints')                    // prints is the secondary table. 
        ->link( 'tpa.id', 'prints.roll_id')
        ->field(
            Field::inst( 'prints.id' )
        )       
    )
   ->leftJoin( 'format_table', 'format_table.id', '=', 'tpa.format_no')

There are other leftJoins I would like to include.

rf1234

I see you have a table "tpa". Each "tpa" entry can have 1 format_no which is selected from table "format_table".

That looks good but I don't understand what you are needin the first "id" field for. Your code doesn't indicate this.

The Mjoin can't be used for editing it is just displaying the prints.ids belonging to the tpa which is fairly independent from the rest of your code.

Where is the actual parent - child editing please?

Here is an example of parent child editing:
https://datatables.net/blog/2016/parent-child-editing

TonyR

Many thanks for your response! I very much appreciate it.

My code above is a direct copy of the 2016 parent/child article you link to, with the additon of my attempted leftJoin (necessary for including format_text from that lookup table).

I did opt for the Mjoin so that the number of child rows for each parent row are shown. I thought that the need for the id field was to ensure it not being for it to show child rows thus the Validate::notEmpty clause.

TonyR

Correction (sorry): I thought the id field was needed as the example (cited) has it.

rf1234

Ok, if you still have issues, don't hesitate to ask for help! Please also note that field type "datable" can be used for parent - child editing really well.

I also use it a lot with a custom button on the parent table that displays the child table with its editor etc.

The entire thing is fairly flexible.

TonyR

My issue remains, though!

Can you give me a better example of a leftJoin if you are using parent-child editing?

rf1234

I have four different versions of parent child editing implemented. All of my field definitions have table-prefixed field names because I always use one ore more left joins.

Which one do you want?

1. The child table (with its own editor) is displayed underneath the parent table on "select" of a record from the parent table.

2. Using a button on the parent table with which you invoke showing the child table in a modal (after selecting a record from the parent table). The child table has its own editor of course.

3. The child table is in field type "datatable" of the parent table and is displayed when the parent table's editor is started. The child table has its own editor, too.

4. A child table is displayed on button click of the parent table (like in 2. above). That child table has grand children that are dynamically retrieved on "select" of a record from the child table, and the grand children have their own editor.

In all four versions the contents of the child table can be shown in a parent table column as well using an Mjoin.

I started with 1. a couple of years ago. Then developed version 2. based on an example by a gentleman called Kaustubh. He gave me the HTML and the JS for the bootstrap modal of the child table. Version 3. is relatively new. I could implement it when Allan introduced field type "datatable". I made version 4. only a few months ago based on Allan's other blog post on parent - child editing.

The code that you posted is not about parent - child editing, in my opinion. It is just a table that has a foreign key which of course is from a child table, but that child table is not being edited in your code.

rf1234

Before I start posting tons of code you might want to take a look at all of the examples again. This is what I found:

1. The first blog post by Allan: https://datatables.net/blog/2016/parent-child-editing
2. The newer blog post by Allan: https://datatables.net/blog/2019/parent-child-editing-in-child-rows
3. Using field type "datatable": https://editor.datatables.net/examples/datatables/parentChild

The last example doesn't have the PHP code unfortunately. But it is just a normal editor ...

I would assume that the first blog post should still be the most relevant for you. While the parent table has no left join in this example that doesn't really matter. A left join in the parent table is pretty much irrelevant for parent child editing.

Using a left join on the parent table would require table-prefixing of the field names like this:

var siteTable = $('#sites').DataTable( {
    ajax: '../php/sites.php',
    columns: [
        { data: 'sites.name' },
        { data: 'users', render: function ( data ) {
            return data.length;
        } }
    ],
    select: {
        style: 'single'
    },
    layout: {
        topStart: {
            buttons: [
                { extend: 'create', editor: siteEditor },
                { extend: 'edit',   editor: siteEditor },
                { extend: 'remove', editor: siteEditor }
            ]
        }
    }
} );

var usersEditor = new DataTable.Editor( {
    ajax: {
        url: '../php/users.php',
        data: function ( d ) {
            var selected = siteTable.row( { selected: true } );
 
            if ( selected.any() ) {
                d.site = selected.data().sites.id;
            }
        }
    },
    table: '#users',
    fields: [ ... ]
} );

Editor::inst( $db, 'sites' )
    ->fields(
        Field::inst( 'sites.id' )->set( false ),
        Field::inst( 'sites.name' )->validator( 'Validate::notEmpty' )
    )
    ->leftJoin( 'format_table', 'format_table.id', '=', 'sites.format_no')
    ->join(
        Mjoin::inst( 'users' )
            ->link( 'sites.id', 'users.site' )
            ->fields(
                Field::inst( 'id' )
            )
    )
    ->process( $_POST )
    ->json();

Please note the change here:

d.site = selected.data().sites.id;