0

I try to make condition where i delete data before insert data to prevent multiple data but it didnt work | even the data in table become empty || 'id_lokasi' is my column name in table - database

$factpelanggan4 = DB::connection('clickhouse')
        ->select("SELECT t.id_tahun, l.id_lokasi, stage.jml_pelanggan
    from dim_tahun t
    inner join stage_pelanggan stage on t.tahun = stage.years
    inner join dim_lokasi l on stage.city_id = l.id_kota
    WHERE (stage.country_id = l.id_negara)
    ORDER BY t.id_tahun ASC , l.id_lokasi ASC, stage.jml_pelanggan ASC
    ");
    
    foreach ($factpelanggan4 as $value) {
        $tahun_id[] = $value['t.id_tahun'];
        $lokasi_id[] = $value['l.id_lokasi'];
        $jml[] = $value['stage.jml_pelanggan'];
    }
    DB::connection('clickhouse')->table('fakta_pelanggan')->where('id_lokasi', $lokasi_id)->delete();
    //DB::connection('clickhouse')->table('fakta_pelanggan')->where('id_lokasi', Not Null)->delete();

    foreach ($factpelanggan4 as $key => $value) {
        $arrayInsert = array(
            'id_tahun' => $tahun_id[$key],
            'id_lokasi' => $lokasi_id[$key],
            'jml_pelanggan' => $jml[$key],
        );
        DB::connection('clickhouse')->table('fakta_pelanggan')->insert($arrayInsert);
        $key++;
    }
Ronaldo Firmansyah
  • 23
  • 7
  • I don't think `DB::connection('clickhouse')->table('fakta_pelanggan')` does what you think it does. It's used with the querybuilder. It will be an object and won't be null for your first if statement. Thus it will keep on running your else if logic and delete where id_lokasi is $lokasi_id – Kevin Y Jul 19 '22 at 03:09
  • Yes, thank you. I modify it already as directly delete the data with 'where' condition ( i'll update my source code above ) it works, but another problem is by that condition, it will not delete the data if the data was inserted manually by database management because it has different $id_lokasi i try to use 'NOT NULL' but it doesnt detected by Laravel – Ronaldo Firmansyah Jul 19 '22 at 03:22
  • Does this answer your question? [How do you check "if not null" with Eloquent?](https://stackoverflow.com/questions/21281504/how-do-you-check-if-not-null-with-eloquent) – Kevin Y Jul 19 '22 at 03:25
  • 1
    Preventing duplicates would usually be done on the DB level, namely by having so-called `Unique Constraints` so that `PHP` would not have much to do with it: https://www.ecosia.org/search?q=db%20Unique%20Constraints – Stefan Wuebbe Jul 19 '22 at 03:26
  • thank you, sir i'll try it Because basically im in learn using laravel command and run all the program with 1 command only :D – Ronaldo Firmansyah Jul 19 '22 at 03:38
  • i think the solutions from the link that Mr.Kevin sent is not the same anymore as its Laravel 9 now, i tried some of those but it didnt work | and from Mr.Stefan i dont really understand because i dont create table or using pure SQL Code Program since make-running program in Laravel Command only | But I still thankful for your helps – Ronaldo Firmansyah Jul 19 '22 at 04:08
  • What are your primary and unique keys? You might just be looking for a firstOrCreate or updateOrCreate: https://stackoverflow.com/questions/18839941/creating-and-update-laravel-eloquent or even an upsert: https://laravel.com/docs/9.x/eloquent#upserts – Kevin Y Jul 19 '22 at 04:18
  • my primary key is $id_lokasi , with the source code above it works deleting data and insert data after to rpevent the same/multiple data | but because of the 'where' condition, if i insert data manually with different $id_lokasi, it will not delete it | because will only delete where $id_lokasi = 'id_lokasi' – Ronaldo Firmansyah Jul 19 '22 at 04:27
  • Example insert data from running command Laravel $id_lokasi(1) = 'id_lokasi(1)' > will delete || Example input-manually i insert data with $id_lokasi(2) > will not delete because the delete will only run if $id_lokasi has value as (1) || sorry if my typing looks terible – Ronaldo Firmansyah Jul 19 '22 at 04:31
  • i try to use truncate() to delete all the data even the $id_lokasi is not the same but still error – Ronaldo Firmansyah Jul 19 '22 at 04:33

1 Answers1

1

You should avoid deleting if you're just inserting, or deleting then inserting. If something goes wrong, you'd be left with no data similar to a truncate. What you really should be doing is inserting or updating.

You can do this manually, selecting, then either inserting or updating, separately. Or you can use firstOrCreate, firstOrNew, updateOrCreate or similar approaches. Since you're working on multiple records at once, you might try an upsert for updating/inserting multiple records at once:
https://laravel.com/docs/9.x/eloquent#upsert

$toInsertOrUpdate = [];
foreach ($factpelanggan4 as $key => $value) {
    // I don't quite follow your looping logic.
    // What you wrote with $key++ after each loop before
    // will be completely ignored since each loop overwrites it.
    // This is a factored version of what you wrote before.
    // Please double check it to make sure it does what you want.
    $toInsertOrUpdate[] = [
        'id_tahun' => $tahun_id[$key],
        'id_lokasi' => $lokasi_id[$key],
        'jml_pelanggan' => $jml[$key],    
    ];
}


// these columns together identify duplicate records.
$uniqueIdentifyingColumns = ['id_tahun','id_lokasi','jml_pelanggan'];

// when a duplicate record is found, only these columns will be updated
$columnsToUpdateOnDuplicate = ['id_tahun','id_lokasi','jml_pelanggan'];

$tbl = DB::connection('clickhouse')->table('fakta_pelanggan');

$tbl->upsert(
    $toInsertOrUpdate,
    $uniqueIdentifyingColumns,
    $columnsToUpdateOnDuplicate
);
Kevin Y
  • 646
  • 5
  • 18