Join was performed on the field 'linked_trans_id' which was not included in the Editor field list. T

Join was performed on the field 'linked_trans_id' which was not included in the Editor field list. T

dyonysis01dyonysis01 Posts: 7Questions: 1Answers: 0

But been trying to figure this out, and reviewed a number of other forum topics on same/similar problem (in chronological order):

https://datatables.net/forums/discussion/61221
https://datatables.net/forums/discussion/60867
https://datatables.net/forums/discussion/59573
https://datatables.net/forums/discussion/53683

Unfortunately, none of the advise or answers provided in the above threads have helped me. So not sure what I am doing wrong.

In the end, I am trying to write a 1:M query lookup for the following (simplified) SQL statement

SELECT transactions.*
FROM register as register, register as tranasactions
WHERE register.linked_trans_id = transactions.linked_trans_id

You'll notice that this is a self-referencing query, but I am attempting to get all rows that are linked to a particular driving/parent row, with an expectation that I will find more than 1 row.

I first tried a ->leftJoin, but as joins work, this returns all rows from register whether or not they have a linke, or matching, row in transactions (aka register). Which is as expected per Left Outer Join (and as documented in https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/).

So after exloring other SQL re-write options, in the end I need INNER JOIN, which in SQL can be done by either table1 INNER JOIN tabel 2 on ... or the more common format as denoted in above query. And in datatables, ->join(MJoin..., whic has brought me back to the error in question.

Sample code I have for this issue is as follows:

(function($){


$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'controllers/table.sqlTest.php',
        table: '#register',
        fields: [
            { label: "Date:", name: "transactions.transaction_date", type: "readonly", format: "YYYY-MM-DD" },
            { label: "Chq:", name: "transactions.chq_num" },
            { label: "Payee:", name: "transactions.payee" },
            { label: "Memo:", name: "transactions.memo" },
            { label: "Notes:", name: "transactions.notes" },
        ]
    } );

    var table = $('#register').DataTable( {
        dom: 'Bfrtip',
        ajax: 'controllers/table.sqlTest.php',
        columnDefs: [
        ],
        columns: [
            /* 0  = sel  */ { data: null, defaultContent: '', className: 'select-checkbox', orderable: false },
            /* 1  = ctrl */ { data: null, defaultContent: '', className: 'details-control', orderable: false },
            /* 2  = acct */ { data: "account.account_name", editField: "transactions.account_id" },
            /* 3  = date */ { data: "transactions.transaction_date" },
            /* 4  = chq  */ { data: "transactions.chq_num" },
            /* 5  = paye */ { data: "transactions.payee" },
            /* 6  = memo */ { data: "transactions.memo" },
            /* 7 = tid  */ { data: "transactions.id" },
        ],
        select: true,
        buttons: [
            { extend: 'edit', text:'Edit',  editor: editor },
            { extend: 'remove', editor: editor }
        ]

    } );

    // Add event listener for opening and closing details
    $('#register tbody').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = table.row( tr );

        // Show/hide the child row, also adorn the class to allow for the right button be display (by HTML)
        // So, in theory, I could ahve multiple columns each with its own trigger button.  Each showing differnt child rows
        if ( row.child.isShown() ) { row.child.hide(); tr.removeClass('shown'); }
        else { row.child( format(row.data()) ).show(); tr.addClass('shown'); }

    });

});

// Sub/Detail row, displayed when you click "+"
function format ( d ) {
    // `d` is the original data object for the row
    return '<table id="transactionDetail" cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
        '<tr>'+
            '<td>Account:</td>'+
            '<td name="detail_account">'+d.account['account_name']+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Note</td>'+
            '<td name="detail_notes" class="detail_notes">'+d.transactions['notes']+'</td>'+
        '</tr>'+
    '</table>';
}


}(jQuery));
<?php

/*
 * Editor server script for DB table transaction
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "../lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Database,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
$out = Editor::inst( $db, 'register as transactions', 'id' )
    ->fields(
        Field::inst( 'transactions.account_id' ),
        Field::inst( 'account.account_name' ),
        Field::inst( 'transactions.transaction_date' )
            ->set( Field::SET_NONE )
            ->validator( Validate::dateFormat( 'Y-m-d' ) )
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) ),
        Field::inst( 'transactions.transaction_type' ),
        Field::inst( 'transactions.chq_num' ),
        Field::inst( 'transactions.payee' ),
        Field::inst( 'transactions.memo' ),
        Field::inst( 'transactions.transaction_amount' )
            // Format the number as a negative if the transation is a debit, instead of a credit.
            ->set( Field::SET_NONE )
            ->getFormatter( function( $value, $row) {
                return $row['transactions.transaction_type'] == 'DEBIT' ? $value*-1:$value;
            }),
        Field::inst( 'transactions.notes' ),
        Field::inst( 'transactions.id' ) ->set( Field::SET_NONE ),
        Field::inst( 'transactions.linked_trans_id' ) ->set( Field::SET_NONE ),
    )
    ->join( MJoin::inst ('register')
            ->link('register.id','transactions.linked_trans_id')
            // ->field(
            //  Field::inst( 'payee', 'register_payee')
            //  ->set( Field::SET_NONE ),
            //  Field::inst( 'memo', 'regsiter_memo')
            //  ->set( Field::SET_NONE )
            // )
            //->where('transactions.linked_tran_id','transaction_linked.link_tran_id', '=')
    )
    ->leftJoin( 'account', 'account.id', '=', 'transactions.account_id' )
    ->process( $_POST )
    ->json();

If I remove the ->join line, I get the all rows back, which is to be expected. So this tells me that at least the PHP/JS/HTML is well formed enough. But when I add in the join again, I get the error persists/reoccurs.

  • Eliot

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    Hi Eliot,

    The order shouldn't matter, but just in case, could you try:

    ->link('transactions.linked_trans_id', 'register.id')
    

    please?

    Thanks,
    Allan

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    Actually looking it it more, I think it might be the register as transactions that is tripping it up. Can you try it with just register and update the field names accordingly?

    Allan

  • dyonysis01dyonysis01 Posts: 7Questions: 1Answers: 0

    Hi Allan,

    The order shouldn't matter, but just in case, could you try:
    ->link('transactions.linked_trans_id', 'register.id')

    I tried reversing the order. In fact, this was the original order of the parameters, which I reversed based on a suggestion in one of the other threads I mentioned. Unfortunately, sam,e error msg, so no luck

    Actually looking it it more, I think it might be the register as transactions that is tripping it up

    I thought about this, and tried again. Same issue.
    I then also tried using ->aliasParentTable, again no luck.

    Do note that I am joining two instances of register table. So wouldn't removing the table alias cause confusion? Or does the join routine know which table is which?

  • dyonysis01dyonysis01 Posts: 7Questions: 1Answers: 0

    Hi Allen,

    Think I am on to something here. Unfortunately, it does not apepar to help me much (ie: does not provide me with data I can use), but it might hint toward a root casue for this error at least.

    I did a search of code for the error message "Join was performed on the field" And found the folowing comment starting line 549 of DataTables\Editor\Join.

    Check that the joining field is available. The joining key can
    come from the Editor instance's primary key, or any other field,
    including a nested value (from a left join). If the instance's
    pkey, then we've got that in the DT_RowId parameter, so we can
    use that. Otherwise, the key must be in the field list

    While the second highlighted statement is what I was trying for, and what we all expect. The first highlighted statement trigger a thought. What the join was required to be on the primary key and not just any field int he field list. So I tried flipping my join to use the primary key off transactions as follows.

    $out = Editor::inst( $db, 'register as transactions', 'id' )
    :
    ->join( MJoin::inst ('register')
       ->link('register.linked_trans_id','transactions.id')
    )
    

    The result was, no more error. Note: It was not the order of the paramters as you noted earlier, rather a change to which field the joning tables are keying off. However, this is a fundamental change to the underlying SQL statement:

    from

    select transactions.* 
    from register as transactions, register as register  
    where transactions.linked_trans_id = register.id;
    

    to

    select transactions.* 
    from register as transactions, register as register  
    where register.linked_trans_id = transactions.id; <==***
    

    The difference being returning three child rows based on the child-parent link to register versus returning the parent row three times based on the parent-child linked from register.

    In the end, it would appear you can not join/multi-join on any field in the field list. Rather the joining table must be against the parent table's primary key. Put another way according to comment on line 549, not "Otherwise, the key must be in the field list"... ?

  • dyonysis01dyonysis01 Posts: 7Questions: 1Answers: 0

    After a little more testing and validating on this topic, I noticed one other things about ->join that I wasnt expected.

    I seems that ->join does the same as ->leftJoin. That is, it does an LEFT OUTER JOIN.

    Maybe this is sproduct on the issue I am having with join as above. But when I altered the join keys to use the parents primary key id, instead of only getting 3 rows (per my data), I got all rows where all rows had NULL values (ie: did not match) for rows in the joined table regsiter. PLus the 3 rows with a match. Ie: Left Outer Join

    Armed with this in mind, I can get what I want by changing to a leftJoin with a wheer clause.

        )
        // ->join( Join::inst ('register', 'id')
        //          ->link('transactions.id', 'register.linked_trans_id')
        //      ->field(
        //          Field::inst( 'linked_trans_id', 'register_linked_trans_id')
        //          ->set( Field::SET_NONE ),
        //          Field::inst( 'payee', 'register_payee')
        //          ->set( Field::SET_NONE ),
        //          Field::inst( 'memo', 'register_memo')
        //          ->set( Field::SET_NONE )
        //      )
        // )
        ->leftJoin( 'register as register', 'register.id', '=', 'transactions.linked_trans_id' )
        ->leftJoin( 'account', 'account.id', '=', 'transactions.account_id' )
        ->where( 'register.linked_trans_id', null, "!=" )
        ->process( $_POST )
        ->json();
    

    Basically changing my approach:

    from (inner join)

    select transactions.* 
    from register as transactions, register as register  
    where transactions.linked_trans_id = register.id;
    

    to (outer join with where clause filter)

    select transactions.* 
    from register as transactions 
        left outer join register on transactions.linked_trans_id = register.id
    where transactions.linked_trans_id is not null
    
  • dyonysis01dyonysis01 Posts: 7Questions: 1Answers: 0

    Hi @allan

    I do appreciate your guidance and assistance. Your suggestion did lead me to an understanding that allowed me alter code to accomplish same desired outcome.

    I know this queue is busy. But I just wanted to following up and wondering if there s any thought of insioght into a possible issue in Join php where you are not able to join-link on a non primary key field? (ref starting with my second followup post above, with "Im on to something").

    Even though I went a different direction ansd resolved my issue, future endeavors may return back to Join/MJoin, and linking based on Field values and not only primary keys may be more desired.

    Thanks
    Eliot

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    Hi Eliot,

    Apologies for the delay in getting back to you here! It absolutely should be possible to use a field other than the primary key to do the join on.

    I seems that ->join does the same as ->leftJoin. That is, it does an LEFT OUTER JOIN.

    Not in SQL. The way Mjoin (which is an alias of the legacy Join class in PHP) works is to perform a second query on the Mjoined table, allowing us to do the array join in PHP. It is possible to make the Join class do an object based join rather than array, but that is redundant due to the support of leftJoin.

    Could you try the following please?

    $out = Editor::inst( $db, 'register', 'id' )
        ->fields(
            Field::inst( 'register.account_id' ),
            Field::inst( 'account.account_name' ),
            Field::inst( 'register.transaction_date' )
                ->set( Field::SET_NONE )
                ->validator( Validate::dateFormat( 'Y-m-d' ) )
                ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) ),
            Field::inst( 'register.transaction_type' ),
            Field::inst( 'register.chq_num' ),
            Field::inst( 'register.payee' ),
            Field::inst( 'register.memo' ),
            Field::inst( 'register.transaction_amount' )
                // Format the number as a negative if the transation is a debit, instead of a credit.
                ->set( Field::SET_NONE )
                ->getFormatter( function( $value, $row) {
                    return $row['register.transaction_type'] == 'DEBIT' ? $value*-1:$value;
                }),
            Field::inst( 'register.notes' ),
            Field::inst( 'register.id' ) ->set( Field::SET_NONE ),
            Field::inst( 'register.linked_trans_id' ) ->set( Field::SET_NONE )
        )
        ->join( MJoin::inst ('register as transactions')
                ->link('transactions.id','register.linked_trans_id')
                // ->field(
                //  Field::inst( 'payee', 'register_payee')
                //  ->set( Field::SET_NONE ),
                //  Field::inst( 'memo', 'regsiter_memo')
                //  ->set( Field::SET_NONE )
                // )
                //->where('transactions.linked_tran_id','transaction_linked.link_tran_id', '=')
        )
        ->leftJoin( 'account', 'account.id', '=', 'register.account_id' )
        ->debug(true)
        ->process( $_POST )
        ->json();
    

    Even if there is an error, can you show me the response from the server? I've basically just swapped the table names and aliases around. I think it was struggling with the alias to the self join.

    Thanks,
    Allan

  • dyonysis01dyonysis01 Posts: 7Questions: 1Answers: 0

    HI Allen,

    Sorry, my turn to be slow in repsonding. I made the suggested changes but am still getting same response.

    Pop up dialog (eror msg):

    DataTables warning: table id=register - Join was performed on the field 'linked_trans_id' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance.
    

    JSON

    {"sError":"Join was performed on the field 'linked_trans_id' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance."}
    

    Chrome DEv Tools (console log - this might be unrelated, but including)

    jquery.dataTables.min.js:62 Uncaught TypeError: Cannot read property 'length' of undefined
        at jquery.dataTables.min.js:62
        at k (jquery.dataTables.min.js:48)
        at Object.success (jquery.dataTables.min.js:49)
        at fire (jquery-3.3.1.js:3268)
        at Object.fireWith [as resolveWith] (jquery-3.3.1.js:3398)
        at done (jquery-3.3.1.js:9305)
        at XMLHttpRequest.<anonymous> (jquery-3.3.1.js:9548)
    

    From here, I tried a few other things, just on hunches and whims.:

    1) Added the following field to the main Editorfield list.

    Editor::inst( $db, 'register', 'id' )
        ->fields(
                ...
                Field::inst( 'transactions.linked_trans_id' ) ->set( Field::SET_NONE )`
        )
    

    Granted, this sholdnt be required, but I working off a remote thought that it was looking for the wrong linked_tran_id field. That is, that it was expecting to find the linked_tran_id from the joined query for transactions, not register.... yes, a long shot.

    Error mesasge received:

    "fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42P01]: Undefined table: 7 ERROR:  missing FROM-clause entry for table \"transactions\"\nLINE 1: ...er.linked_trans_id as \"register.linked_trans_id\", transactio...\n
    

    But this time if produced a query in the JSON

    SELECT  register.id as \"register.id\", 
        register.account_id as \"register.account_id\", 
        account.account_name as \"account.account_name\", 
        register.transaction_date as \"register.transaction_date\", 
        register.transaction_type as \"register.transaction_type\", 
        register.chq_num as \"register.chq_num\", 
        register.payee as \"register.payee\", 
        register.memo as \"register.memo\", 
        register.transaction_amount as \"register.transaction_amount\", 
        register.notes as \"register.notes\", 
        register.linked_trans_id as \"register.linked_trans_id\", 
        transactions.linked_trans_id as \"transactions.linked_trans_id\" 
    FROM  register LEFT JOIN account ON account.id = register.account_id "
    

    Based on the above SQL I am not surprised at the error message. Nor would I be surprised as it is not valid to access a field in a sub query (assuming it is a sub query).
    However, I was a little taken that the Mjoin is not a "join", but you did said it is a

    Mjoin ... works is to perform a second query on the Mjoined table

    So, back to not surprised.

    2) I tried aliases the field register.linked_trans_id, various ways. -->name(), "as linked_trans_id". etc... the thought here was that the query/join routine is looking for linked_trans_id in the field list and not register.linked_trans_id.... same results as above ( ie: I think I was not able to get the field aliasing to work ).

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    Could you possibly send me a dump of your register and account SQL tables please? The schema alone would be fine, but if you are able to include some dummy data that would be even better. That was I can try to recreate it exactly here.

    I feel that this should be working, so there is a bug here, unless I'm missing something!

    Allan

  • dyonysis01dyonysis01 Posts: 7Questions: 1Answers: 0

    Hi Allan, sent you a DM with the attached schemas as requested.

This discussion has been closed.