Datatable as input with join

carrarachristophe

Hello,

As I never managed to have a Select2 field to work, I am using a datatable as input. This enables me to select an item in the list based on the information contained in various columns.
Here is part of my code:

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.bibliotheque_voeux.php',
        table: '#bibliotheque_voeux',
        fields: [
            {
                label: "Oeuvre:",
                name: "bibliotheque_bibliotheques.oeuvre",
                type: "datatable",
                config: {
                    paging: false,
                    scrollY: 150,
                    scrollCollapse: true,
                    columns: [
                        {title: 'ISBN',
                            data: 'oeuvre_id'},
                        {title: 'Auteur',
                            data: 'auteur'},
                        {title: 'Titre',
                            data: 'titre'},
                        {title: 'Tome',
                            data: 'tome'}
                    ]
                }
            },

Editor::inst( $db, 'bibliotheque_bibliotheques', 'bibliotheque_id' )
    ->fields(
        Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
            ->options( function($db) {
                return $db->select('bibliotheque_oeuvres', array('oeuvre_id', 'auteur', 'titre', 'tome'))->fetchAll();
            } )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Oeuvre requise' )))
    )
    ->leftJoin( 'bibliotheque_auteurs', 'bibliotheque_auteurs.auteur_id', '=', 'bibliotheque_oeuvres.auteur' )
    ->where( 'bibliotheque_bibliotheques.voeux', 1 )
    ->process( $_POST )
    ->json();

Logically, the datatable field displays the following columns:
1. bibliotheque_oeuvres.oeuvre_id
2. bibliotheque_oeuvres.auteur
3. bibliotheque_oeuvres.titre
4. bibliotheque_oeuvres.tome

When I would like to display the following columns:
1. bibliotheque_oeuvres.oeuvre_id
2. bibliotheque_auteurs.auteur
3. bibliotheque_oeuvres.titre
4. bibliotheque_oeuvres.tome

When I amend the code accordingly, I am getting the following message:
DataTables warning: table id=DataTables_Table_0 - Requested unknown parameter 'bibliotheque_auteurs.auteur' for row 0, column 1. For more information about this error, please see http://datatables.net/tn/4

Would you have any example discribing how I could fix that issue and use a join with a datatable as input?

Thanks and regards,

Christophe

allan

Hi Christophe,

The Options class doesn't have a leftJoin method, so yes, you'd need to use a function as you have done - this is something like what you'll need to use for it:

$db
  ->query('select')
  ->table('bibliotheque_oeuvres')
  ->get(array(
    'bibliotheque_oeuvres.oeuvre_id',
    'bibliotheque_oeuvres.titre',
    'bibliotheque_oeuvres.tome',
    'bibliotheque_auteurs.auteur'
  ))
  ->join(
    'bibliotheque_auteurs',
    'bibliotheque_auteurs.auteur_id = bibliotheque_oeuvres.auteur',
    'LEFT'
  )
  ->exec()
  ->fetchAll();

The select() method is basically just a short hand for that sort of chaining. Doing it this way gives more flexibility such as adding join statements.

Allan

carrarachristophe

Thank you Allan,
I am still getting a similar message.
I can avoid it for the first 3 fields by removing the bibliotheque_oeuvres. reference, but when I do the same for bibliotheque_auteurs., I am getting the following message:
"DataTables warning: table id=bibliotheque_voeux - An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'auteur' in field list is ambiguous"
Any idea of what is wrong mentionning the table name before the column?

allan

That error would normally suggest that you haven't added the table name actually.

Could you show me your full PHP script please?

Also, immediately before

->process( $_POST )

Could you add ->debug( true ) and then use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Thanks,
Allan

carrarachristophe

Hi Allan,
If I understood correctly how the debugger works, the code is ifaxaw.

allan

Thank you - unfortunately the debugger is telling me there are no tables on the page so I can't actually see the Ajax response. Is there a JS error in the browser's console? Can you show me the return from the server to the Ajax request please?

Allan

carrarachristophe

Hi Allan,
I think I found what I did wrong.
"ozipek" should work now. Could you please confirm?

allan

Got it now - thanks. It doesn't look like it contains the SQL debug information though. Did you add -debug(true) just before ->process($_POST)?

The debugger is showing me that the server is responding with the following for the options:

        "bibliotheque_bibliotheques.oeuvre": [{
            "bibliotheque_oeuvres.oeuvre_id": "1",
            "bibliotheque_oeuvres.titre": "rarazra",
            "bibliotheque_oeuvres.tome": null,
            "bibliotheque_auteurs.auteur": null

So you would need to have data: 'bibliotheque_oeuvres.oeuvre_id' for the datatable configuration, rather than just data: 'oeure_id'.

I don't see any indication of the error about ambiguous fields though.

Allan

Allan

carrarachristophe

Hi Allan,
Sorry, by cancelling some changes, I removed the -debug(true)
I added it again. Could you please review "otabat"?
I don't understand your point on oeuvre_id. I have bibliotheque_oeuvres.oeuvre_id everywhere

allan

Many thanks! The client-side and server-side aren't quite insync for their configurations.

Two options for you:

1) Leave the server-side script as it is and change the initialisation options for the data properties in the datatable configuration to be:

data: 'bibliotheque_oeuvres\.oeuvre_id'

etc. Note the \ is important - the data coming back from the server as a dot in the property name rather than being a nested object.

2) Leave the client-side script as it is and change the PHP query to get the columns data for the datatable to be

$db
  ->query('select')
  ->table('bibliotheque_oeuvres')
  ->get(array(
    'bibliotheque_oeuvres.oeuvre_id as oeuvre_id',
    'bibliotheque_oeuvres.titre as titre',
    'bibliotheque_oeuvres.tome as tome',
    'bibliotheque_auteurs.auteur as auteur'
  ))
  ->join(
    'bibliotheque_auteurs',
    'bibliotheque_auteurs.auteur_id = bibliotheque_oeuvres.auteur',
    'LEFT'
  )
  ->exec()
  ->fetchAll();

Note the use of as to alias the fields

Allan

carrarachristophe

Hi Allan,
I tried both (ohuwew and oqatap respectively) but still getting the same message.

allan

I think I'm going to need a link to your page to be able to track it down please. Those two suggestions should have worked...

With the second one for example, I can see the options being returned from the server are:

        "bibliotheque_bibliotheques.oeuvre": [{
            "oeuvre_id": "1",
            "titre": "rarazra",
            "tome": null,
            "auteur": null
        }, {

which matches the original JS you showed above.

Allan

carrarachristophe

Hi Allan,

After trying many things, I found out was coming from the client side.
data: 'bibliotheque_oeuvres.oeuvre_id' or data: 'bibliotheque_oeuvres\.oeuvre_id' is causing the issue.
I changed it to 'oeuvre_id'

Here is the final code:

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.bibliotheque_voeux.php',
        table: '#bibliotheque_voeux',
        fields: [
            {
                label: "Utilisateur:",
                name: "bibliotheque_bibliotheques.utilisateur",
                type: 'select',
                placeholder: '',
                placeholderDisabled: false,
                placeholderValue: null
            },
            {
                label: "Oeuvre:",
                name: "bibliotheque_bibliotheques.oeuvre",
                type: "datatable",
                optionsPair: {
                    value: 'oeuvre_id',
                },
                config: {
                    paging: false,
                    scrollY: 150,
                    scrollCollapse: true,
                    columns: [
                        {title: 'ISBN',
                            data: 'oeuvre_id'},
                        {title: 'Auteur',
                            data: 'auteur'},
                        {title: 'Titre',
                            data: 'titre'},
                        {title: 'Tome',
                            data: 'tome'}
                    ]
                }
            }

Thanks again for your help.

colin

Nice, thanks for reporting back,

Colin