LARAVEL DATABASE QUERY BUILDER PART-2: Retrieving, Chunking, Aggregates and Streaming Results

Running Database Queries in Laravel

Laravel Query Builder ব্যবহার করে, আপনি raw SQL statement গুলো না লিখেই বিভিন্ন ডাটাবেস অপারেশন করতে পারেন, আর এর মধ্যে Retrieving, Chunking, Aggregates এবং Streaming Results Lazily সুবিধা হচ্ছে অন্যতম। আর এখানে retrieving মানে আমরা কতভাবে আমাদের ব্যবহৃত Database থেকে Data Retrieve করতে পারব। আর chunking মানে বুঝাচ্ছি , একটা বৃহৎ Database থেকে একটি ক্ষুদ্র ডেটা বের করে আনবো। আবার একটা বৃহৎ ডাটাবেস থেকে তার ডাটা গুলোকে ছোট ছোট ভাগে ডাটা গুলো পাঠানোর কাজগুলোকে বলা হয় Streaming Results Lazily আর ডাটাবেসের ডাটা গুলোর উপর বিভিন্ন Mathematical calculation এপ্লাই করার জন্য Laravel Query Builder এ যেসব ফাঙ্কশন গুলোকে ব্যবহার করা হয় , সেগুলোকে বলা হয় Aggregates.

এই পোস্টের উদাহরণ গুলো বুঝার সুবিধার্তে নিম্নোক্ত 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` datetime DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `age` float NOT NULL,
  `gender` enum('Male','Female') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `phone`, `age`, `gender`) VALUES
(1, 'Abul Hashem', 'abul@hashem.com', NULL, '01755886699', 18, 'Male'),
(2, 'Soriful Islam', 'soriful@islam.com', NULL, '01966558877', 25, 'Female'),
(3, 'Abir Hossain', 'abir@hossain.com', NULL, '01655544778', 30, 'Female'),
(4, 'Khan Jahan Ali', 'khanjahan@ali.com', NULL, '01566998833', 18, 'Male'),
(5, 'Aminul Haque Tapu', 'aminul@haque.com', NULL, '01236547896', 30, 'Male'),
(6, 'Zawadul Karim', 'zawadul@karim.com', NULL, '0158866995', 25, 'Male'),
(7, 'Farhan Hasin', 'farhan@hasin.com', NULL, '01788996655', 29, 'Female'),
(8, 'Kamrul Islam', 'kamrul@islam.com', NULL, '01589966557', 45, 'Male');

--
-- Indexes for dumped tables
--

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

Mastering Laravel with ReactJS Course

Retrieving All Rows From A Table

একটি table থেকে সব গুলো ডেটা নিয়ে আসতে আপনি DB facade এর table method টি ব্যবহার করতে পারেন। table method টি প্রদত্ত টেবিলের জন্য একটি fluent query builder instance প্রদান করে, এছাড়াও এটি আপনাকে ক্যোয়ারীতে আরও অনেক constraint গুলোকে chain করতে দেয় এবং তারপর সর্বশেষ get method ব্যবহার করে কোয়েরির results retrieve করতে দেয়:

use Illuminate\Support\Facades\DB;

Route::get('/getall', function () {
    $users = DB::table('users')->get();
 
    foreach ($users as $user) {
        echo $user->name,"<br>";
    }
});

Database এর একটি নির্দিষ্ট table থেকে একটি single row অথবা single column retrieve করা

আপনি Database এর table থেকে একটি single row অথবা single column retrieve করার প্রয়োজন হলে, আপনি first Method ব্যবহার করতে পারেন। আপনার route web.php File এ নিচের কোডটি লিখুন :

use Illuminate\Support\Facades\DB;

Route::get('/getval', function () {
    $user = DB::table('users')->where('email', 'soriful@islam.com')->first();
    echo $user->name;
});

আপনি সম্পূর্ণ row এর পরিবর্তে সরাসরি একটি column এর value print করতে চাইলে value() method টি ব্যবহার করতে পারেন। এর জন্যে আপনার route web.php File এ নিচের কোডটি লিখুন :

use Illuminate\Support\Facades\DB;

Route::get('/getval', function () {
    $name = DB::table('users')->where('email', 'soriful@islam.com')->value('name');
    echo $name;
});

Table এর একটি নির্দিষ্ট column এর সব row একসাথে retrieve করা :

Table এর একটি নির্দিষ্ট column এর সব row একসাথে retrieve করতে চাইলে আপনি DB facade এর pluck() Method ব্যবহার করতে পারেন। নিচের উদাহরণটি দেখুন :

use Illuminate\Support\Facades\DB;

Route::get('/getval', function () {
    $names = DB::table('users')->pluck('name');
    foreach ($names as $name) {
        echo $name,"<br>";
    }
});

Result

তবে আপনি চাইলে Pluck Method দিয়ে table এর একটি column কে key এবং অন্যটিকে value হিসেবে রিটার্ন করতে পারেন । নিচের উদাহরণটি দেখুন :

use Illuminate\Support\Facades\DB;

Route::get('/getval', function () {
    $names = DB::table('users')->pluck('name','id');
    $table = "<table border='1'";
    $table .= "<tr>";
    $table .= "<th>Id</th><th>Name</th>";
    $table .= "</tr>";
    foreach ($names as $id=>$name) {
        $table .= "<tr>";
        $table.="<td>$id</td><td>$name</td>";
        $table .= "</tr>";
    }
    $table .= "</table>";
    echo $table;
});

Result

Specifying A Select Clause

উপরের উদহারণ গুলোতে ডেটাবেসের টেবিল এর সবগুলো রো select হয়। আপনি সবসময় একটি ডাটাবেস টেবিল থেকে সব কলাম select করতে চান না. select method ব্যবহার করে, আপনি query এর জন্য একটি কাস্টম “select” clause নির্দিষ্ট করতে পারেন:

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
    $users = DB::table('users')
            ->select('name', 'email as user_email')
            ->get();
    echo "<pre>";
    print_r($users);
    echo "</pre>";
});

এছাড়াও distinct method ব্যবহার করে আপনি ক্যোয়ারীটিকে distinct রেজাল্ট রিটার্ন করার জন্য force করতে পারেন:

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
    $users = DB::table('users')->select('name','age')->distinct()->get();
    echo "<pre>";
    print_r($users);
    echo "</pre>";
}); 

আর সবগুলো Column এর distinct value পেতে, আপনি নিচের মতো কোয়েরি রান করতে পারেন।

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
    $users = DB::table('users')->distinct()->get();
    echo "<pre>";
    print_r($users);
    echo "</pre>";
}); 

আপনার যদি ইতিমধ্যেই একটি query builder instance থাকে এবং আপনি তার বিদ্যমান select clause এ নতুন করে একটি কলাম add করতে চান, তাহলে আপনি addSelect method ব্যবহার করতে পারেন:

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
    $query = DB::table('users')->select('name');
    $users = $query->addSelect('age')->get();
    echo "<pre>";
    print_r($users);
    echo "</pre>";
}); 

Mastering Laravel with ReactJS Course

Raw Expressions

কখনো কখনো আপনাকে একটি ক্যোয়ারীতে একটি arbitrary string সন্নিবেশ করতে হতে পারে। একটি raw string expression তৈরি করতে, আপনি DB facade দ্বারা প্রদত্ত raw method ব্যবহার করতে পারেন:

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
    $users = DB::table('users')
    ->select(DB::raw('count(*) as user_count, age as byage'))
    ->where('age', '>=', 18)
    ->groupBy('age')
    ->get();
    echo "<pre>";
    print_r($users);
    echo "</pre>";
}); 

Raw Methods

DB::raw Method ব্যবহার করার পরিবর্তে, আপনি আপনার query এর বিভিন্ন অংশে একটি raw expression সন্নিবেশ করতে নিম্নলিখিত methods গুলিও ব্যবহার করতে পারেন। মনে রাখবেন, লারাভেল গ্যারান্টি দিতে পারে না যে raw expressions ব্যবহার করে কোনো query , SQL injection vulnerabilities বিরুদ্ধে সুরক্ষিত।

selectRaw

AddSelect(DB::raw(/* … */)) এর পরিবর্তে আমরা SelectRaw Method ব্যবহার করা যেতে পারে। এই method টি তার দ্বিতীয় argument হিসাবে বাইন্ডিংয়ের একটি ঐচ্ছিক অ্যারে গ্রহণ করে:

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
    $users = DB::table('users')
    ->selectRaw('name,phone,age + ? as with_extension', [2])
    ->get();
    echo "<pre>";
    print_r($users);
    echo "</pre>";
}); 

উপরের আউটপুটে লক্ষ্য করবেন , প্রত্যেকটি age এর ভ্যালু 2 করে বৃদ্ধি পাবে।

whereRaw / orWhereRaw

whereRaw এবং orWhereRaw Method দুটি দিয়ে আপনার ক্যোয়ারী এর where clause এ যথাক্রমে ‘or’ এবং ‘and’ logical operator যুক্ত করতে পারেন:

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
    $users = DB::table('users')
                ->select('name','age','gender')
                ->whereRaw('age > ? and gender="Male"',[20])
                ->get();
    echo "<pre>";
    print_r($users);
    echo "</pre>";
}); 

havingRaw / orHavingRaw

havingRaw এবং orHavingRaw Method দুটি দিয়ে আপনার ক্যোয়ারী এর having clause এ যথাক্রমে ‘or’ এবং ‘and’ logical operator যুক্ত করতে পারেন।

তবে তার আগে নিম্নোক্ত orderdetails table স্ট্রাকচার এবং ডেটা এর SQL টুকু রান করে নিন :

--
-- Table structure for table `orderdetails`
--

CREATE TABLE `orderdetails` (
  `orderNumber` int(11) NOT NULL,
  `productCode` varchar(15) NOT NULL,
  `quantityOrdered` int(11) NOT NULL,
  `priceEach` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `orderdetails`
--

INSERT INTO `orderdetails` (`orderNumber`, `productCode`, `quantityOrdered`, `priceEach`) VALUES
(10423, 'S18_3320', 21, '80.36'),
(10423, 'S24_4258', 28, '78.89'),
(10424, 'S10_1949', 50, '201.44'),
(10424, 'S12_1666', 49, '121.64'),
(10424, 'S18_1097', 54, '108.50'),
(10424, 'S18_4668', 26, '40.25'),
(10424, 'S32_3522', 44, '54.94'),
(10424, 'S700_2824', 46, '85.98'),
(10425, 'S10_4962', 38, '131.49'),
(10425, 'S12_4473', 33, '95.99'),
(10425, 'S18_2238', 28, '147.36'),
(10425, 'S18_2319', 38, '117.82'),
(10425, 'S18_2432', 19, '48.62'),
(10425, 'S18_3232', 28, '140.55'),
(10425, 'S18_4600', 38, '107.76'),
(10425, 'S24_1444', 55, '53.75'),
(10425, 'S24_2300', 49, '127.79'),
(10425, 'S24_2840', 31, '31.82'),
(10425, 'S32_1268', 41, '83.79'),
(10425, 'S32_2509', 11, '50.32'),
(10425, 'S50_1392', 18, '94.92');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `orderdetails`
--
ALTER TABLE `orderdetails`
  ADD PRIMARY KEY (`orderNumber`,`productCode`),
  ADD KEY `productCode` (`productCode`);

এইবার এই উদাহরণটি রান করে দেখতে পারেন :

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
$order_details = DB::table('orderdetails')
    ->select('ordernumber', DB::raw('SUM(quantityOrdered) AS itemsCount,SUM(priceeach*quantityOrdered) AS total'))
    ->groupBy('ordernumber')
    ->havingRaw('SUM(total) > ?', [1000])
    ->get();
echo "<pre>";
print_r($order_details);
echo "</pre>";
}); 

orderByRaw

OrderByRaw method টি SQL এর “order by” clause এর জন্য ব্যবহার করা যেতে পারে:

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
    $users = DB::table('users')
    ->select('name','age')
    ->orderByRaw('age DESC')
    ->get();
echo "<pre>";
print_r($users);
echo "</pre>";
}); 

groupByRaw

groupByRaw method টি আপনি SQL এর group by clause এর কাজে ব্যবহার করতে :

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
    $users = DB::table('users')
    ->select('name', 'gender')
    ->groupByRaw('name, gender')
    ->get();
echo "<pre>";
print_r($users);
echo "</pre>";
});

Chunking Results

Database এ Big Data নিয়ে কাজ করতে গেলে অথবা অনেকগুলো email একসাথে পাঠাতে গেলে আপনাকে অনেক সময় সমস্যায় পড়তে হয়, এর জন্য আপনি DB facade এর chunk method ব্যবহার করতে পারেন। মূলতঃ Laravel “chunk” method বৃহৎ ডেটাবেসে কাজ করার সময় বড় ক্যোয়ারীকে ছোট খণ্ডে বিভক্ত করতে ব্যাপকভাবে ব্যবহৃত হয়। নিচের উদাহরণটি দেখুন :

use Illuminate\Support\Facades\DB;

Route::get('/getval', function () {
    DB::table('users')->orderBy('id')->chunk(2, function ($users) {
        $i=1;
        foreach ($users as $user){
        echo "page ", $i++;
        echo "<pre>";
        print_r($user);
        echo "</pre>";
        }
    });
});

Result

এছাড়াও আপনি closure থেকে false return করে পরবর্তী chunk গুলোর প্রসেস বন্ধ করতে পারেন:

use Illuminate\Support\Facades\DB;

Route::get('/getval', function () {
    DB::table('users')->orderBy('id')->chunk(2, function ($users) {
        $i=1;
        foreach ($users as $user){
        echo "page ", $i++;
        echo "<pre>";
        print_r($user);
        echo "</pre>";
        if ($user->id == 3) {
            return false;
         }
        }
    });
});

chunkById Method

আপনি রেজাল্ট গুলোকে chunking করা কালীন, যেকোনো ডাটাবেস রেকর্ড আপডেট করলে, আপনার chunk রেজাল্ট গুলো unexpected চেঞ্জ হতে পারে। আপনি যদি chunking করার সময় রিট্রিভ করা রেকর্ডগুলি আপডেট করতে চান , তবে chunk method এর পরিবর্তে chunkById Method টি ব্যবহার করা উত্তম। কেননা “chunkById” id এবং limit ব্যবহার করে যখন “chunk” limit এবং offset ব্যবহার করে। অর্থাৎ chunkById method টি automatically রেকর্ড গুলোর primary key এর উপর ভিত্তি করে ফলাফলগুলিকে paginate করবে।

অন্যভাবে বলা যায় , chunkById method হল একটি ডাটাবেস টেবিল থেকে প্রচুর সংখ্যক রেকর্ড retrieve করার এবং মেমরির সমস্যা এড়াতে ছোট ছোট “chunks” এ process করার একটি উপায়। এটি chunk method এর অনুরূপভাবে কাজ করে, তবে প্রতিটি chunk retrieve করার জন্য রেকর্ডের সংখ্যা নির্দিষ্ট করার পরিবর্তে, এটি প্রতিটি chunk এ retrieve করা রেকর্ডের শেষ আইডি নির্দিষ্ট করে । এইভাবে, এটি আপনাকে smoothly প্রচুর পরিমাণে ডেটা পরিচালনা করতে সুযোগ দেয়।

তো প্রথমে চলুন দেখাযাক , chunkById method ব্যবহার না করে update করতে গেলে কি ধরণের সমস্যা হয়।

Mastering Laravel with ReactJS Course

ধরুন আপনার নিম্নরূপ একটি টেবিল এবং কিছু ডেটা রয়েছে:

এখন আমরা chunking করা কালীন নিম্নোক্ত উপায়ে , আমাদের table কে update করব।

use Illuminate\Support\Facades\DB;

Route::get('/getval', function () {
    DB::table('users')
    ->whereNull('email_verified_at')
    ->orderBy('id')->chunk(2, function ($users) {
        foreach ($users as $user) {
            DB::table('users')
            ->where('id', $user->id)
            ->update(['email_verified_at' => now()]);
        }
    });
});

যেহেতু ‘chunk’ Method ‘limit’ এবং offset দুটোই ব্যবহার করে , আপনি লক্ষ্য করবেন , আপনার table এর প্রতি দুটি row বাদ দিয়ে row গুলো আপডেট করতেছে:

এখন যদি ‘chunk’ Method এর পরিবর্তে chunkById method টি ব্যবহার করেন। তাহলে এই সমস্যার মুখোমুখি হতে হবেনা। কেননা chunkById মেথড টি table এর primary key এবং শুধু limit সুবিধা ব্যবহার করে :

use Illuminate\Support\Facades\DB;

Route::get('/getval', function () {
    DB::table('users')
    ->whereNull('email_verified_at')
    ->orderBy('id')->chunkById(2, function ($users) {
        foreach ($users as $user) {
            DB::table('users')
            ->where('id', $user->id)
            ->update(['email_verified_at' => now()]);
        }
    });
});

chunk এর মাধ্যমে যখন কোন রেকর্ড আপডেট করবো তখন কতগুলো ডাটা আপডেট হয়েছে সেটা কাউন্ট করবো কিভাবে?

chunk এর মাধ্যমে যখন কোন রেকর্ড আপডেট করবো তখন কতগুলো ডাটা আপডেট হয়েছে সেটা কাউন্ট করবো কিভাবে তার একটি উদাহরণ নিম্নে দেওয়া হলো :

Route::get('/chunk',function(){
    $count = 0;
DB::table('users')
    ->whereNull('email_verified_at')
    ->orderBy('id')
    ->chunkById(2, function ($users) use (&$count) {
        foreach ($users as $user) {
            DB::table('users')
            ->where('id', $user->id)
            ->update(['email_verified_at' => now()]);    
        }
        $count += count($users);
    });
echo "Number of records updated: " . $count;

});

Streaming Results Lazily

lazy Method

lazy method টি chunk method এর অনুরূপভাবে কাজ করে। পার্থক্য হচ্ছে “lazy method” এমন একটি method কে বোঝায় যা শুধুমাত্র তখনই execute হয় যখন এটি বাস্তবে প্রয়োজন হয়, এটি chunk method এর মতো immediately execute হয়না ।

use Illuminate\Support\Facades\DB;

Route::get('/getval', function () {
    DB::table('users')->orderBy('id')->lazy(2)->each(function ($users) {
            foreach ($users as $user) {
            echo $user,"<br>";
            }
        });
});

lazyById() Method

lazyById method টি chunkById method এর অনুরূপভাবে কাজ করে। পার্থক্য হচ্ছে “lazyById method” এমন একটি method কে বোঝায় যা শুধুমাত্র তখনই execute হয় যখন এটি বাস্তবে প্রয়োজন হয়, এটি chunkById method এর মতো immediately execute হয়না ।

 Route::get('/getval', function () {
    DB::table('users')->orderBy('id')->lazyById(2)->each(function ($users) {
        $page = 1;
            foreach ($users as $user) {
            echo $user,"<br>";
            }
        });
}); 

Aggregates

Laravel Query Builder count, max, min, avg, এবং sum নামে কিছু aggregate Method এর সুবিধা দেয়, নিচে এগুলোর কিছু উদাহরণ দেওয়া হলো :

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
    $totalUsers = DB::table('users')->count();
    $averageAge=DB::table('users')->avg('age');
    $maxAge=DB::table('users')->max('age');
    $minAge=DB::table('users')->min('age');
    echo "Total No. of Users: " . $totalUsers,"<br>";
    echo "Average Age is: ", $averageAge,"<br>";
    echo "Minimum Age is :", $minAge,"<br>";
    echo "Maximum Age is :", $maxAge,"<br>";

}); 

আর অবশ্যই, আপনি আপনার রেজাল্ট কে আরো বেশি fine-tune করতে আপনি এই aggregate methods গুলিকে অন্যান্য clauses গুলির সাথে একত্রে ব্যবহার করতে পারেন:

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
    $totalUsers = DB::table('users')->where('age','>', 25)->count();
    echo "Total No. of Users: " . $totalUsers,"<br>";
}); 

Determining If Records Exist

কোনো রেকর্ড বিদ্যমান কিনা তা নির্ধারণ করতে count method ব্যবহার করার পরিবর্তে, আপনি exist এবং doesntExist method ব্যবহার করতে পারেন।

সেক্ষেত্রে আপনার রেজাল্ট true অথবা false আসবে।

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
    if(DB::table('users')->where('age','>', 25)->exists()){
    $totalUsers = DB::table('users')->where('age','>', 25)->count();
        echo "Total $totalUsers Users Available";
    }
}); 

use Illuminate\Support\Facades\DB;
Route::get('/getval', function () {
    if(DB::table('users')->where('age','>', 50)->doesntExist()){
        echo "No Users Available";
    }
});