editor.set to update join table

marboed

Hi,

I tried to update value on join table but somehow it throws me error that the field could not be found / unknown.

Tried to use method that is described here:
https://editor.datatables.net/examples/api/triggerButton

here is the query:

Editor::inst( $this->editorDb, 'hc_claims' )
        ->fields(
            Field::inst( 'a3m_account_details.fullname' ),
            Field::inst( 'hc_department.department_title' ),
            Field::inst( 'hc_claims.expense_date' )
                ->validator( Validate::dateFormat( 'Y-m-d' ) )
                ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
                ->setFormatter( Format::dateFormatToSql( 'Y-m-d' ) ),
            Field::inst( 'hc_claims.description' ),
            Field::inst( 'hc_claims.amount' ),
            Field::inst( 'hc_claims.submitted_date' )
                ->validator( Validate::dateFormat( 'Y-m-d' ) )
                ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
                ->setFormatter( Format::dateFormatToSql( 'Y-m-d' ) ),
            Field::inst( 'claim_status.status' )
        )
        ->join(
            Mjoin::inst( 'files' )
                ->link( 'hc_claims.id', 'claims_files.claim_id' )
                ->link( 'files.id', 'claims_files.file_id' )
                ->fields(
                    Field::inst( 'id' )
                        ->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'/genesis-claim-backend/admin/assets/files/__ID__.__EXTN__' )
                            ->db( 'files', 'id', array(
                                'filename'    => Upload::DB_FILE_NAME,
                                'filesize'    => Upload::DB_FILE_SIZE,
                                'web_path'    => Upload::DB_WEB_PATH,
                                'system_path' => Upload::DB_SYSTEM_PATH
                            ) )
                            ->validator( Validate::fileSize( 500000, 'Files must be smaller that 500K' ) )
                            ->validator( Validate::fileExtensions( array( 'png', 'jpg', 'jpeg', 'gif' ), "Please upload an image" ) )
                        )
                )
            )
        ->leftJoin( 'a3m_account_details', 'a3m_account_details.account_id', '=', 'hc_claims.user_id' )
        ->leftJoin( 'hc_department', 'a3m_account_details.department_id', '=', 'hc_department.department_id' )
        ->leftJoin( 'claim_status', 'hc_claims.id', '=', 'claim_status.claim_id AND hc_claims.step_no = claim_status.step_no' )
        ->where( 'claim_status.approver_id', $userID )
        ->process( $post )
        ->json();

and here is the view:

var table = $('#example').DataTable( {
                dom: "Bfrtip",
                ajax: {
                    url: "Ajax/approveClaim",
                    type: "POST"
                },
                serverSide: true,
                columns: [
                    { data: "a3m_account_details.fullname" },
                    { data: "hc_department.department_title" },
                    { data: "hc_claims.expense_date" },
                    { 
                        data: "hc_claims.amount", 
                        render: $.fn.dataTable.render.number( ',', '.', 2, '$' ) 
                    },
                    { data: "hc_claims.submitted_date" },
                    {
                        data: "files",
                        render: function ( d ) {
                            return d.length ?
                                d.length+' file(s)' :
                                'No file(s)';
                        },
                        title: "File(s)",
                        orderable: false,
                        searchable: false
                    },
                    { data: "claim_status.status" }
                ],
                select: true,
                order: [
                            [ 4, "desc" ],
                            [ 6, "desc" ]
                        ],
                buttons: [
                    // { extend: "create", editor: editor },
                    { extend: "edit",   editor: editor },
                    { extend: "remove", editor: editor },
                    {
                        extend: "selectedSingle",
                        text: "Approve",
                        action: function ( e, dt, node, config ) {
                            // Immediately add `250` to the value of the salary and submit
                            editor
                                .edit( table.row( { selected: true } ).index(), false )
                                .set( 'claim_status.status', 'approved' )
                                .submit();
                        }
                    },
                ]
            } );

it throws me error of

can please advise, which side I did wrong and whether it is possible to update join table using this method? if not then, could kindly advise me which method that I can use?

Thank you very much in advance.

allan

What is odd there is that your set() call is passing claim_status.status, not just the claim_status that the error message is showing.

Could you give me a link to the page so I can debug it please?

Allan

marboed

Hi Allen,

thank you very much for your reply. I have update my code but now facing different kind of error when "approve" button is click.

$(document).ready(function() {
            editor = new $.fn.dataTable.Editor( {
                "ajax": "Ajax/approveClaim",
                "table": "#example",
                "fields": [ {
                        type: "readonly",
                        label: "Full name:",
                        name: "a3m_account_details.fullname"
                    }, {
                        type: "readonly",
                        label: "Department:",
                        name: "hc_department.department_title"
                    }, {
                        label: "Expense Date:",
                        name: "hc_claims.expense_date",
                        type: "datetime"
                    }, {
                        type: "textarea",
                        label: "Description:",
                        name: "hc_claims.description"
                    }, {
                        label: "Submitted On:",
                        name: "hc_claims.submitted_date",
                        type: "datetime"
                    }, {
                        label: "Amount:",
                        name: "hc_claims.amount"
                    }, {
                        label: "File(s):",
                        name: "files[].id",
                        type: "uploadMany",
                        display: function ( fileId, counter ) {
                            fileLink = editor.file( 'files', fileId ).web_path;
                            thumbnailImage = '<img src="'+fileLink+'"/>';
                            return '<a href="'+fileLink+'" target="_blank">'+thumbnailImage+'</a>';
                        },
                        noFileText: 'No file(s)'
                    }, {
                        label: "Status:",
                        name: "claim_status.status"
                    }
                ]
            } );
var table = $('#example').DataTable( {
                dom: "Bfrtip",
                ajax: {
                    url: "Ajax/approveClaim",
                    type: "POST"
                },
                serverSide: true,
                columns: [
                    { data: "a3m_account_details.fullname" },
                    { data: "hc_department.department_title" },
                    { data: "hc_claims.expense_date" },
                    { 
                        data: "hc_claims.amount", 
                        render: $.fn.dataTable.render.number( ',', '.', 2, '$ ' ) 
                    },
                    { data: "hc_claims.submitted_date" },
                    {
                        data: "files",
                        render: function ( d ) {
                            return d.length ?
                                d.length+' file(s)' :
                                'No file(s)';
                        },
                        title: "File(s)",
                        orderable: false,
                        searchable: false
                    },
                    { data: "claim_status.status" }
                ],
                select: true,
                order: [
                            [ 4, "desc" ],
                            [ 6, "desc" ]
                        ],
                buttons: [
                    // { extend: "create", editor: editor },
                    { extend: "edit",   editor: editor },
                    { extend: "remove", editor: editor },
                    {
                        extend: "selectedSingle",
                        text: "Approve",
                        action: function ( e, dt, node, config ) {
                            // console.log('approve clicked')
                            editor
                                .edit( table.row( { selected: true } ).index(), false )
                                .set( 'claim_status.status', 'approved' )
                                .submit();
                        }
                    },
                ]
            } );

when click on update button. then

An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause'

was shown

marboed

Hi Allan,

thanks very much for your reply.

I have uploaded the files to my dropbox.
javascript file:
https://dropbox.com/s/4mk3ehgr51vx9w0/approveClaim.php?dl=0

server script file:
https://dropbox.com/s/ywypmm12a49vpuq/ApproveClaimModel.php?dl=0

now showing error when "approve" button is pressed:
https://datatables.net/forums/uploads/editor/ln/67ixp4pe40qs.png

fyi: I am able to update field which in one table but not for this case which update value on the join table. please help me conquer this one. thank you very much.

allan

Could you add ->debug( true ) immediately before the ->process( $_POST ) call please? Then when the error occurs, show me the data that is submitted and also the JSON response from the server.

Thanks,
Allan

Mariusz Glugla

Hi. Was this problem resolved?

OscarC

I have exactly the same problem. Did it ever get solved?