LARAVEL DATABASE QUERY BUILDER PART-3: Insert, Update, Delete, Pessimistic Locking and Delete

ইতিমধ্যেই আমরা জেনেছি Laravel Query Builder হল লারাভেলে ডাটাবেস কোয়েরি তৈরির জন্য একটি সুবিধাজনক টুল। এটি আমাদেরকে জটিলসব SQL Query গুলোকে একটি সাবলীল এবং চেইনযোগ্য ইন্টারফেস ব্যবহার করে একটি সহজ এবং সহজ উপায়ে বিভিন্ন SQL Query তৈরি করতে দেয়। Laravel Query Builder ব্যবহার করে, আপনি raw SQL statement গুলো না লিখেই বিভিন্ন ডাটাবেস অপারেশন করতে পারেন, যেমন retrieving data, inserting data, updating data, এবং deleting data. উপরন্তু, Laravel Query Builder স্বয়ংক্রিয়ভাবে user input গুলো escapes যায়, যা SQL injection attacks প্রতিরোধ করতে সাহায্য করে। আজকের পর্বে আমরা Laravel Query Builder ব্যবহার করে কিভাবে insert, update, এবং delete এর কাজগুলো করা হয় , তা দেখব।

এই পোস্টের উদাহরণ গুলো বুঝার সুবিধার্তে নিম্নোক্ত users table এর structure এবং Data এর SQL টুকু রান করে নিন :

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `users_email_unique` (`email`);

Insert Statements

Insert Single Row

লারাভেলে, আপনি Query Builder এর insert method ব্যবহার করে একটি ডাটাবেস টেবিলে ডেটা insert করতে পারেন:

use Illuminate\Support\Facades\DB;

Route::get('/insert', function () {
    $data = [
        'name' => 'John Doe',
        'email' => 'john4@example.com',
        'password' => bcrypt('secret'),];
    
     if(DB::table('users')->insert($data)){
        dd(DB::table('users')->get());
     }
});

এই উদাহরণে, $data array তে field এর নাম গুলো এবং তাদের নিজ নিজ value রয়েছে যা আপনি “users” টেবিলে insert করতে চান। insert method ডেটার একটি array গ্রহণ করে এবং সংশ্লিষ্ট টেবিলে নির্দিষ্ট করা ডাটাবেস টেবিলে এটি insert করায়।

Insert Multiple Row at a time

আবার আপনি একটি array এর মধ্যে একাধিক অ্যারে পাস করে একসাথে অনেকগুলো রেকর্ড insert করতে পারেন। প্রতিটি অ্যারে একেকটি রো হিসেবে টেবিলে Insert হবে।

use Illuminate\Support\Facades\DB;

Route::get('/insert', function () {
    $data = [
                [ 'name' => 'Soriful Islam', 'email' => 'soriful@gmail.com', 'password' => bcrypt('secret')],
                [ 'name' => 'Aminul Islam', 'email' => 'aminul@gmail.com', 'password' => bcrypt('secret')],
                [ 'name' => 'MarYam Masud', 'email' => 'maryam@gmail.com', 'password' => bcrypt('secret')],
            ];
    
    if(DB::table('users')->insert($data)){
    dd(DB::table('users')->get()); 
    }

insertOrIgnore method

অনেক সময় MySQL Database এ ডেটা Insert করার সময় MySQL এর strict mode এর দরুন কিছু error তৈরি হয় ,আর এই error গুলোকে Ignore বা avoid করার জন্য আমরা insertOrIgnore Method ব্যবহার করতে পারি। এই Method টি আপনার ডুপ্লিকেট রেকর্ড এর জন্য যে error গুলিদেখাতো সেগুলোও Ignore করা হবে এবং ডাটাবেস ইঞ্জিনের উপর নির্ভর করে অন্যান্য ধরণের ত্রুটিগুলিও Ignore করতে পারে৷ সহজে বলাযায় InsertOrIgnore MySQL Method এর MySQL’s strict mode কে বাইপাস করবে:

use Illuminate\Support\Facades\DB;

Route::get('/insert', function () {
    $data = [
        'name' => 'John Doe',
        'email' => 'john4@example.com',
        'password' => bcrypt('secret'),];
    
     if(DB::table('users')->insertOrIgnore($data)){
        dd(DB::table('users')->get());
     }
});

insertUsing Method

আপনি যদি subquery ব্যবহার করে এক Table থেকে ডেটা এনে অন্য টেবিলে record insert করতে চান। এবং উক্ত টেবিলে রেকর্ডটি Insert হয়েছে তা নিশ্চিত করতে চান , তাহলে আপনাকে insertUsing Method টি ব্যবহার করতে হবে।

use Illuminate\Support\Facades\DB;

Route::get('/getdata', function () {
   $insert= DB::table('users2')->insertUsing(
    ['id', 'name', 'email', 'email_verified_at','password'],
    DB::table('users')
    ->select('id', 'name', 'email', 'email_verified_at','password'));
    
     if($insert){
        dd(DB::table('users2')->get());
     }
});

এছাড়াও আপনি চাইলে নিম্নোক্ত উপায়ে where Clause ব্যবহার করতে পারেন :

use Illuminate\Support\Facades\DB;

Route::get('/getdata', function () {
   $insert= DB::table('users2')->insertUsing(
    ['id', 'name', 'email', 'email_verified_at','password'],
    DB::table('users')
    ->select('id', 'name', 'email', 'email_verified_at','password')
    ->where('updated_at', '<=', now()->subMonth()));
    
     if($insert){
        dd(DB::table('users2')->get());
     }
});

Mastering Laravel with ReactJS Course

Auto-Incrementing IDs

যদি আপনার table এ auto-incrementing id থাকে , এবং সেটিকে আপনি retrieve করতে চান , তাহলে আপনাকে insertGetId Method ব্যবহার করতে হবে:

use Illuminate\Support\Facades\DB;

Route::get('/getdata', function () {
    $id = DB::table('users')->insertGetId(
        ['name' => 'AurongoZeb', 'email' => 'john6@example.com','password' => bcrypt('secret')]
    );
    dd($id);
});

Upserts Method

upsert method টি ডেটাবেসে Data Insert এবং আপডেট এর কাজ করে থাকে , যদি ইতিমধ্যে insert করা record টি বিদ্যমান থাকে , তাহলে upsert method টি নতুন করে insert না করে পূর্বের ডেটাকে আপডেট করে। আর যদি record টি বিদ্যমান না থাকে তাহলে নতুন করে Insert করবে।

upsert method এ তিনটি array argument থাকে। প্রথম আর্গুমেন্ট টি insert অথবা update কাজ করার জন্য একটি এরে আকারে column এবং value গুলো থাকবে। দ্বিতীয় আর্গুমেন্টে টেবিলের unique column গুলো থাকবে , আর তৃতীয় আর্গুমেন্ট এ সেসব Column গুলো থাকবে যেগুলো বিদ্যমান থাকলে আপডেট হবে।

use Illuminate\Support\Facades\DB;

Route::get('/upsert', function () {
    $inserted_data = ['name' => 'Soriful Hasan', 'email' => 'hasan@gmail.com', 'password' => bcrypt('secret')];
    $unique_data = ['email'];
    $update_data = ['name', 'password'];
    
    if(DB::table('users')->upsert($inserted_data,$unique_data,$update_data)){
    dd(DB::table('users')->get()); 
    }
});

Update Statements

Laravel query builder এ ডাটাবেসে রেকর্ড Insert করা ছাড়াও, update method ব্যবহার করে আপনি চাইলে বিদ্যমান রেকর্ডগুলিও update করতে পারেন । update method টি,Insert method এর মতো, column এবং value জোড়ার একটি অ্যারে গ্রহণ করে যা কলামগুলিকে আপডেট করতে নির্দেশ করে। update method টি affected rows সংখ্যা প্রদান করে। এছাড়াও আপনি clauses গুলি ব্যবহার করে আপডেট ক্যোয়ারী সীমাবদ্ধ করতে পারেন:

use Illuminate\Support\Facades\DB;
Route::get('/update', function () {
    $affected = DB::table('users')
                ->where('id', 3)
                ->update(['address' => 'Dhaka,Bangladesh']);

    if($affected>0){
        return "Your Data is Updated";
    }
    return "Not Updated";
});

Update Or Insert

Laravel query builder এ UpdateOrInsert Method দিয়ে ডেটাবেসে কোনো রেকর্ড বিদ্যমান থাকলে এই মেথড টি উক্ত রেকর্ডকে আপডেট করে, অন্যথায় এটি প্রদত্ত ডেটার সাথে একটি নতুন রেকর্ড Insert করে। লারাভেলের Query Builder এর updateOrInsert মেথড টি Upsert Method এর মতোই। মূল পার্থক্য হচ্ছে updateOrInsert Method দুটি Argument গ্রহণ করে। অন্যদিকে Upsert Method টি তিনটি Argument গ্রহণ করে।

updateOrInsert Method এর প্রথম Argument টি একটি রেকর্ড বিদ্যমান কিনা তা নির্ধারণ করার জন্য conditions গুলির একটি array এবং দ্বিতীয়টি insert বা Update করার জন্য attributes গুলির একটি array । যদি conditions গুলির সাথে মিলে যাওয়া একটি রেকর্ড বিদ্যমান থাকে তবে এটি প্রদত্ত attributes গুলির সাথে আপডেট করা হবে, অন্যথায় attributes গুলির সাথে একটি নতুন রেকর্ড insert করা হবে৷

use Illuminate\Support\Facades\DB;
Route::get('/update-or-insert', function () {
    $affected=DB::table('users')
    ->updateOrInsert(
        ['email' => 'soriful.islam@example.org', 'name' => 'Soriful Islam'],
        ['address' => 'Rajshahi,Bangladesh']
    );

    if($affected>0){
        return "Data Update/Insert Successfully ";
    }
    return "Your Data is not Update or not Insert any new row";
});

Updating JSON Columns

Laravel Query Builder ব্যবহার করে আপনি যেকোনো JSON কলাম আপডেট করতে পারেন , যেকোনো JSON অবজেক্টে একটি Key এর value আপডেট করতে আপনাকে ‘->’ সিনট্যাক্স ব্যবহার করতে হবে । এই অপারেশনটি MySQL 5.7+ এবং PostgreSQL 9.5+ এ সমর্থিত। এই কাজ টি করার জন্য প্রথমে আপনি নিম্নোক্ত SQL টি রান করে নিন:

CREATE TABLE `members` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `address` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`address`))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `members` (`id`, `name`, `address`) VALUES
(1, 'Masud Alam', '{\"city\": \"Dhaka\", \"country\": \"India\"}');

এবার আপনি নিম্নোক্ত query builder Query দিয়ে আপনার JSON Object property এর Country কে আপডেট করতে পারেন :

use Illuminate\Support\Facades\DB;
Route::get('/json-update', function () {
    $updated = DB::table('members')
                ->where('id', 1)
                ->update(['address->country' => 'Bangladesh']);

    if($updated>0){
        return "Data Update Successfully ";
    }
    return "Your Data is not Updated";
});

Increment & Decrement

Laravel Query builder একটি প্রদত্ত কলামের মান বৃদ্ধি বা হ্রাস করার জন্য সুবিধাজনক methods সরবরাহ করে। এই উভয় পদ্ধতিই দুটি আর্গুমেন্ট একসেপ্ট করে: প্রথমটি যে কলাম টি পরিবর্তন করবেন সেটি ।আর দ্বিতীয় আর্গুমেন্ট টি হচ্ছে কলামটি যে পরিমাণে বৃদ্ধি বা হ্রাস করা উচিত তা নির্দিষ্ট করার জন্য:

DB::table('users')->increment('votes');
 
DB::table('users')->increment('votes', 5);
 
DB::table('users')->decrement('votes');
 
DB::table('users')->decrement('votes', 5);

প্রয়োজনে, আপনি ইনক্রিমেন্ট বা হ্রাস অপারেশনের সময় আপডেট করার জন্য অতিরিক্ত কলামগুলিও নির্দিষ্ট করতে পারেন:

DB::table('users')->increment('votes', 1, ['name' => 'John']);

উপরন্তু, আপনি incrementEach এবং decrementEach পদ্ধতি ব্যবহার করে একবারে একাধিক কলাম বৃদ্ধি বা হ্রাস করতে পারেন:

DB::table('users')->incrementEach([
    'votes' => 5,
    'balance' => 100,
]);

Delete Statements

Laravel Query Builder এ delete method দিয়ে আপনি টেবিল থেকে যেকোনো রেকর্ড মুছে ফেলার জন্য ব্যবহার করতে পারেন। delete method টি আপনাকে affected rows সংখ্যা return করবে।

একটি table এর সবগুলো row ডিলিট করে ফেলতে চাইলে নিচের মতো করে Laravel Query Builder এর delete Method টি ব্যবহার করতে হবে।

use Illuminate\Support\Facades\DB;
Route::get('/delete', function () {
    $deleted = DB::table('users')->delete();

    if($deleted>0){
        return "Data Delete Successfully ";
    }
    return "Your Data is not Deleted";
});

এছাড়াও আপনি delete method কল করার আগে “where” clauses যোগ করে delete statements কে সীমাবদ্ধ করতে পারেন:

use Illuminate\Support\Facades\DB;
Route::get('/delete', function () {
    $deleted = DB::table('users')->where('phone', '=', '01943510576')->delete();

    if($deleted>0){
        return "Data Delete Successfully ";
    }
    return "Your Data is not Deleted";
});

সাধারণতঃ delete Method ব্যবহার করে কোনো ডেটা ডিলিট করার পর , Database সর্বশেষ auto-incrementing ID এর index ধরে রাখে। আপনি যদি একটি সম্পূর্ণ টেবিল auto-incrementing ID এর index সহ delete করে ফেলতে চান, যা টেবিল থেকে সমস্ত রেকর্ড মুছে ফেলবে এবং auto-incrementing ID কে zero তে রিসেট করতে আপনি truncate method ব্যবহার করতে পারেন:

use Illuminate\Support\Facades\DB;
Route::get('/delete', function () {
     DB::table("students")->truncate();
});

Mastering Laravel with ReactJS Course

Pessimistic Locking

Pessimistic locking হল এক ধরনের ডাটাবেস লকিং প্রক্রিয়া যেখানে একটি রেকর্ডে বা ডাটাবেস টেবিলের row গুলো read করার সময় একটি লক apply করা হয়, লকটি released না হওয়া পর্যন্ত ডেটা পরিবর্তন করা থেকে অন্যান্য transactions গুলোকে বাধা দেয়।

Laravel Query Builder এ আপনার select statement গুলি কার্যকর করার সময় “pessimistic locking” সুবিধা যুক্ত করার জন্য কিছু ফাংশন রয়েছে। এর মধ্যে একটি হচ্ছে “shared lock” যা একটি select statement এ ব্যবহার করতে আপনি “shared lock” Method কল করতে পারেন৷ একটি “shared lock” আপনার transaction committed না হওয়া পর্যন্ত selected row গুলিকে modify করা থেকে বাধা দেয়:

DB::table('users')
        ->where('votes', '>', 100)
        ->sharedLock()
        ->get();

এছাড়াও , আপনি চাইলে lockForUpdate Method টিও ব্যবহার করতে পারেন। এটি আপডেটের জন্য selected records গুলিকে modified করা থেকে বা অন্য শেয়ার করা লকের সাথে select করা থেকে বাধা দেয়:

DB::table('users')
        ->where('votes', '>', 100)
        ->lockForUpdate()
        ->get();

Debugging

Current query bindings এবং SQL ডাম্প করার জন্য একটি ক্যোয়ারী তৈরি করার সময় আপনি dd এবং dump method ব্যবহার করতে পারেন। dd method টি debug information প্রদর্শন করবে এবং request টি executing করা বন্ধ করবে। dump method টি debug information প্রদর্শন করবে তবে request টি executing করা continue করার অনুমতি দেবে:

DB::table('users')->where('votes', '>', 100)->dd();
 
DB::table('users')->where('votes', '>', 100)->dump();