Cannot display linked table field info in editor (I can in the datatable, what am I doing wrong?)

geonick

I have a table called "contracts", which is left joined to a table called "clients", which is again left joined to a table called "countries". Using the following code I can get the country's name to be displayed in the datatable, but not in the editor. I am using server side processing. Can anyone please help?

I use this code in my "scontracts.php" which is in turn used in my "contracts.js" below

Editor::inst( $db, 'contracts', 'id' )
    ->fields(
        Field::inst( 'contracts.client_id' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Please select the client for the contract!' )  
            ) )
            ->validator( Validate::dbValues(null, 'id', 'clients') )
            ->options( function($db) {
                return $db->select('clients', array('id', 'firstname', 'lastname', 'country_id'))->fetchAll();
            } ),
        Field::inst( 'clients.firstname' ),
        Field::inst( 'clients.lastname' ),
        Field::inst( 'clients.country_id' )
            ->options( Options::inst()
                ->table( 'countries' )
                ->value( 'id' )
                ->label( 'country' )
            ),
        Field::inst( 'countries.country' ),

        [ . . . ]

    )
    ->leftJoin( 'clients', 'clients.id', '=', 'contracts.client_id' )
    ->leftJoin( 'countries', 'countries.id', '=', 'clients.country_id' )
    ->process( $_POST )
    ->json();

And I use this code this in my "contracts.js"

var editor = new DataTable.Editor({
    ajax: "scontracts.php",
    table: '#contracts',
    fields: [
        {
            label: 'Client:',
            name: 'contracts.client_id',
            type: 'datatable',
            optionsPair: {
                value: 'id'
            },
            config: {
                initComplete: function () {
                    this.api().on('select', function ( e, dt, type, indexes ) {
                      editor.one('open', function () {
                        dt.row(  {selected: true} ).node().scrollIntoView(true);
                      });
                    })
                },
                columns: [
                    {
                        title: 'First Name',
                        data: 'firstname'
                    },
                    {
                        title: 'Last Name',
                        data: 'lastname'
                    },
                    {
                        title: 'Country',
                        data: 'country' // <= country_id works here, country doesn't
                    }
                ],
                paging: false,
                scrollY: '15vh',
                scrollCollapse: true
            }
        },

        [ . . . ]

    ]
});

allan

Hi,

Thanks for the details of the issue. The field name in question is contracts.client_id. Looking at the PHP the options for it is coming from:

return $db->select('clients', array('id', 'firstname', 'lastname', 'country_id'))->fetchAll();

That is where the data is coming from for the list of options, and thus also the four data points you have available for use in the datatable. There is not country there, thus it can't show.

What you need to do is expand the data for the options to include the left joined information. You could do that with:

        Field::inst( 'contracts.client_id' )
            ->options( Options::inst()
                ->table( 'clients' )
                ->value( 'id' )
                ->label( ['firstname', 'lastname', 'country'] )
                ->leftJoin('countries', 'countries.id', '=', 'clients.country_id')
            )

Allan

geonick

Thank you for your prompt reply, Allan.

I tried replacing my field options with yours and now I am getting the following error:

"DataTables warning: table id=contracts - An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous".

allan

Ah, I should have thought of that.

Field::inst( 'contracts.client_id' )
    ->options( Options::inst()
        ->table( 'clients' )
        ->value( 'clients.id' )
        ->label( ['firstname', 'lastname', 'country'] )
        ->leftJoin('countries', 'countries.id', '=', 'clients.country_id')
    )

Will hopefully do it. If you have a country field in both tables, you might need to qualify it as well.

Allan

geonick

Thanks once again, Allan.

Now the error message has changed to:

"DataTables warning: table id=DataTables_Table_0 - Requested unknown parameter 'firstname' for row 0, column 0. For more information about this error, please see https://datatables.net/tn/4"

After some messing about with it, the error seems to be triggered by the "contracts.js" code (columns). Any idea on where I should go from here?

allan

Sorry, I forgot that we need to explicitly state that the extra columns should be returned:

Field::inst( 'contracts.client_id' )
    ->options( Options::inst()
        ->table( 'clients' )
        ->value( 'clients.id' )
        ->label( ['firstname', 'lastname', 'country'] )
        ->include( ['firstname', 'lastname', 'country'] )
        ->leftJoin('countries', 'countries.id', '=', 'clients.country_id')
    )

Important point: Options->include() is going to be renamed Options->inc() due to compatibility issues with older PHP versions. That will be happening with the next release of Editor. Sorry! When it happens, and you update, it will just be a case of renaming the method call.

Allan

geonick

Thanks! I have already noted this change for the upcoming release of Editor. Sadly, we are now facing a new error:

"DataTables warning: table id=contracts - Ajax error. For more information about this error, please see https://datatables.net/tn/7"

Amy idea what I should try next?

allan

What's the response from the server? It might contain an error message. If it doesn't check the PHP error log. It might be you are hitting the issue with the use of include.

Thanks,
Allan

geonick

I am getting a 500 Internal Server Error and no response. How can I check the PHP error log? I checked and I am on PHP v7.3 at the moment. Any idea if this version has the issue with include?

kthorngren

I don't use PHP but maybe this technote will help to view the server log.

Kevin

geonick

Thanks, Kevin.

I thought there was a way to check the error log without having access to the server. I will look into this and get back with more information regarding the issue, or if I am lucky enough with just a report of having solved it and answered my initial question. :-)

Nick

geonick

I couldn't get access to the server PHP error log, but I managed to try the code with PHP version: 8.2.26, which resulted in getting the same error.

Could it be something else, apart from the issue with include?

allan

Could it be something else, apart from the issue with include?

Yes, it could be virtually anything . Try adding:

error_reporting(\E_ALL);
ini_set('display_errors', '1');

at the top of your file (i.e. immediately after the opening <?php). That should cause it to show the error messages, which you'll then be able to see in the "Response" tab for the Ajax request in your browser's network inspector.

Allan

geonick

Thanks, Allan.

I tried that and seem to have gotten to the bottom of the problem. It seems like line 29, which points to include(), is to blame:

"Fatal error: Uncaught Error: Call to undefined method DataTables\Editor\Options::include()"

Where do I go from here? I am currently using PHP v8.2.26 which isn't that old. Could I solve my problems by upgrading to v8.4? Should I wait for the next Editor release and use inc() instead? Or maybe is there another temporary solution I can use?

Nick

allan

Make this change in your local copy of the Options.php file.

Then change ->include( to ->inc( from the code above.

Frustrated that one slipped past me when I realised! I'll try to get an updated version of Editor out later this week with that change, but that's what to do to make it work right now.

Allan

geonick

Thanks, Allan.

The copy of my Options.php looks like it already has the changes you are suggesting.

     *
     * @return ($inc is null ? string[] : $this)
     */
    public function inc($inc = null)
    {
        if ($inc === null) {
            return $this->_includes;
        }

        if (is_array($inc)) {
            $this->_includes = array_merge($this->_includes, $inc);
        } else {
            $this->_includes[] = $inc;
        }

        return $this;
    }

I tried using inc() instead of include() in the suggested code and that has finally made the error go away. It seems to be working now, but with another problem.

The linked table in the editor is not "linked", so even when I make a selection of an entry the field remains empty. Any ideas on this?

Nick

allan

Hi Nick,

Can you PM me a link to the page? That will make it much easier to debug. The field should be selecting whatever option matches the value of contracts.client_id for the row being edited.

Allan

geonick

For others that might be facing a similar issue, I seem to have solved the final boss problem by removing the following code from my contracts.js file:

            optionsPair: {
                value: 'id'
            },