Editor With Join Throws Errors

lifestyle

When I try to save any edits I get:

<b>Fatal error</b>: Call to a member function val() on a non-object in <b>www/extra/DataTables-1.10.1/extensions/Editor-1.3.2/php/Editor/Editor.php</b> on line <b>1026</b><br />

Also, to populate the pick list, it will autoselect the correct companyname only if I don't pass an orderby argument to the selectdistinct() function... so the list is not friendly to choose from. If I order by companyname asc, the list is ordered accordingly, but the current company is not selected upon edit... it simply selects the first item...

So... what am I doing wrong. I would like to be able to save my edits and fix the behaviour of my joined data pick list.

Last little question, is there a way to use the select2 jquery plugin on the selects in datatables so that long lists could be ajax populated and searchable... it would make the UI even slicker than it is now.

Thanks! Learning the editor and loving it.

PHP Source

$data = Editor::inst( $db, 'mydb.my_contacts as contacts', 'contacts.id' )
    ->fields(
        Field::inst( 'contacts.live' ),
        Field::inst( 'contacts.contact_id' ),
        Field::inst( 'contacts.fullname' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'contacts.position' ),
        Field::inst( 'contacts.bus_tel' ),
        Field::inst( 'contacts.bus_ext' ),
        Field::inst( 'contacts.mobile_tel' ),
        Field::inst( 'contacts.email' ),
        Field::inst( 'contacts.notes' ),
        Field::inst( 'contacts.LastModified' ),
        Field::inst( 'brokers.CompanyName' )
    )
    ->leftJoin( 'mydb.my_companies as brokers', 'brokers.id', '=', 'contacts.fk_adv_id' )
    
    ->process( $_REQUEST )
    ->data();
    
    if ( ! isset($_REQUEST['action']) ) {
    // Get a list of sites for the `select` list
    $data['company'] = $db
        ->selectDistinct( "mydb.my_companies", "id as value, CompanyName as label")
        ->fetchAll();
    }
    
    echo json_encode($data);


JS Source:

var editor = new $.fn.dataTable.Editor( {
        "ajax": { url: "data.php", type: 'POST' },
        "table": "#brokers",
        "fields": [
            {
                "label": "Live",
                "name": "contacts.live",
                "type": "checkbox",
                "ipOpts": [
                    {
                        "label": "",
                        "value": "1"
                    }
                ],
                "separator": "|"
            },
            {
                "label": "Contact ID",
                "name": "contacts.contact_id",
                "type": "text"
            },
            {
                "label": "Sales Rep",
                "name": "contacts.fullname",
                "type": "text"
            },
            {
                "label": "Position",
                "name": "contacts.position",
                "type": "text"
            },
            {
                "label": "Business Tel",
                "name": "contacts.bus_tel",
                "type": "text"
            },
            {
                "label": "Ext",
                "name": "contacts.bus_ext",
                "type": "text"
            },
            {
                "label": "Mobile",
                "name": "contacts.mobile_tel",
                "type": "text"
            },
            {
                "label": "E-mail",
                "name": "contacts.email",
                "type": "text"
            },
            {
                "label": "Company",
                "name": "contacts.fk_broker_id",
                "type": "select"
            },
            {
                "label": "Notes",
                "name": "contacts.notes",
                "type": "textarea"
            },
            {
                "label": "Last Modified",
                "name": "contacts.LastModified",
                "type": "readonly"
            }
        ]
    } );
    
        
       
    var oTable;
        
        oTable = $('#brokers').dataTable( {
            "dom": "Tfrtip",
            "ajax": { url: "data.php", type: 'POST' },
            "columns": [
            {   data: null, 
                defaultContent: '', 
                orderable: false
            },  
            {
                "data": "contacts.live"
            },
            {
                "data": "contacts.contact_id"
            },
            {
                "data": "contacts.fullname"
            },
            {
                "data": "contacts.position"
            },
            {
                "data": "brokers.CompanyName"
            },
            {
                "data": "contacts.bus_tel",
                "render": function (data, type, row) {
                    
                    return data.replace(/(\d{3})(\d{3})(\d{4})/, '$1-$2-$3');
                }
            },
            {
                "data": "contacts.email"
            }
        ],
        "tableTools": {
            "sRowSelect": "os",
            "sRowSelector": 'td:first-child',
            sSwfPath: "../../extra/DataTables-1.10.1/extensions/TableTools/swf/copy_csv_xls_pdf.swf",
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor },
                {
                    sExtends: "collection",
                    sButtonText: "Save",
                    sButtonClass: "save-collection",
                    aButtons: [ 'copy', 'csv', 'xls', 'pdf' ]
                },
                'print'
            ]
        },
        initComplete: function ( settings, json ) {

            editor.field( 'brokers.fk_broker_id' ).update( json.company );
        },
            "sPaginationType": "bs_normal",
            "bProcessing": true,
            "bAutoWidth":false,
            "bStateSave": false,
            "fnServerParams": function ( aoData ) {
                aoData.push( {"name": "filter", "value": $('#broker_filter').val() }); 
            },
            "fnRowCallback": function( nRow, aData, iDisplayIndex, iDisplayIndexFull ) {
                        
                        
                        if ( aData.live == "0" )
                        {
                            //$('tr', nRow).addClass('meadow');
                            //nRow.className = nRow.className + ' ' + 'concrete';
                        }
                        
                        return nRow;
            }
            
        });

allan

Hi,

Thanks for all the details. I'm afraid I'm out of the office for the night now so I can't test and debug the code above until tomorrow, but I rather suspect that there is an issue with the aliasing. If you were to removing the aliases in the PHP code, I think it would probably work as expected.

Also, to populate the pick list, it will autoselect the correct companyname only if I don't pass an orderby argument to the selectdistinct() function...

That's odd. Are you able to give me a link to the page so I can debug that. Putting in an order clause really shouldn't make any difference!

Last little question, is there a way to use the select2 jquery plugin on the selects

There is a plug-in for Selectize which is very similar. I'll look into creating a plug-in for select2 as well.

Allan

lifestyle

Ok thanks for the comments. I have removed the aliases and used the table names in their entirety but no difference... I will await your reply once you are back.

allan

Bummer - thanks for letting me know. I'll dig into it when I get back into the office tomorrow morning.

Allan

lifestyle

Also, when I install the plugin (works ok) the selectized object in the modal is empty (it seems that editor.field( 'mytable.field' ).update( json.company ) puts the data into a regular select control, but selectize ignores it or is not aware of the initComplete update to the control. I have even tried editor.field( 'mytable.field' ).update( json.company ).selectize(); to no avail...

It does work fine with static assigned values for the select control, just not with dynamically added content

PS: Aliasing would be an awesome feature so we can hide some our our DB schema from prying eyes of folks who examine our JS... they will know our column names, but hiding the table name just seems to make sense... especially when the data comes from more than one DB.table like my example...

allan

Actually, I was thinking about this and I might know what it is from scanning the code. The contacts.fk_adv_id and brokers.id field values are retrieved or used, so on update the isn't enough information to be able to do the join.

Could you try adding:

Field::inst( 'contacts.fk_adv_id' ),
Field::inst( 'brokers.id' ),

to the fields list and in the Editor constructor:

{
    "name": "contacts.fk_adv_id",
    "type": "hidden"
},
{
    "name": "brokers.id",
    "type": "hidden"
}

and see if that resolves the issue?

Thanks,
Allan

lifestyle

Yes, that seems to be the issue. Also, I seem to have to use my full dbname.table.field syntax on each field and then again (of course) in the javascript. This works, but seems like a big security issue since the full table names and fields are visible to the user on the page source. Is there any way around this?

Also, any ideo how to invoke selectize on the updated select options from the data array? A standard select is fine, but when I choose selectize, it is empty.

allan

Good to hear that works. I don't have a workaround at the moment, but I would like the aliasing to work in this case, and I'll look into addressing this for the next Editor build.

Regarding the security aspect - I don't really see exposing the database name as a security issue. I might be wrong, and it is certainly undesirable, but it wouldn't give any information that a hacker wouldn't be able to obtain if they already had access to the database! If they don't have access, it isn't going to do them any good.

For the selectize update - the selectize API will need to be used.

You can gain access to the selectize API object using the inst() method of the Editor plug-in: var s = editor.field( 'myField' ).inst();.

So you might have something like:

var s = editor.field( 'myField' ).inst();

s.clearOptions();
s.addOption( 'Hello world' );

Allan

lifestyle

Allan I am having an issue with this last suggestion...

This works... and my select is populated fine

editor.field('myfield').update( json.company );

This does not work

var f = editor.field('myfield').inst();
f.update(json.company);

This does not work either (with the select2 plugin) The select2 list is empty.

editor.field('myfield').update( json.company );
editor.field('myfield').select2('data', json.company);

There must be something with the way the plug and field types work. I am obviously not passing the right object or data to it. It works great as a plain old select but I want to use the plugins you created.

Also, select2 seems to want the json to be id and text instead of value and label is this part of the issue?

allan

The selectize plug-in doesn't implement an update method I don't think. You would need to use the selectize API to do the update.

Allan

lifestyle

Right... so editor.field('myfield').select2('data', json.company); should work according to the API. it seems that the editor.field object is wrong or maybe the fact that the data in JSON is value/label and select2 wants id/text... i was not sure if your plugin makes the translation from value/label to id/text or not... I cannot seem to push values into that editor field post-initilization... I see other options using ajax calls to populate iOPTs but I already have the data in the page's JSON and wanted to use it...

allan

There isn't a select2() method available on the field object. Just the standard field methods and the extra inst() method are available for the select2 plug-in.

Perhaps, editor.field('myfield').inst('data', json.company); should do it? (although i don't see a data() method in the select2 API documentation?

Allan

lifestyle

This is driving me nutty...

On the Select2 docs it shows populating the list using the following (as one of many ways):

$("#e11").select2({
  placeholder: "Select report type",
  allowClear: true,
  data: [{id: 0, text: 'story'},{id: 1, text: 'bug'},{id: 2, text: 'task'}]
});

If I load the select2 plugin (not selectize) into the editor/datatables, by using:

var editor = ...
{
    "label": "Province / State",
    "name": "myDB.State",
    "type": "select2"
}

and then attempt to populate the list...

initComplete: function ( settings, json ) {
     editor.field('myDB.State').inst('data', json.provstates);
}

I see the select2 stylized element but it has no data... despite the fact that json.provstates as all the data I need in the 'id':'text' format that select2 needs to see in JSON

If I make the element as simple select, I can populate it without issue using:

editor.field( 'myDB.State' ).update( json.provstates );

Can you offer any insight into this? I just cannot seem to get this data to update into the select2 box after the table has initialized (had the same issue when using selectize, but I prefer select2 for a few reasons, primarily since it can be a select list or allow text input...

Ideally, it would only populate the box when actually switching to edit mode to reduce the data on the page source... but I can live without that if only I could use the dynamic select2 :)

allan

Well that was not in the least bit easy! Select2 doesn't appear to like the data source being changed and the recommended way is to use a function and a variable that you change.

So I tried that, but that doesn't work if you attach Select2 to a select element.

So what I found was that if you change the options of the select element and then trigger a change event, Select2 will update. Little messy, but it can be compacted into a little function for the plug-in:

    update: function ( conf, data ) {
        _fieldTypes.select2._addOptions( conf, data );
        $(conf._input).trigger('change');
    }

Just add that into the plug-in and then you will be able to do:

editor.field('users.site').update( [ {label:'one', value:1} ] )

(note that it uses label and value like the select field type in Editor).

I'll update the plug-in on the site with this addition.

Allan

lifestyle

Thank you very much.