DELETE when primary key in leftJoin

zajc

I have primary key cert_vzo_par.id in leftJoin

$data = Editor::inst($db, 'cert_vzo_par', 'id')
...
->leftJoin( 'cvpriv', 'cvpriv.cvpar_id', '=', 'cert_vzo_par.id' )

When I try to DELETE the row in this case is trying to delete also from the view cvpriv.

DELETE FROM cvpriv WHERE cvpriv.cvpar_id = :where_0

Is there a way to avoid deleting from the view in the leftJoin where it is primary key from the main table?

allan

Do you need to submit the value of the joined table's primary key? What is that being used for?

Allan

zajc

The view cvpriv is actually the view created from the table from Mjoin, cert_vzo_pri.

->join(
        Mjoin::inst('cpriv')
            ->link('cert_vzo_par.id', 'cert_vzo_pri.cvpar_id')
            ->link('cert_pri.id', 'cert_vzo_pri.cpri_id')
            ->fields(
                Field::inst('id')
                    ->validator('Validate::required')
                    ->options('cpriv', 'id', 'vrstica'),
                Field::inst('vrstica')
            )
    )

If I replace

->leftJoin( 'cvpriv', 'cvpriv.cvpar_id', '=', 'cert_vzo_par.id' )

to

->leftJoin( 'cert_vzo_pri', 'cert_vzo_pri.cvpar_id', '=', 'cert_vzo_par.id' )

it works, but I need different columns from different tables to show to the costumer not exactly from the cert_vzo_pri. I just wanted to simplify the whole php because it is already 200 lines.

As far I can see the only solution, I'm using Oracle, is to create INSTEAD OF trigger to be able to DELETE the values from the view cvpriv.

I thought only the Mjoin is responsible to DELETE data from the dependant table.

Here is the whole php.

<?php
// Session
include 'lib/db.session.php';

// Placeholder for Select2
include_once 'lib/custom.php';

// DataTables PHP library and database connection
include 'lib/DataTables.php';

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

// Build our Editor instance and process the data coming from _POST
$data = Editor::inst($db, 'cert_vzo_par', 'id')
    ->fields(
                Field::inst('cert_vzo_par.cvzo_id'),
                Field::inst('cert_vzo_par.zap_st'),
                Field::inst('cert_vzo_par.izl'),
                Field::inst('cvpriv.vrstica'),
                                Field::inst('cvpriv.csvrs_ime'),
                               Field::inst('cvpriv.cssor_ime'),
                Field::inst('csstov.vrstica'),
                Field::inst('cert_vzo_par.csnas_id')
                                    ->validator('Validate::notEmpty'),
                               Field::inst('csnasv.vrstica'),
                               Field::inst('cert_vzo_par.cseno_id_obd')
                                      ->options('csenov_108', 'id', 'naziv'),
                                Field::inst('csenov_108.naziv'),
                Field::inst('cert_vzo_par.cssto_id')
                                ->options('csstov', 'id', 'vrstica'),
                Field::inst('csstov2.vrstica'),
                Field::inst('cert_vzo_par.masa'),
                Field::inst('cert_vzo_par.masa2'),
                Field::inst('cert_vzo_par.pak_masa')
                    ->getFormatter(function ($val, $data, $opts) {
                        return str_replace('.', ',', $val);
                    })
                    ->setFormatter(function ($val, $data, $opts) {
                            return str_replace(',', '.', $val);
                        }),
                Field::inst('cert_vzo_par.cseno_id_pak')
                                            ->options('csenov_107', 'id', 'naziv'),
                Field::inst('csenov_107.naziv'),
                Field::inst('cert_vzo_par.pak_masa2')
                    ->getFormatter(function ($val, $data, $opts) {
                        return str_replace('.', ',', $val);
                    })
                    ->setFormatter(function ($val, $data, $opts) {
                            return str_replace(',', '.', $val);
                        }),
                Field::inst('cert_vzo_par.cseno_id_pak2')
                                        ->options('csenov_107', 'id', 'naziv'),
                Field::inst('csenov_107_2.naziv'),
                Field::inst('cert_vzo_par.zap_dat')
                                ->validator('Validate::dateFormat', 'd.m.Y')
                                ->getFormatter('Format::date_sql_to_format', 'd.m.Y')
                                ->setFormatter('Format::date_format_to_sql', 'd.m.Y'),
                Field::inst('cert_vzo_par.cseno_id_zap')
                        ->options('csenov_127', 'id', 'naziv'),
                Field::inst('csenov_127.naziv'),
                Field::inst('cert_vzo_par.cseno_id_vzo')
                        ->options('csenov_129', 'id', 'naziv'),
                Field::inst('csenov_129.naziv'),
                Field::inst('cert_vzo_par.datum')
                                ->validator('Validate::dateFormat', 'm.Y')
                                ->getFormatter('Format::date_sql_to_format', 'm.Y')
                                ->setFormatter('Format::date_format_to_sql', 'm.Y'),
                Field::inst('cert_vzo_par.cseno_id_evr')
                        ->options('csenov_130', 'id', 'naziv'),
                Field::inst('csenov_130.naziv'),
                Field::inst('cert_vzo_par.cseno_id_eob')
                        ->options('csenov_131', 'id', 'naziv'),
                Field::inst('csenov_131.naziv'),
                Field::inst('cert_vzo_par.cseno_id_epi')
                        ->options('csenov_132', 'id', 'naziv'),
                Field::inst('csenov_132.naziv'),
                Field::inst('cert_vzo_par.csnas_id2'),
                                Field::inst('csnasv2.vrstica'),
                Field::inst('cert_vzo_par.opombe'),
                Field::inst('cert_vzo_par.cseno_id_dpo1')
                        ->options('csenov_154', 'id', 'naziv'),
                Field::inst('csenov_154.naziv'),
                Field::inst('cert_vzo_par.cseno_id_dpo2')
                        ->options('csenov_156', 'id', 'naziv'),
                Field::inst('csenov_156.naziv'),
                Field::inst('cert_vzo_par.cseno_id_dpo3')
                        ->options('csenov_155', 'id', 'naziv'),
                Field::inst('csenov_155.naziv'),
                Field::inst('cert_vzo_par.ser_od'),
                Field::inst('cert_vzo_par.ser_do'),
                Field::inst('cert_vzo_par.datum_tis')
                                ->getFormatter('Format::date_sql_to_format', 'd.m.Y')
                                ->setFormatter('Format::date_format_to_sql', 'd.m.Y'),
                Field::inst('cert_vzo_par.pak_st'),
                Field::inst('cert_vzo_par.cert_st')
                                ->validator( function ( $val, $data, $opts ) {
                                    return $val < 150001 && $val < 999999 && $val ?
                                        'Prosim vnesi številko med 150001 in 999999 (leto + zaporedna številka)' :
                                        true;
                                }),
              Field::inst('cert_vzo_par.datum_izd')
                                ->getFormatter('Format::date_sql_to_format', 'd.m.Y')
                                ->setFormatter('Format::date_format_to_sql', 'd.m.Y'),
             Field::inst('cert_vzo_par.cupo_id1')
                        ->options('cupov', 'id', 'naziv'),
             Field::inst('cupov1.naziv'),
                         Field::inst('cert_vzo_par.cupo_id2')
                         ->options('cupov', 'id', 'naziv'),
             Field::inst('cupov2.naziv')
    )
    ->where('cvzo_id', $_POST['p_cvzo_id'])
        ->leftJoin( 'cvpriv', 'cvpriv.cvpar_id', '=', 'cert_vzo_par.id' )
        ->leftJoin( 'csstov', 'csstov.id', '=', 'cvpriv.cssto_id' )
        ->leftJoin( 'csnasv', 'csnasv.id', '=', 'cert_vzo_par.csnas_id' )
        ->leftJoin( 'csstov csstov2', 'csstov2.id', '=', 'cert_vzo_par.cssto_id')
        ->leftJoin( 'csenov_107', 'csenov_107.id', '=', 'cert_vzo_par.cseno_id_pak' )
        ->leftJoin( 'csenov_107 csenov_107_2', 'csenov_107_2.id', '=', 'cert_vzo_par.cseno_id_pak2' )
                 ->leftJoin( 'csenov_108', 'csenov_108.id', '=', 'cert_vzo_par.cseno_id_obd' )
        ->leftJoin( 'csenov_127', 'csenov_127.id', '=', 'cert_vzo_par.cseno_id_zap' )
        ->leftJoin( 'csenov_129', 'csenov_129.id', '=', 'cert_vzo_par.cseno_id_vzo' )
        ->leftJoin( 'csenov_130', 'csenov_130.id', '=', 'cert_vzo_par.cseno_id_evr' )
        ->leftJoin( 'csenov_131', 'csenov_131.id', '=', 'cert_vzo_par.cseno_id_eob' )
        ->leftJoin( 'csenov_132', 'csenov_132.id', '=', 'cert_vzo_par.cseno_id_epi' )
        ->leftJoin( 'csnasv csnasv2', 'csnasv2.id', '=', 'cert_vzo_par.csnas_id2')
        ->leftJoin( 'csenov_154', 'csenov_154.id', '=', 'cert_vzo_par.cseno_id_dpo1' )
        ->leftJoin( 'csenov_156', 'csenov_156.id', '=', 'cert_vzo_par.cseno_id_dpo2' )
        ->leftJoin( 'csenov_155', 'csenov_155.id', '=', 'cert_vzo_par.cseno_id_dpo3' )
        ->leftJoin( 'cupov cupov1', 'cupov1.id', '=', 'cert_vzo_par.cupo_id1' )
        ->leftJoin( 'cupov cupov2', 'cupov2.id', '=', 'cert_vzo_par.cupo_id2' )
        ->join(
        Mjoin::inst('cpriv')
            ->link('cert_vzo_par.id', 'cert_vzo_pri.cvpar_id')
            ->link('cert_pri.id', 'cert_vzo_pri.cpri_id')
            ->fields(
                Field::inst('id')
                    ->validator('Validate::required')
                    ->options('cpriv', 'id', 'vrstica'),
                Field::inst('vrstica')
            )
    )
        ->join(
                Mjoin::inst('csenov_109')
                        ->link('cert_vzo_par.id', 'cert_par_sre.cvpar_id')
                        ->link('csenov_109.id', 'cert_par_sre.cseno_id')
                        ->fields(
                                Field::inst('id')
                                        ->validator('Validate::required')
                                        ->options('csenov_109', 'id', 'naziv'),
                                Field::inst('naziv')
                        )
        )
        ->join(
                Mjoin::inst('csenov_110')
                        ->link('cert_vzo_par.id', 'cert_par_dok.cvpar_id')
                        ->link('csenov_110.id', 'cert_par_dok.cseno_id')
                        ->fields(
                                Field::inst('id')
                                        ->validator('Validate::required')
                                        ->options('csenov_110', 'id', 'naziv'),
                                Field::inst('naziv')
                        )
        )
        ->join(
      Mjoin::inst('csenov_111')
          ->link('cert_vzo_par.id', 'cert_par_ana.cvpar_id')
          ->link('csenov_111.id', 'cert_par_ana.cseno_id')
          ->fields(
              Field::inst('id')
                  ->validator('Validate::required')
                  ->options('csenov_111', 'id', 'naziv'),
              Field::inst('naziv')
          )
  )
    ->join(
        Mjoin::inst('csenov_128')
                ->link('cert_vzo_par.id', 'cert_par_pri.cvpar_id')
                ->link('csenov_128.id', 'cert_par_pri.cseno_id')
                ->fields(
                        Field::inst('id')
                                ->validator('Validate::required')
                                ->options('csenov_128', 'id', 'naziv'),
                        Field::inst('naziv')
                )
    )
      ->process($_POST)
    ->data();


echo json_encode($data);

zajc

I have solved this simply by set(false) to all the cvpriv columns.

Field::inst('cvpriv.vrstica')->set(false),
Field::inst('cvpriv.csvrs_ime')->set(false),
Field::inst('cvpriv.cssor_ime')->set(false),

Now I can use the view instead of the table and DELETE is working as expected.