One-to-many join and Oracle 11g

zajc

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?

zajc

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

allan

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

Allan

zajc

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

allan

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

zajc

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":[]}

zajc

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

zajc

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"' )

allan

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

Allan

zajc

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' )