Laravel Database Query Builder
LARAVEL DATABASE QUERY BUILDER PART-2: Retrieving, Chunking, Aggregates and Streaming Results
![Retrieving, Chunking and Streaming Results Lazily with Database Query Builder](https://blog.w3programmers.com/wp-content/uploads/2023/01/Retrieving-Chunking-and-Streaming-Results-Lazily-with-Database-Query-Builder.jpg)
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`);
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>"; } });
![Retrieve all data from a table using query builder](https://blog.w3programmers.com/wp-content/uploads/2023/01/Retrieve-all-data-from-a-table-using-query-builder.jpg)
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; });
![Laravel query builder first method](https://blog.w3programmers.com/wp-content/uploads/2023/01/Laravel-query-builder-first-method.jpg)
আপনি সম্পূর্ণ 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
![Laravel pluck Method](https://blog.w3programmers.com/wp-content/uploads/2023/01/Laravel-pluck-Method.jpg)
তবে আপনি চাইলে 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
![Laravel Pluck Method for two column](https://blog.w3programmers.com/wp-content/uploads/2023/01/Laravel-Pluck-Method-for-two-column-.jpg)
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>"; });
![Specifying A Select Clause](https://blog.w3programmers.com/wp-content/uploads/2023/01/Specifying-A-Select-Clause.jpg)
এছাড়াও 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>"; });
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>"; });
![Laravel Query Builder Raw Expressions](https://blog.w3programmers.com/wp-content/uploads/2023/01/Laravel-Query-Builder-Raw-Expressions.jpg)
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>"; });
![Laravel selectRaw Method Example](https://blog.w3programmers.com/wp-content/uploads/2023/01/Laravel-selectRaw-Method-Example.jpg)
উপরের আউটপুটে লক্ষ্য করবেন , প্রত্যেকটি 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>"; });
![Laravel Query Builder whereRaw and orWhereRaw Functions](https://blog.w3programmers.com/wp-content/uploads/2023/01/Laravel-Query-Builder-whereRaw-and-orWhereRaw-Functions.jpg)
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>"; });
![Laravel 9 Query Builder havingRaw and orHavingRaw Example](https://blog.w3programmers.com/wp-content/uploads/2023/01/Laravel-9-Query-Builder-havingRaw-and-orHavingRaw-Example.jpg)
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>"; });
![Laravel 9 Query Builder orderByRaw Method Example](https://blog.w3programmers.com/wp-content/uploads/2023/01/Laravel-9-Query-Builder-orderByRaw-Method-Example.jpg)
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>"; });
![Laravel 9 Query Builder groupBy Method Example](https://blog.w3programmers.com/wp-content/uploads/2023/01/Laravel-9-Query-Builder-groupBy-Method-Example.jpg)
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
![Laravel Query Builder chunk Method](https://blog.w3programmers.com/wp-content/uploads/2023/01/Laravel-Query-Builder-chunk-Method.jpg)
এছাড়াও আপনি 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 করতে গেলে কি ধরণের সমস্যা হয়।
ধরুন আপনার নিম্নরূপ একটি টেবিল এবং কিছু ডেটা রয়েছে:
![An Example of users Table](https://blog.w3programmers.com/wp-content/uploads/2023/01/An-Example-of-users-Table.jpg)
এখন আমরা 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 গুলো আপডেট করতেছে:
![Update Data During Chunk Method](https://blog.w3programmers.com/wp-content/uploads/2023/01/Update-Data-During-Chunk-Method-.jpg)
এখন যদি ‘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>"; });
![Laravel Query Builder Aggregate Functions](https://blog.w3programmers.com/wp-content/uploads/2023/01/Laravel-Query-Builder-Aggregate-Functions.jpg)
আর অবশ্যই, আপনি আপনার রেজাল্ট কে আরো বেশি 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"; } });