Laravel Database Query Builder
LARAVEL DATABASE QUERY BUILDER PART-5: Laravel Query Builder Joins Basic to Advance
Laravel Query Builder-এ একটি join query হল একটি common কলামের উপর ভিত্তি করে একাধিক ডাটাবেস টেবিল থেকে ডেটা retrieve করার একটি উপায়। Laravel একটি fluent query builder interface প্রদান করে database operations, join queries সহ।
Laravel Query Builder-এ inner join, left join, right join, এবং cross join সহ বিভিন্ন ধরণের join queries রয়েছে। Laravel Query Builder এই ধরনের প্রতিটি joins এর জন্য ভিন্ন ভিন্ন methods প্রদান করে।
আজকের পর্বে আমরা দেখব কিভাবে জটিল সব join query লারাভেল Query Builder দিয়ে সমাধান করা যায়। আর এর জন্য আমরা নিম্নোক্ত Sample Database Schema টি ব্যবহার করব :
এবার এই Sample Database Schema টি এখান থেকে ডাউনলোড করে আপনার সার্ভারে ইম্পোর্ট করুন ।
Inner Join Clause in Query Builder
Inner join হল রিলেশনাল ডাটাবেসে এক ধরনের join operation যা matching condition উপর ভিত্তি করে দুই বা ততোধিক টেবিলের row গুলিকে একত্রিত করে। একটি Inner join এর result set শুধুমাত্র সেইসব row গুলি অন্তর্ভুক্ত থাকে যেখানে উভয় টেবিলের জন্য join condition টি true ।
join condition টি সাধারণত comparison operator ব্যবহার করে প্রকাশ করা হয় যেমন equal (=), less than(<), greater than (>), বা not equal (<>)। এতে সাধারণত join হওয়া প্রতিটি টেবিল থেকে একটি কলাম involves থাকে, যেগুলোর মান একই আছে কিনা তা চেক করা হয়।
আরো সহজ করে বললে INNER JOIN একটি টেবিলের প্রতিটি row এর সাথে অন্য টেবিলের প্রতিটি row সাথে মেলে এবং আপনাকে উভয় টেবিলের একই কলাম ধারণ করে এমন row query করতে দেয়৷
নীচের Venn diagram টি ব্যাখ্যা করে যে কীভাবে INNER JOIN clause কাজ করে:
INNER JOIN examples In Laravel Query Builder
এবার আসুন আপনার download করা sample database এ products এবং productlines এর স্ট্রাকচারটা দেখি:
এই diagram এ , products table এ productLine নামে একটি column রয়েছে , যা productlines টেবিলের productLine কলামের সাথে reference করা । অর্থাৎ products table এর productLine কলামটি মূলত productlines টেবিল এর একটি foreign key column.
মূলতঃ আপনি productLine foreign key relationships এর ভিত্তিতে productlines এবং products table দুটির মধ্যে join করেন।
ধরুন আপনি নিম্নোক্ত আউটপুট পেতে চান:
- products টেবিল থেকে productCode এবং productName ।
- productlines টেবিল থেকে product লাইনের textDescription ।
এটি করার জন্য, আপনাকে Laravel Query Builder এ নিম্নরূপ INNER JOIN clause ব্যবহার করে productline কলামের value এর উপর ভিত্তি করে row গুলো মিলিয়ে উভয় টেবিল থেকে Data Select করতে হবে:
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$products = DB::table('products')
->select('productCode', 'productName', 'textDescription')
->join('productlines', 'products.productline', '=', 'productlines.productline')
->get();
echo "<pre>";
print_r($products);
echo "</pre>";
});
এখানে table method টি products table টি select করতে ব্যবহার করা হয়. আপনি যে কলামগুলি retrieve করতে চান তা নির্দিষ্ট করতে select method ব্যবহার করা হয়। join method টি productline কলামের productlines টেবিলের সাথে products table join দিতে ব্যবহৃত হয়।
ডেটা retrieve করতে get method ব্যবহার করা হয়। এটি query দ্বারা প্রত্যাবর্তিত row গুলো ধারণকারী stdClass objects এর একটি collection return দেবে।
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select `productCode`, `productName`, `textDescription` from `products` inner join `productlines` on `products`.`productline` = `productlines`.`productline`
এবং ফলাফল আসবে নিম্নরূপ:
INNER JOIN with GROUP BY clause
Laravel Query Builder Inner Join এ Group By Clause ব্যবহারের আগে প্রথমে নিম্নলিখিত orders এবং orderdetails টেবিল দুটির স্ট্রাকচার দেখুন:
এখন আমরা orders এবং orderdetails টেবিল দুটি থেকে Laravel Query Builder ব্যবহার করে order number, order status, এবং total sales কত তা বের করব। আর এর জন্য INNER JOIN এর সাথে GROUP BY clause ব্যবহার করব।
Laravel Query Builder কোডটি হবে নিম্নরূপ:
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$orders=DB::table('orders')
->select('orders.orderNumber', 'orders.status', DB::raw('SUM(quantityOrdered * priceEach) as total'))
->join('orderdetails', 'orders.orderNumber', '=', 'orderdetails.orderNumber')
->groupBy('orders.orderNumber', 'orders.status')
->get();
echo "<pre>";
print_r($orders);
echo "</pre>";
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select `orders`.`orderNumber`, `orders`.`status`, SUM(quantityOrdered * priceEach) as total from `orders` inner join `orderdetails` on `orders`.`orderNumber` = `orderdetails`.`orderNumber` group by `orders`.`orderNumber`, `orders`.`status`
এবং ফলাফল আসবে নিম্নরূপ:
INNER JOIN with three tables example
Laravel Query Builder Inner Join এ তিনটি table ব্যবহার করার পূর্বে প্রথমে নিম্নলিখিত products, orders এবং orderdetails টেবিল তিনটির স্ট্রাকচার দেখুন:
এখন আমরা এই তিনটি টেবিল থেকে orderNumber, orderDate, orderLineNumber,productName,quantityOrdered,priceEach এই কলাম গুলোর ডেটা নিয়ে আসতে হলে নিম্নোক্ত উপায়ে Laravel Query Builder Inner Join Code লিখতে হবে:
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$orders=DB::table('orders')
->select('orders.orderNumber', 'orderDate', 'orderLineNumber', 'productName', 'quantityOrdered', 'priceEach')
->join('orderdetails', 'orders.orderNumber', '=', 'orderdetails.orderNumber')
->join('products', 'orderdetails.productCode', '=', 'products.productCode')
->orderBy('orders.orderNumber')
->orderBy('orderLineNumber')
->get();
echo "<pre>";
print_r($orders);
echo "</pre>";
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select `orders`.`orderNumber`, `orderDate`, `orderLineNumber`, `productName`, `quantityOrdered`, `priceEach` from `orders` inner join `orderdetails` on `orders`.`orderNumber` = `orderdetails`.`orderNumber` inner join `products` on `orderdetails`.`productCode` = `products`.`productCode` order by `orders`.`orderNumber` asc, `orderLineNumber` asc
এবং ফলাফল আসবে নিম্নরূপ:
Laravel INNER JOIN with four tables example
Laravel Query Builder Inner Join এ চারটি table ব্যবহার করার পূর্বে প্রথমে নিম্নলিখিত products, orders, customers এবং orderdetails টেবিল চারটির স্ট্রাকচার দেখুন:
এখন আমরা এই চারটি টেবিল থেকে orderNumber, orderDate, customerName, orderLineNumber, productName, quantityOrdered, priceEach এই কলাম গুলোর ডেটা নিয়ে আসতে হলে নিম্নোক্ত উপায়ে Laravel Query Builder Inner Join Code লিখতে হবে:
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$orders=DB::table('orders')
->select('orders.orderNumber', 'orderDate', 'customerName', 'orderLineNumber', 'productName', 'quantityOrdered', 'priceEach')
->join('orderdetails', 'orders.orderNumber', '=', 'orderdetails.orderNumber')
->join('products', 'orderdetails.productCode', '=', 'products.productCode')
->join('customers', 'orders.customerNumber', '=', 'customers.customerNumber')
->orderBy('orders.orderNumber')
->orderBy('orderLineNumber')
->get();
echo "<pre>";
print_r($orders);
echo "</pre>";
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select `orders`.`orderNumber`, `orderDate`, `customerName`, `orderLineNumber`, `productName`, `quantityOrdered`, `priceEach` from `orders` inner join `orderdetails` on `orders`.`orderNumber` = `orderdetails`.`orderNumber` inner join `products` on `orderdetails`.`productCode` = `products`.`productCode` inner join `customers` on `orders`.`customerNumber` = `customers`.`customerNumber` order by `orders`.`orderNumber` asc, `orderLineNumber` asc
এবং ফলাফল আসবে নিম্নরূপ:
INNER JOIN এ অন্যান্য operator গুলোর ব্যবহার
এখন পর্যন্ত, আপনি দেখেছেন, আমরা এতক্ষন যতগুলো Inner join করেছি , সেগুলোর সবগুলোর condition এ row গুলো মেলানোর জন্য equal operator (=) ব্যবহার করেছি।
equal operator (=) ছাড়াও, আপনি join condition তৈরি করতে অন্যান্য অপারেটর ব্যবহার করতে পারেন যেমন greater than (>), less than (<), এবং not-equal (<>) operator ।
নিম্নলিখিত Laravel Query Builder Code টি পণ্যের sales price খুঁজে পেতে একটি less-than ( <) join ব্যবহার করে যার কোড হল S10_1678 যা সেই পণ্যের জন্য প্রস্তুতকারকের প্রস্তাবিত খুচরা মূল্য (MSRP) থেকে কম:
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$result = DB::table('products as p')
->select('orderNumber', 'productName', 'msrp', 'priceEach')
->join('orderdetails as o', function($join) {
$join->on('p.productcode', '=', 'o.productcode')
->whereRaw('p.msrp > o.priceEach');
})
->where('p.productcode', '=', 'S10_1678')
->get();
echo "<pre>";
print_r($result);
echo "</pre>";
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select `orderNumber`, `productName`, `msrp`, `priceEach` from `products` as `p` inner join `orderdetails` as `o` on `p`.`productcode` = `o`.`productcode` and p.msrp > o.priceEach where `p`.`productcode` = 'S10_1678'
এখানে, আমরা প্রথমে Select() method ব্যবহার করে প্রয়োজনীয় column গুলো সিলেক্ট করছি। তারপর আমরা join() method ব্যবহার করে orderdetails টেবিলের সাথে products জয়েন করেছি । join() method এ, আমরা on() Method এবং অতিরিক্ত whereRaw() condition ব্যবহার করে join condition specify করছি।
অবশেষে, আমরা productcode উপর ভিত্তি করে ফলাফলগুলি ফিল্টার করতে where() condition টি প্রয়োগ করছি। get() method result set return করে।
এবং ফলাফল আসবে নিম্নরূপ:
LEFT JOIN Clause in Query Builder
LEFT JOIN Clause আপনাকে দুই বা ততোধিক টেবিল থেকে ডেটা query করতে দেয়। LEFT JOIN ও INNER JOIN clause এর অনুরূপ, LEFT JOIN এ Left Table এর সব ডেটা আসবে আর Right Table এর সে সমস্ত ডেটা আসবে যেগুলো শুধুমাত্র LEFT Table এর raw গুলোর সাথে মিলবে।
ধরুন আপনি দুটি টেবিল t1 এবং t2 কে left join করতে চান।
নিম্নলিখিত statement টি দেখায় কিভাবে দুটি টেবিলে যোগ দিতে LEFT JOIN clause টি ব্যবহার করতে হয়:
SELECT
select_list
FROM
t1
LEFT JOIN t2 ON
join_condition;
আপনি যখন LEFT JOIN clause টি ব্যবহার করেন, তখন left table এবং right table এর concept গুলি চালু করা হয়।
উপরের সিনট্যাক্সে, t1 হল left table এবং t2 হল right table।
LEFT JOIN clause টি left table (t1) থেকে প্রথমে ডেটা select করে। এটি join_condition-এর উপর ভিত্তি করে right table(t2) এর যেসব row এর সাথে left table (t1) এর যেসব row সাথে মিলে যায় শুধু সেগুলো নিয়ে আসে।
যদি উভয় টেবিলের rows গুলি join condition কে true হিসাবে মূল্যায়ন করে, LEFT JOIN উভয় টেবিল থেকে একটি নতুন row তে row এর কলামগুলিকে একত্রিত করে এবং ফলাফলের row তে এই নতুন row টিকে অন্তর্ভুক্ত করে।
যদি left table (t1) এর row গুলো right table(t2) কোনো row এর সাথে না মেলে , LEFT JOIN তখন উভয় টেবিলের row গুলির কলামগুলিকে একটি নতুন row তে একত্রিত করে এবং ফলাফলের row তে নতুন row অন্তর্ভুক্ত করে। যাইহোক, এটি right table(t2) থেকে row এর সমস্ত কলামের জন্য NULL ব্যবহার করে।
আবার, left table এর একটি row তে right table থেকে matching row আছে কিনা তা নির্বিশেষে left table থেকে LEFT JOIN সমস্ত row রিটার্ন করে।
কোন মিল না থাকলে, right table থেকে row এর কলাম NULL থাকবে।
নীচের Venn diagram টি ব্যাখ্যা করে যে কীভাবে LEFT JOIN clause কাজ করে:
LEFT JOIN examples In Laravel Query Builder
চলুন LEFT Join clause ব্যবহার করার কিছু উদাহরণ নেওয়া যাক:
দুটি টেবিলের মধ্যে join করার জন্য LEFT JOIN clause
Laravel Query Builder LEFT JOIN clause এ দুটি table ব্যবহার করার পূর্বে প্রথমে নিম্নলিখিত customers এবং orders টেবিল দুটির স্ট্রাকচার দেখুন:
প্রতিটি customer এর zero বা তার বেশি অর্ডার থাকতে পারে যখন প্রতিটি অর্ডার অবশ্যই একজন customer এর হতে হবে।
নিম্নোক্ত Laravel Query Builder Code টি সমস্ত customer দের এবং তাদের অর্ডারগুলি খুঁজে পেতে LEFT JOIN clause টি ব্যবহার করা হয়েছে:
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$result = DB::table('customers')
->leftJoin('orders', 'orders.customerNumber', '=', 'customers.customerNumber')
->select('customers.customerNumber', 'customerName', 'orderNumber', 'status')
->get();
echo "<pre>";
print_r($result);
echo "</pre>";
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select `customers`.`customerNumber`, `customerName`, `orderNumber`, `status` from `customers` left join `orders` on `orders`.`customerNumber` = `customers`.`customerNumber`
এই কোডে, customers table টি left table এবং orders table টি হচ্ছে right table. আমরা customerNumber কলাম ব্যবহার করে customers টেবিলের সাথে orders table এ join দিতে leftJoin() method ব্যবহার করি। তারপরে আমরা result থেকে যে কলামগুলি চাই তা select করতে select() method ব্যবহার করি। অবশেষে, আমরা query result retrieve করতে get() method ব্যবহার করি।
LEFT JOIN clause টি সেই সমস্ত customer দেরকে return করবে যাদের কোন order নেই। যদি কোনো customer এর কোনো অর্ডার না থাকে, তাহলে সেই কলামের orderNumber এবং status এর value গুলি NULL বা ফাঁকা থাকবে। কারণ উভয় টেবিল customers এবং orders এ একই কলামের নাম (customerNumber) join condition এ equal operator ব্যবহার করা হয়েছে।
আপনি যদি এই কোডকে LEFT JOIN clause এর পরিবর্তে INNER JOIN clause দিয়ে replace করেন, তাহলে আপনি কেবলমাত্র সেই customer দের পাবেন যাদের অন্তত একটি অর্ডার আছে।
LEFT JOIN clause to find unmatched rows
LEFT JOIN clause টি খুবই উপযোগী যখন আপনি একটি table থেকে সেসব row গুলো খুঁজতে চান যেখানে অন্য টেবিল এর কোনো row এর সাথে মিলে এমন row নেই।
নিম্নোক্ত উদাহরণটি LEFT JOIN ব্যবহার করে এমন customers দের খুঁজে বের করবে যাদের কোনো অর্ডার নেই:
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$results = DB::table('customers as c')
->leftJoin('orders as o', 'c.customerNumber', '=', 'o.customerNumber')
->select('c.customerNumber', 'c.customerName', 'o.orderNumber', 'o.status')
->whereNull('o.orderNumber')
->get();
echo "<pre>";
print_r($results);
echo "</pre>";
});
এখানে, আমরা প্রথমে যথাক্রমে customers table কে c এবং orders table কে o নামে নাম করি। তারপর আমরা leftJoin method ব্যবহার করে দুটি টেবিলের মধ্যে join করি এবং join condition উল্লেখ করি। তারপর , আমরা select method ব্যবহার করে প্রয়োজনীয় কলাম সিলেক্ট করি। অবশেষে, আমরা একটি whereNull condition যোগ করি শুধুমাত্র সেই row গুলিকে ফিল্টার করার জন্য যেখানে orderNumber null থাকে। আমরা objects এর array হিসাবে result গুলি retrieve করতে get method ব্যবহার করি।
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select `c`.`customerNumber`, `c`.`customerName`, `o`.`orderNumber`, `o`.`status` from `customers` as `c` left join `orders` as `o` on `c`.`customerNumber` = `o`.`customerNumber` where `o`.`orderNumber` is null
LEFT JOIN to join three tables
Laravel Query Builder Left Join এ তিনটি table ব্যবহার করার পূর্বে প্রথমে নিম্নলিখিত employees, customers, এবং payments টেবিল তিনটির স্ট্রাকচার দেখুন:
আমরা প্রথমে LEFT JOIN ব্যবহার করে employees table থেকে সব ইমপ্লয়ীদের first_name এবং last_name বের করব। তারপর যেসব ইমপ্লয়ীদের অন্তত পক্ষ একজন customer আছে , তাদের customerName একই সাথে payments table থেকে checkNumber এবং amounts নিয়ে আসব । আর যেসব এমপ্লয়ীদের অন্তত একজন কাস্টমার না থাকে , Laravel Query Builder তাদের checkNumber এবং amounts এর value Null দেখাবে:
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$results = DB::table('employees')
->leftJoin('customers', 'employees.employeeNumber', '=', 'customers.salesRepEmployeeNumber')
->leftJoin('payments', 'payments.customerNumber', '=', 'customers.customerNumber')
->select('employees.lastName', 'employees.firstName', 'customers.customerName', 'payments.checkNumber', 'payments.amount')
->orderBy('customers.customerName')
->orderBy('payments.checkNumber')
->get();
echo "<pre>";
print_r($results);
echo "</pre>";
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select `employees`.`lastName`, `employees`.`firstName`, `customers`.`customerName`, `payments`.`checkNumber`, `payments`.`amount` from `employees` left join `customers` on `employees`.`employeeNumber` = `customers`.`salesRepEmployeeNumber` left join `payments` on `payments`.`customerNumber` = `customers`.`customerNumber` order by `customers`.`customerName` asc, `payments`.`checkNumber` asc
নিম্নের ছবিটিতে আংশিক আউটপুট দেখানো হলো :
LEFT Join with where Condition
একটি অর্ডারের বিপরীতে একজন কাস্টমার কি কি প্রোডাক্ট নিয়েছে তা দেখার জন্য আপনি LEFT Join এ where Condition ব্যবহার করতে পারেন :
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$results = DB::table('orders as o')
->leftJoin('orderDetails', 'o.orderNumber', '=', 'orderDetails.orderNumber')
->where('o.orderNumber', '=', 10123)
->select('o.orderNumber', 'o.customerNumber', 'orderDetails.productCode')
->get();
echo "<pre>";
print_r($results);
echo "</pre>";
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select `o`.`orderNumber`, `o`.`customerNumber`, `orderDetails`.`productCode` from `orders` as `o` left join `orderDetails` on `o`.`orderNumber` = `orderDetails`.`orderNumber` where `o`.`orderNumber` = 10123
RIGHT JOIN Clause in Query Builder
RIGHT JOIN অনেকটা LEFT JOIN এর অনুরূপ,জাস্ট কাজটা বিপরীত। অর্থাৎ RIGHT JOIN এ Right Table এর সব ডেটা আসবে আর Left Table এর সে সমস্ত ডেটা আসবে যেগুলো শুধুমাত্র Right Table এর raw গুলোর সাথে মিলবে।
ধরুন আপনি দুটি টেবিল t1 এবং t2 কে Right join করতে চান।
নিম্নলিখিত statement টি দেখায় কিভাবে দুটি টেবিলে যোগ দিতে Right JOIN clause টি ব্যবহার করতে হয়:
SELECT
select_list
FROM
t1
Right JOIN t2 ON
join_condition;
আপনি যখন RIGHT JOIN clause টি ব্যবহার করেন, তখন left table এবং right table এর concept গুলি চালু করা হয়।
উপরের সিনট্যাক্সে, t1 হল left table এবং t2 হল right table।
Right JOIN clause টি Right table (t2) থেকে প্রথমে ডেটা select করে। এটি join_condition-এর উপর ভিত্তি করে Left table(t1) এর যেসব row এর সাথে Right table (t2) এর যেসব row সাথে মিলে যায় শুধু সেগুলো নিয়ে আসে।
যদি Right table (t2) এর row গুলো Left table(t1) এর কোনো row এর সাথে না মেলে , RIGHT JOIN তখন উভয় টেবিলের row গুলির কলামগুলিকে একটি নতুন row তে একত্রিত করে এবং ফলাফলের row তে নতুন row অন্তর্ভুক্ত করে। যাইহোক, এটি Left table(t1) থেকে row এর সমস্ত কলামের জন্য NULL ব্যবহার করে।
আবার, Right table এর একটি row তে Left table থেকে matching row আছে কিনা তা নির্বিশেষে Right table থেকে RIGHT JOIN সমস্ত row রিটার্ন করে।
কোন মিল না থাকলে, Left table থেকে row এর কলাম NULL থাকবে।
নীচের Venn diagram টি ব্যাখ্যা করে যে কীভাবে Right JOIN clause কাজ করে:
RIGHT JOIN examples In Laravel Query Builder
চলুন RIGHT Join clause ব্যবহার করার কিছু উদাহরণ নেওয়া যাক:
দুটি টেবিলের মধ্যে join করার জন্য RIGHT JOIN clause
Laravel Query Builder RIGHT JOIN clause এ দুটি table ব্যবহার করার পূর্বে প্রথমে নিম্নলিখিত customers এবং orders টেবিল দুটির স্ট্রাকচার দেখুন:
আমরা শুধু মাত্র সেসব customer দের লিস্ট আনব যাদের ন্যূনতম একটি অর্ডার থাকতে হবে।
নিম্নোক্ত Laravel Query Builder Code টি সেসমস্ত customer দের খুঁজে বের করবে যাদের ন্যূনতম একটি অর্ডার আছে। আর এর জন্য আমরা RIGHT JOIN clause টি ব্যবহার করব
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$result =DB::table('customers')
->rightJoin('orders', 'orders.customerNumber', '=', 'customers.customerNumber')
->select('customers.customerName')
->distinct()
->get();
foreach($result as $r){
echo $r->customerName,"<br>";
}
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select distinct `customers`.`customerName` from `customers` right join `orders` on `orders`.`customerNumber` = `customers`.`customerNumber`
নিম্নের ছবিটিতে আংশিক আউটপুট দেখানো হলো :
employees Table এর সাথে customers Table এর right join
employees Table এর সাথে customers Table এর right join করার পূর্বে প্রথমে নিম্নলিখিত employees এবং customers টেবিল দুটির স্ট্রাকচার দেখুন:
এখন আমরা employees Table এর সাথে customers Table এর right join করব।
এখানে আমরা employees table থেকে সব ইমপ্লয়ীদের লিস্ট নিয়ে আসব। সাথে যদি তারা কোনো customer কে সাপোর্ট দিয়ে থাকে , সেই কাস্টমার এর Customer Number টি show করব।
এক্ষেত্রে যেহেতু আমরা employees Table এর সাথে customers Table এর right join করতেছি , সেজন্য এখানে employees Table থেকে সব ইমপ্লয়ীদের লিস্ট আসবে। সেই সাথে যদি এইসব ইমপ্লয়ীদের কেও কোনো কাস্টমারকে কখনো কোনো সাপোর্ট দিয়ে থাকে , তাহলে সেই সাথে সেই কাষ্টমারদের Customer Number টিও show করবে:
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$results=DB::table('customers')
->rightJoin('employees', 'salesRepEmployeeNumber', '=', 'employeeNumber')
->select('employeeNumber', 'customerNumber')
->orderBy('employeeNumber')
->get();
echo "<pre>";
print_r($results);
echo "</pre>";
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select `employeeNumber`, `customerNumber` from `customers` right join `employees` on `salesRepEmployeeNumber` = `employeeNumber` order by `employeeNumber` asc
নিম্নের ছবিটিতে আংশিক আউটপুট দেখানো হলো :
এখন যদি আমরা চাই , যে একজন employee যতজন কাস্টমারকে সাপোর্ট দিয়েছে , তাদের লিস্ট দেখতে চাই। সেক্ষেত্রে উপরোক্ত কোডের সাথে where clause যুক্ত করে দিলেই হবে:
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$results=DB::table('customers')
->rightJoin('employees', 'salesRepEmployeeNumber', '=', 'employeeNumber')
->select('employeeNumber', 'customerNumber')
->orderBy('employeeNumber')
->where('employeeNumber','=','1165')
->get();
echo "<pre>";
print_r($results);
echo "</pre>";
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select `employeeNumber`, `customerNumber` from `customers` right join `employees` on `salesRepEmployeeNumber` = `employeeNumber` where `employeeNumber` = '1165' order by `employeeNumber` asc
নিম্নের ছবিটিতে আংশিক আউটপুট দেখানো হলো :
আবার যদি আমরা এভাবে চাই, যে সেসব এমপ্লয়িদের লিস্ট দেখব , যারা কখনো কোনো কাস্টমার সাপোর্ট দেয়নাই। তখন আমাদের কোডগুলো হবে নিম্নরুপঃ
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$results=DB::table('customers')
->rightJoin('employees', 'salesRepEmployeeNumber', '=', 'employeeNumber')
->select('firstName','lastName','employeeNumber')
->whereNull('customerNumber')
->orderBy('employeeNumber')
->get();
echo "<pre>";
print_r($results);
echo "</pre>";
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select `firstName`, `lastName`, `employeeNumber` from `customers` right join `employees` on `salesRepEmployeeNumber` = `employeeNumber` where `customerNumber` is null order by `employeeNumber` asc
Self Join Clause in Laravel Query Builder
একটি ডাটাবেস টেবিলে self join হচ্ছে এক ধরণের join operation যেখানে একটি টেবিল নিজের সাথে নিজেকে join করা হয়। এর মানে হল যে একই টেবিলটি ক্যোয়ারীতে দুবার ব্যবহার করা হবে, আর টেবিলের প্রতিটি instance কে একে অপরের থেকে আলাদা করার জন্য একটি unique alias দিতে হবে।
Self join গুলো সাধারণত ব্যবহৃত হয় যখন একটি টেবিলে hierarchical ডেটা থাকে, যেমন employees একটি টেবিল যেখানে প্রতিটি row একজন employee অন্য কোনো employee কে কে report করে এবং একটি কলাম থাকে যা উক্ত employee যে অন্য যে ইমপ্লয়ীকে রিপোর্ট করে, তার ID দেওয়া থাকে একটি Self join ব্যবহার করে, একজন employee এর রিপোর্টিং বস এবং রিপোর্টিং বসের রিপোর্টিং বস এবং আরও অনেক কিছু সম্পর্কে তথ্য বের করা সম্ভব।
একটি self join করতে, আপনাকে SQL ক্যোয়ারীতে একই টেবিলের জন্য দুটি alias নির্দিষ্ট করতে হবে। একটি alias জয়েনের “left” দিকটি প্রতিনিধিত্ব করবে এবং অন্যটি জয়েনের “right” দিকটি উপস্থাপন করে। তারপরে আপনি দুটি alias এর মধ্যে join condition টি নির্দিষ্ট করতে পারেন, ঠিক যেমন আপনি দুটি ভিন্ন টেবিলের মধ্যে নিয়মিত join এর কাজটি করেন।
প্রথমে চলুন উপরের বর্ণনা অনুযায়ী আমাদের employees table এর স্ট্রাকচার টি দেখা নেওয়া যাক:
এখন আমরা দেখব , উক্ত টেবিল কোন এমপ্লয়ীর কে রিপোর্টিং বস, আর এর জন্য আমরা নিম্নোক্ত Laravel Inner Join Method ব্যবহার করব : :
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$results=DB::table('employees AS e')
->Join('employees AS m','m.employeeNumber','=','e.reportsTo')
->selectRaw("e.employeeNumber AS 'Manager Id', CONCAT(e.firstName,' ',e.lastname) AS 'Manager',m.employeeNumber AS 'Boss Id',CONCAT(m.firstName,' ',m.lastName) AS 'Reporting Boss'")
->orderBy('Manager')
->get();
echo "<pre>";
print_r($results);
echo "</pre>";
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select e.employeeNumber AS 'Manager Id', CONCAT(e.firstName,' ',e.lastname) AS 'Manager',m.employeeNumber AS 'Boss Id',CONCAT(m.firstName,' ',m.lastName) AS 'Reporting Boss' from `employees` as `e` inner join `employees` as `m` on `m`.`employeeNumber` = `e`.`reportsTo` order by `Manager` asc
নিম্নের ছবিটিতে আংশিক আউটপুট দেখানো হলো :
self join example using LEFT JOIN clause
উপরের উদাহরণে লক্ষ্য করবেন President এর নাম employee লিস্ট এ আসেনি। কারণ President কাউকে রিপোর্ট করা লাগেনা, বার তার কোনো রিপোর্টিং বস নেই। এক্ষেত্রে আপনি যদি President এর নামও employee list এ নিয়ে আসতে চান , সেক্ষেত্রে আপনাকে left Join এর সাহায্য নিতে হবে :
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select IFNULL(CONCAT(m.lastname, ', ', m.firstname), 'Top Manager') as Manager, CONCAT(e.lastname, ', ', e.firstname) as 'Direct report' from `employees` as `e` left join `employees` as `m` on `m`.`employeeNumber` = `e`.`reportsto` order by `Manager` desc
উপরোক্ত query তে, আমরা প্রথমে left join ব্যবহার করে employee দের টেবিলে নিজের সাথে join করি। তারপরে আমরা select clause তৈরি করতে SelectRaw() method ব্যবহার করি, যেখানে আমরা IFNULL() ফাংশন ব্যবহার করি সে সব কেসগুলি পরিচালনা করার জন্য যখন একজন employee এর কোন manager থাকে না (সেক্ষেত্রে, আমরা কারো নামের পরিবর্তে “Top Manager” প্রদর্শন করি)। আমরা CONCAT() ফাংশন ব্যবহার করে employee এবং তাদের manager দের last এবং first name Concat করি।
অবশেষে, আমরা orderByDesc() method ব্যবহার করে Manager কলাম দ্বারা সেট করা ফলাফলকে descending order করি এবং get() method ব্যবহার করে রেকর্ডগুলি বের করে আনি।
নিম্নের ছবিটিতে আংশিক আউটপুট দেখানো হলো :
self join to compare successive rows
self join ব্যবহার করে, আপনি customers টেবিলে join করে আপনি একই শহরে অবস্থানকারী কাস্টমারদের একটি list প্রদর্শন করতে পারেন:
DB::table('customers as c1')
->join('customers as c2', function($join){
$join->on('c1.city', '=', 'c2.city');
$join->on('c1.customername', '>', 'c2.customername');
})
->select('c1.city', 'c1.customername', 'c2.customername')
->orderBy('c1.city')
->get();
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
select `c1`.`city`, `c1`.`customername`, `c2`.`customername` from `customers` as `c1` inner join `customers` as `c2` on `c1`.`city` = `c2`.`city` and `c1`.`customername` > `c2`.`customername` order by `c1`.`city` asc
উপরোক্ত Query তে , আমরা প্রথমে একটি inner join ব্যবহার করে customers Table কে নিজের সাথে নিজেকে join করি। আমরা on() method ব্যবহার করে join condition শর্তাবলী নির্দিষ্ট করতে একটি closure ব্যবহার করি।
তারপর, আমরা select() method ব্যবহার করে কাঙ্খিত কলাম select করি।
অবশেষে, আমরা orderBy() method ব্যবহার করে c1.city দ্বারা সেট করা result অর্ডার করি এবং get() method ব্যবহার করে রেকর্ডগুলি নিয়ে আসি।
নিম্নের ছবিটিতে আংশিক আউটপুট দেখানো হলো :
CROSS JOIN in Laravel Query Builder
Cross Join (যা Cartesian Join নামেও পরিচিত) হল একটি join operation যা একটি result set তৈরি করে যা দুটি টেবিলের গুণফল। এটি উভয় টেবিল থেকে rows গুলির সব possible combinations প্রদান করে যখন কোনো join condition নির্দিষ্ট করা না থাকে।
ধরুন আপনি CROSS JOIN ক্লজ ব্যবহার করে দুটি টেবিলে join দিয়েছেন। ফলাফল সেটে উভয় টেবিলের সমস্ত row গুলো অন্তর্ভুক্ত থাকবে, যেখানে প্রতিটি row হল প্রথম টেবিলের row এর সাথে দ্বিতীয় টেবিলের row এর combination । সাধারণভাবে, প্রতিটি টেবিলে যথাক্রমে n এবং m row থাকলে, ফলাফল সেটে nXm row থাকবে।
নিম্নে CROSS JOIN clause এর syntax দেওয়া হলো:
SELECT * FROM t1 CROSS JOIN t2;
মনে রাখবেন যে INNER JOIN, LEFT JOIN , এবং RIGHT JOIN clauses গুলি থেকে ভিন্ন কারণ এতে ON বা USING clause নেই।
যদি আপনি একটি WHERE clause যোগ করেন, যদি টেবিল t1 এবং t2 এর মধ্যে relationship থাকে, তাহলে CROSS JOIN নিম্নলিখিত ক্যোয়ারীতে দেখানো INNER JOIN clause এর মতো কাজ করে:
SELECT * FROM t1 CROSS JOIN t2 WHERE t1.id = t2.id;
CROSS JOIN clause examples in Laravel Query Builder
প্রথমে চলুন CROSS JOIN clause প্রদর্শনের জন্য সম্পূর্ণ নতুন একটি ডেটাবেসে কিছু টেবিল সেট আপ করা যাক:
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100),
price DECIMAL(13,2 )
);
CREATE TABLE stores (
id INT PRIMARY KEY AUTO_INCREMENT,
store_name VARCHAR(100)
);
CREATE TABLE sales (
product_id INT,
store_id INT,
quantity DECIMAL(13 , 2 ) NOT NULL,
sales_date DATE NOT NULL,
PRIMARY KEY (product_id , store_id),
FOREIGN KEY (product_id)
REFERENCES products (id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (store_id)
REFERENCES stores (id)
ON DELETE CASCADE ON UPDATE CASCADE
);
নিম্নে তিনটি টেবিলের বর্ণনা দেওয়া হলো:
- products টেবিলে product গুলোর মাস্টার ডেটা রয়েছে যেমন, product id, product name, এবং sales price রয়েছে।
- stores টেবিলে product গুলো কোন store এ রয়েছে , সেই store এর নাম গুলো রয়েছে।
- sales টেবিলে sales সম্পর্কৃত বিস্তারিত যেমন , product_id, store_id ,quantity এবং sales_date রয়েছে।
অবশেষে, তিনটি টেবিলে নিম্নোক্ত data গুলো insert করান। ধরুন আমাদের তিনটি প্রোডাক্ট iPhone, iPad এবং Macbook Pro রয়েছে যা North এবং South দুটি stores থেকে বিক্রি হয়।
INSERT INTO products(product_name, price)
VALUES('iPhone', 699),
('iPad',599),
('Macbook Pro',1299);
INSERT INTO stores(store_name)
VALUES('North'),
('South');
INSERT INTO sales(store_id,product_id,quantity,sales_date)
VALUES(1,1,20,'2017-01-02'),
(1,2,15,'2017-01-05'),
(1,3,25,'2017-01-05'),
(2,1,30,'2017-01-02'),
(2,2,35,'2017-01-05');
মোটামুটি আমাদের তিনটি টেবিলের রিলেশন এবং তাদের স্ট্রাকচার হবে নিম্নরূপ:
এবার আমরা উক্ত টেবিল গুলো থেকে , সমন্বিতভাবে প্রতিটি product এর total sales কত এবং কোন store থেকে সেলস গুলো হয়েছে তা রিটার্ন করবে। আর এর জন্য আমাদেরকে Laravel Inner Join ব্যবহার করতে হবে :
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$results=DB::table('sales')
->join('products', 'products.id', '=', 'sales.product_id')
->join('stores', 'stores.id', '=', 'sales.store_id')
->select('store_name', 'product_name', DB::raw('SUM(quantity * price) AS revenue'))
->groupBy('store_name', 'product_name')
->get();
echo "<pre>";
print_r($results);
echo "</pre>";
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
SELECT
store_name,
product_name,
SUM(quantity * price) AS revenue
FROM
sales
INNER JOIN
products ON products.id = sales.product_id
INNER JOIN
stores ON stores.id = sales.store_id
GROUP BY store_name , product_name;
নিম্নের ছবিটিতে আংশিক আউটপুট দেখানো হলো :
এখন, আপনি যদি জানতে চান যে কোন store একটি নির্দিষ্ট পণ্য কোনো বিক্রি হয়নি। উপরের query টি এই প্রশ্নের উত্তর দিতে পারেনি।
আর এই ধরণের সমস্যা সমাধানের জন্য, আপনাকে CROSS JOIN ক্লজ ব্যবহার করতে হবে।
use Illuminate\Support\Facades\DB;
Route::get('/query', function () {
$results=DB::table('products as a')
->crossJoin('stores as b')
->leftJoin(DB::raw('(SELECT
stores.id AS store_id,
products.id AS product_id,
store_name,
product_name,
ROUND(SUM(quantity * price), 0) AS revenue
FROM sales
INNER JOIN products ON products.id = sales.product_id
INNER JOIN stores ON stores.id = sales.store_id
GROUP BY stores.id, products.id, store_name, product_name) as c'),
function ($join) {
$join->on('c.store_id', '=', 'b.id')
->on('c.product_id', '=', 'a.id');
})
->select('b.store_name', 'a.product_name', DB::raw('IFNULL(c.revenue, 0) as revenue'))
->orderBy('b.store_name')
->get();
echo "<pre>";
print_r($results);
echo "</pre>";
});
উপরের উদাহরণটি নিম্নলিখিত SQL তৈরি করবে:
SELECT
b.store_name,
a.product_name,
IFNULL(c.revenue, 0) AS revenue
FROM
products AS a
CROSS JOIN
stores AS b
LEFT JOIN
(SELECT
stores.id AS store_id,
products.id AS product_id,
store_name,
product_name,
ROUND(SUM(quantity * price), 0) AS revenue
FROM
sales
INNER JOIN products ON products.id = sales.product_id
INNER JOIN stores ON stores.id = sales.store_id
GROUP BY stores.id, products.id, store_name , product_name) AS c ON c.store_id = b.id
AND c.product_id= a.id
ORDER BY b.store_name;
নিম্নের ছবিটিতে আংশিক আউটপুট দেখানো হলো :
Masud Alam
আমি মাসুদ আলম, বাংলাদেশের ৩৬ তম Zend Certified Engineer । ২০০৯ সালে কম্পিউটার সাইন্স থেকে বেচেলর ডিগ্রী অর্জন করি। দীর্ঘ ১৫ বছর আমি Winux Soft, SSL Wireless, IBCS-PRIMAX, Max Group, Canadian International Development Agency (CIDA), Care Bangladesh, World Vision, Hellen Keller, Amarbebsha Ltd সহ বিভিন্ন দেশি বিদেশী কোম্পানিতে ডেটা সাইন্স, মেশিন লার্নিং, বিগ ডেটা, ওয়েব ডেভেলপমেন্ট এবং সফটওয়্যার ডেভেলপমেন্ট এর উপর বিভিন্ন লিডিং পজিশন এ চাকরি এবং প্রজেক্ট লিড করি। এছাড়াও বাংলাদেশের ১৮৫ জন জেন্ড সার্টিফাইড ইঞ্জিনিয়ার এর মধ্যে ১২০ এরও অধিক ছাত্র আমার হাতে জেন্ড সার্টিফাইড ইঞ্জিনিয়ার হয়েছেন। বর্তমানে w3programmers ট্রেনিং ইনস্টিটিউট এ PHP এর উপর Professional এবং Advance Zend Certified PHP -8.2 Engineering, Laravel Mastering Course with ReactJS, Python Beginning To Advance with Blockchain, Machine Learning and Data Science, Professional WordPress Plugin Development Beginning to Advance কোর্স করাই। আর অবসর সময়ে w3programmers.com এ ওয়েব টেকনোলজি নিয়ে লেখালেখি করি।