one to many joint and search

MaxValembois

Hi everyone,
I am currently evaluating editor for my software (15 day trial) and I have successfully achieved a one to many joint on my script to handle a variable amount of options (define by the end user)

my json look something like that (I cut the first part as it is not relevant I believe :
{"data":[{"DT_RowId":"row_1","id":"1","est_un_jouet":"0","nom":"Mumbo Jumbo", [....many more....] ,"165_view_options":[{"nom":"etat","valeur":"neuf"},{"nom":"TYPE_DE_JEUX","valeur":"Jeux de soci\u00c3\u00a9t\u00c3\u00a9"}]},
So you can see I get all my option as a table of table named 165_view_options. In each subtable I have 2 fields : "nom" which is the name of the option and "valeur" which is the value.

on the serveur side I have a simple one to many joint to get all the data :

$editor=Editor::inst($db,$_SESSION['table_prefix'] .'jeux as j')->fields(
                Field::inst('j.id','id' ),
                Field::inst('j.est_un_jouet','est_un_jouet'),
                Field::inst('j.nom','nom'),
                ...
                ...
                ...

            )
        ->join(
                 Mjoin::inst('165_view_options')
                    ->link('j.id', '165_view_options.id_associe')
                ->fields(
                        Field::inst('nom'),
                        Field::inst('valeur')
//                        Field::inst('type')
                        )
                   ->where( function ( $q ) use ( $inclureAdherentsRadies ) {
                        $q->and_where(function($r) use ($inclureAdherentsRadies){
                               $r->where('165_view_options.type','jeu','=');
                               $r->where('165_view_options.valeur','','!=');
                         });    
                   })
            )


        ->process($_POST)
        ->json();

On the client side I have several things :
first of all a variable containing all the option name that I use to set up the right amount of columns headers. Note here that I have a dynamic system to show or not the option ($optPersonalisee['affichage'] ) :

foreach ($colonnesOptions as $colonnesOption) {
                            if ($optPersonalisee['affichage'] == 1) {
                                echo'<TH>'.$colonnesOption.'</TH>';
                            }
                        }

Finally the Js is the following :

$(document).ready(function () {
var editor = new $.fn.dataTable.Editor( {
        ajax: '../appServices/ServeurSideJeux.php',
        table: '#TableListJeux',
        fields: [
            {label: 'id', name: 'id',  'className': 'locked', type:"readonly"},
            {label: 'nom', name: 'nom'},
            ]
});







  $('#TableListJeux').DataTable({
        "serverSide": true,
        "ajax": {
            url:'../appServices/ServeurSideJeux.php',
            type:"POST"
        },
            "scrollX": true,
        "columnDefs": [  { "visible": false, "targets": 0 }  ],
        "order": [[ 0, 'desc' ]],
        "language"    : { 
                    "url": "http://cdn.datatables.net/plug-ins/1.10.12/i18n/French.json",
                    'loadingRecords': ' ',
                    'processing': '<div class="chargementDataTable"><span class="loader"></span></div>'
                    },    
        "lengthMenu": [ [10, 25, 50 ,100, -1], [10, 25, 50, 100,'Tous'] ],
        columns : [
            <?php
            foreach ($colonnes as $colonne) {
            ?>
                {  
                    <?php if ($colonne !="image2Affichage") { ?>
                    "data" : "<?= $colonne ?>", 
                    "render": function(data, type, row, meta){
                                //console.log(row);
                                if(type === 'display'){
                                    data = '<a href="./jeux.php?action=view&jeu=' + row['id'] + '">' + data + '</a>';
                                }

                                return data;
                             }
                    <?php }elseif($colonne =="image2Affichage"){ ?>
                    "data" : "<?= $colonne ?>", 
                    "render": function(data, type, row, meta){
                                //console.log(row);
                                if(type === 'display'){
                                    data = '<a href="./jeux.php?action=view&jeu=' + row['id'] + '"><img src="' + data + '"></a>';
                                }

                                return data;
                             }  
                        <?php }else{ ?>
                    "data" : "<?= $colonne ?>",        
                        <?php } ?>
                },
            <?php
            }
            ?>
           <?php foreach ($colonnesOptions as $COption) { ?>
               {data: "165_view_options",
                 render: function(data, type, row, meta){
                        console.log(data);
                        if(type === 'display'){
                            render="";
                            data.forEach(function(item, index, array) {
                                  // console.log(item);
                                  if(item['nom']=="<?=$COption?>"){
                                   render +=item['valeur'];
                               }
                           });
                        }
                        return render;
                     },
            } ,
           <?php } ?>
            {
                data: null,
                className: "center",
                defaultContent: '<a href="" class="bouton editor_edit">modification rapide</a>',
                searchable:false, sortable:false
            }

       ],
        'dom'        : 'Blfrtip',

        'buttons': [
                {
                  extend: 'pdfHtml5',
                  messageTop: 'Liste de jeux',
                  exportOptions: {
                    modifier: { selected: true },
                    rows: { selected: true }
                  }
                },
                'excel'
            ],
        'processing': true,

        "initComplete": function( settings, json ) {
            $('div.dataTables_filter input').focus()
        },
   });


   // Edit record
    $('#TableListJeux').on('click', 'a.editor_edit', function (e) {
        e.preventDefault();

        editor.edit( $(this).closest('tr'), {
            title: 'Edit record',
            buttons: 'Update'
        } );
    } );

});

</script>

All of that works fine and display each option on the right row. The only issue is theses row are not sortable or searchable, i get an error like this one :

"DataTables warning: table id=TableListJeux - Unknown field: 165_view_options (index 12)"

Is there a way to make the search work ?
thanks for the time and the help everyone
Max

rf1234

This thread seems to be dealing with something very similar.
https://datatables.net/forums/discussion/44584

Assuming that this is column 12:

{
                data: null,
                className: "center",
                defaultContent: '<a href="" class="bouton editor_edit">modification rapide</a>',
                searchable:false, sortable:false
            }

I think the correct syntax would be "orderable: false" not "sortable: false".
"sortable" is not or no longer in the list of options: https://datatables.net/reference/option/

So is column 12 the one I mentioned above? What does the browser's console say?

MaxValembois

Hi Rf1234

I will update my code in this direction then but it actually works fine with sortable so far...
the issue is I would like to be able to sort and search and then I get the error

allan

Unfortunately, server-side processing with filtering on the Mjoin'ed column is not currently possible. The problem is that when using Mjoin the query is split into two - the main (host table) query, and the Mjoin'ed table. The search condition is only applied to the main table at this time.

The solution is either to disable server-side processing (how many rows do you have?) or disable search and order for it as rf1234 suggests.

Allan