DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

One-to-many join and Oracle 11g

One-to-many join and Oracle 11g

zajczajc Posts: 67Questions: 10Answers: 2
edited February 2016 in Editor

I have this simple one-to-many example for Editor 1.5.6.

$data = Editor::inst($db, 'cert_pri', 'cpri_id')
    ->fields(
        Field::inst('cert_pri.cpri_id'),
        Field::inst('cert_pri.cpri_st_prijave')
            ->validator('Validate::notEmpty'),
        Field::inst('cert_pri.cpri_leto_prijave')
            ->validator('Validate::notEmpty')
    )
    ->join(
        Mjoin::inst('cert_sif_pri')
            ->link('cert_pri.cpri_id', 'cert_pri_pri.cppri_cpri_id')
            ->link('cert_sif_pri.cspri_id', 'cert_pri_pri.cppri_cspri_id')
            ->fields(
                Field::inst('cspri_id')
                    ->validator('Validate::required')
                    ->options('cert_sif_pri', 'cspri_id', 'cspri_naziv'),
                Field::inst('cspri_naziv')
            )
    )
    ->process($_POST)
    ->data();

If I use MySQL database I get this JSON and it works fine:

{"data":[{"DT_RowId":"row_9","cert_pri":{"cpri_id":"9","cpri_st_prijave":"1","cpri_leto_prijave":"2015"},"cert_sif_pri":[]},{"DT_RowId":"row_5","cert_pri":{"cpri_id":"5","cpri_st_prijave":"1","cpri_leto_prijave":"2016"},"cert_sif_pri":[{"cspri_id":"3","cspri_naziv":"ISTA"}]},{"DT_RowId":"row_10","cert_pri":{"cpri_id":"10","cpri_st_prijave":"2","cpri_leto_prijave":"2015"},"cert_sif_pri":[{"cspri_id":"3","cspri_naziv":"ISTA"}]},{"DT_RowId":"row_7","cert_pri":{"cpri_id":"7","cpri_st_prijave":"2","cpri_leto_prijave":"2016"},"cert_sif_pri":[{"cspri_id":"1","cspri_naziv":"EU"},{"cspri_id":"2","cspri_naziv":"OECD"},{"cspri_id":"4","cspri_naziv":"Potrdilo o potrditvi"},{"cspri_id":"5","cspri_naziv":"Potrdilo o nedokon\u010dni potrditvi"}]},{"DT_RowId":"row_8","cert_pri":{"cpri_id":"8","cpri_st_prijave":"3","cpri_leto_prijave":"2016"},"cert_sif_pri":[{"cspri_id":"2","cspri_naziv":"OECD"},{"cspri_id":"4","cspri_naziv":"Potrdilo o potrditvi"},{"cspri_id":"5","cspri_naziv":"Potrdilo o nedokon\u010dni potrditvi"}]},{"DT_RowId":"row_14","cert_pri":{"cpri_id":"14","cpri_st_prijave":"4","cpri_leto_prijave":"2016"},"cert_sif_pri":[{"cspri_id":"2","cspri_naziv":"OECD"}]}],"options":{"cert_sif_pri[].cspri_id":[{"label":"EU","value":"1"},{"label":"ISTA","value":"3"},{"label":"OECD","value":"2"},{"label":"Potrdilo o nedokon\u010dni potrditvi","value":"5"},{"label":"Potrdilo o potrditvi","value":"4"}]},"files":[]}

If I connect to Oracle database I got this error and JSON which doesn't get data from the Mjoin (empty).

<b>Notice</b>:  Undefined index: dteditor_pkey in <b>/var/www/zajc.xyz/sup/php/lib/Editor/Join.php</b> on line <b>509</b>

{"data":[{"DT_RowId":"row_5","cert_pri":{"cpri_id":"5","cpri_st_prijave":"1","cpri_leto_prijave":"2016"},"cert_sif_pri":[]},{"DT_RowId":"row_7","cert_pri":{"cpri_id":"7","cpri_st_prijave":"2","cpri_leto_prijave":"2016"},"cert_sif_pri":[]},{"DT_RowId":"row_8","cert_pri":{"cpri_id":"8","cpri_st_prijave":"3","cpri_leto_prijave":"2016"},"cert_sif_pri":[]},{"DT_RowId":"row_9","cert_pri":{"cpri_id":"9","cpri_st_prijave":"1","cpri_leto_prijave":"2015"},"cert_sif_pri":[]},{"DT_RowId":"row_10","cert_pri":{"cpri_id":"10","cpri_st_prijave":"2","cpri_leto_prijave":"2015"},"cert_sif_pri":[]},{"DT_RowId":"row_14","cert_pri":{"cpri_id":"14","cpri_st_prijave":"4","cpri_leto_prijave":"2016"},"cert_sif_pri":[]}],"options":{"cert_sif_pri[].cspri_id":[{"label":"EU","value":"1"},{"label":"ISTA","value":"3"},{"label":"OECD","value":"2"},{"label":"Potrdilo o nedokon\u010dni potrditvi","value":"5"},{"label":"Potrdilo o potrditvi","value":"4"}]},"files":[]}

Can you help, please?

Replies

  • zajczajc Posts: 67Questions: 10Answers: 2
    edited February 2016

    I just changed the part in /Editor/join.php

    ->table( $dteTable .' as '. $dteTableLocal );

    to

    ->table( $dteTable .' '. $dteTableLocal );

    beacuse Oracle doesn't create table aliases with 'AS'.

    I have checked Oracle logs and the SELECTS are all right... But still "JSON invalid response" is shown.

    Oracle get 3 selects similar as MySql:

    SELECT cpri_id AS "cpri_id",
       cert_pri.cpri_id AS "cert_pri.cpri_id",
       cert_pri.cpri_st_prijave AS "cert_pri.cpri_st_prijave",
       cert_pri.cpri_leto_prijave AS "cert_pri.cpri_leto_prijave"
  FROM cert_pri

    SELECT cpri_id AS dteditor_pkey, cert_sif_pri.cspri_id AS cspri_id, cert_sif_pri.cspri_naziv AS cspri_naziv
  FROM cert_pri cert_pri
       JOIN cert_pri_pri
          ON cert_pri.cpri_id = cert_pri_pri.cppri_cpri_id
       JOIN cert_sif_pri
          ON cert_sif_pri.cspri_id = cert_pri_pri.cppri_cspri_id

    SELECT DISTINCT cspri_id AS "cspri_id", cspri_naziv AS "cspri_naziv" FROM cert_sif_pri
  • allanallan Posts: 65,365Questions: 1Answers: 10,845 Site admin

    I've not had a chance to look into this today - sorry. I'll try to do so tomorrow.

    Allan

  • zajczajc Posts: 67Questions: 10Answers: 2

    I would appreciate your help. I'm stuck here.

  • allanallan Posts: 65,365Questions: 1Answers: 10,845 Site admin

    I'm struggling to reproduce this here I'm afraid. In the Join.php file could you find this block:

                if ( $this->_type === 'object' ) {
                $join[ $row['dteditor_pkey'] ] = $inner;
            }

    and immediately before it add print_r( $row );.

    The JSON returned from the server will be invalid still, but it will let us see what the data for each row is. I'm not understanding why dteditor_pkey is not being found since it is in the field list of the SQL.

    Oracle allows the use of AS to create an alias (equally it also will create an alias without it), so I don't think that is the issue here.

    Thanks,
    Allan

  • zajczajc Posts: 67Questions: 10Answers: 2

    This is what I get:

    Array ( [DTEDITOR_PKEY] => 5 [CSPRI_ID] => 3 [CSPRI_NAZIV] => ISTA ) 
Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 510

Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 511

Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 513
Array ( [DTEDITOR_PKEY] => 7 [CSPRI_ID] => 1 [CSPRI_NAZIV] => EU ) 
Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 510

Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 513
Array ( [DTEDITOR_PKEY] => 7 [CSPRI_ID] => 2 [CSPRI_NAZIV] => OECD ) 
Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 510

Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 513
Array ( [DTEDITOR_PKEY] => 7 [CSPRI_ID] => 4 [CSPRI_NAZIV] => Potrdilo o potrditvi ) 
Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 510

Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 513
Array ( [DTEDITOR_PKEY] => 7 [CSPRI_ID] => 5 [CSPRI_NAZIV] => Potrdilo o nedokončni potrditvi ) 
Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 510

Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 513
Array ( [DTEDITOR_PKEY] => 8 [CSPRI_ID] => 2 [CSPRI_NAZIV] => OECD ) 
Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 510

Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 513
Array ( [DTEDITOR_PKEY] => 8 [CSPRI_ID] => 4 [CSPRI_NAZIV] => Potrdilo o potrditvi ) 
Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 510

Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 513
Array ( [DTEDITOR_PKEY] => 8 [CSPRI_ID] => 5 [CSPRI_NAZIV] => Potrdilo o nedokončni potrditvi ) 
Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 510

Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 513
Array ( [DTEDITOR_PKEY] => 10 [CSPRI_ID] => 3 [CSPRI_NAZIV] => ISTA ) 
Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 510

Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 513
Array ( [DTEDITOR_PKEY] => 14 [CSPRI_ID] => 2 [CSPRI_NAZIV] => OECD ) 
Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 510

Notice: Undefined index: dteditor_pkey in /var/www/zajc.xyz/sup/php/lib/Editor/Join.php on line 513
{"data":[{"DT_RowId":"row_5","cert_pri":{"cpri_id":"5","cpri_st_prijave":"1","cpri_leto_prijave":"2016"},"cert_sif_pri":[]},{"DT_RowId":"row_7","cert_pri":{"cpri_id":"7","cpri_st_prijave":"2","cpri_leto_prijave":"2016"},"cert_sif_pri":[]},{"DT_RowId":"row_8","cert_pri":{"cpri_id":"8","cpri_st_prijave":"3","cpri_leto_prijave":"2016"},"cert_sif_pri":[]},{"DT_RowId":"row_9","cert_pri":{"cpri_id":"9","cpri_st_prijave":"1","cpri_leto_prijave":"2015"},"cert_sif_pri":[]},{"DT_RowId":"row_10","cert_pri":{"cpri_id":"10","cpri_st_prijave":"2","cpri_leto_prijave":"2015"},"cert_sif_pri":[]},{"DT_RowId":"row_14","cert_pri":{"cpri_id":"14","cpri_st_prijave":"4","cpri_leto_prijave":"2016"},"cert_sif_pri":[]}],"options":{"cert_sif_pri[].cspri_id":[{"label":"EU","value":"1"},{"label":"ISTA","value":"3"},{"label":"OECD","value":"2"},{"label":"Potrdilo o nedokon\u010dni potrditvi","value":"5"},{"label":"Potrdilo o potrditvi","value":"4"}]},"files":[]}
  • zajczajc Posts: 67Questions: 10Answers: 2
    edited February 2016

    I guess the problem is that dteditor_pkey is UPPERCASE, maybe yajra/laravel-pdo-via-oci8 issue!?

  • zajczajc Posts: 67Questions: 10Answers: 2
    edited February 2016

    It could easily be solved by changing this in Join.php and it should also work in MySql too.

    ->get( $joinField.' as dteditor_pkey' )

    to

    ->get( $joinField.' as "dteditor_pkey"' )
  • allanallan Posts: 65,365Questions: 1Answers: 10,845 Site admin

    Interesting! Yes, for the moment add the double quotes. I need to see if I can have that correctly escaped in the driver.

    Allan

  • zajczajc Posts: 67Questions: 10Answers: 2

    In Join.php between

    while ( $row=$res->fetch() ) {
    $inner = array();

    the array keys should be lowercase otherwise it doesn't work. I solve this with function array_change_key_case

    while ( $row=$res->fetch() ) {
    $row=array_change_key_case($row, CASE_LOWER );//Oracle driver "bug" workaround
    $inner = array();

    Also I remove double quotes I suggested in previous post back to "original" code.

    ->get( $joinField.' as dteditor_pkey' )
This discussion has been closed.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2025 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.