MySQL Recursive Query

১। Recursive Query’র ধারণা:

ধরা যাক, আমাদের নিম্নরূপ ক্যাটাগরি রয়েছে।

• জীবজগৎ
  • প্রাণিজগৎ
    • পশু
    • পাখি
    • মাছ
  • উদ্ভিদজগৎ
• জড়জগৎ
  • আসবাবপত্র
  • পানি
  • মাটি

এখন আমরা জীবজগৎ ক্যাটাগরির অধীনস্থ সকল সাব-ক্যাটাগরির তালিকা করতে চাই। তাহলে আমারদের এভাবে যেতে হবে :-

1. জীবজগৎ এর অধীনে কী কী সাব-ক্যাটাগরি রয়েছে।
2. উক্ত সাব-ক্যাটাগরিগুলোর অধীনে কী কী সাব-ক্যাটাগরি রয়েছে ।
3. উক্ত সাব-ক্যাটাগরিগুলোর অধীনে কী কী সাব-ক্যাটাগরি রয়েছে ।
4. উক্ত সাব-ক্যাটাগরিগুলোর অধীনে কী কী সাব-ক্যাটাগরি রয়েছে । (এভাবে শেষ পর্যন্ত চলতে থাকবে।)

এই কাজটাই আমরা MySQL Recursive Query-এর সাহায্যে করতে পারি। তাহলে চলুন কাজ শুরু করা যাক।

২। ডাটা টেবিল তৈরি:

প্রথমেই আমরা ডাটাবেসে categories নামে একটি টেবিল তৈরি করে কিছু ডাটা এন্ট্রি করবো। সর্বোচ্চ ক্যাটাগরির ক্ষেত্রে, অর্থাৎ যেসব ক্যাটাগরি অন্য কোনো ক্যাটাগরির অধীনে নয়, তাদের ক্ষেত্রে parent_id হিসেবে 0 (zero) ব্যবহৃত হয়েছে।

-- Table structure
CREATE TABLE `categories` (
  `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `category` varchar(50) NOT NULL,
  `parent_id` smallint(5) UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table data
INSERT INTO `categories` (`id`, `category`, `parent_id`) VALUES
(1, 'জীবজগৎ', 0),
(2, 'প্রাণিজগৎ', 1),
(3, 'পশু', 2),
(4, 'পাখি', 2),
(5, 'মাছ', 2),
(6, 'উদ্ভিদজগৎ', 1),
(7, 'জড়জগৎ', 0),
(8, 'আসবাবপত্র', 7),
(9, 'পানি', 7),
(10, 'মাটি', 7);

উপরিউক্ত কোডগুলো রান করলে ডাটাবেসে নিম্নরূপ টেবিল তৈরি হবে।

৩। সাব-ক্যাটাগরির তালিকা তৈরি:

জীবজগৎ ক্যাটাগরির অধীনস্থ সকল সাব-ক্যাটাগরির তালিকা করতে:-

WITH RECURSIVE cte (id, category) AS (
	SELECT id, category
	FROM categories
	WHERE parent_id = 1
	
	UNION ALL
	
	SELECT sub.id, sub.category
	FROM categories sub
	INNER JOIN cte
		ON sub.parent_id = cte.id
)
SELECT * FROM cte;

লাইন নম্বর ৪ (চার)-এ parent_id = 1 , যেখানে 1 হলো আমাদের প্যারেন্ট ক্যাটাগরি ‘জীবজগৎ’-এর আইডি। তাহলে নিচের মতো টেবিল তৈরি হবে।

উপরিউক্ত টেবিলে প্যারেন্ট ক্যাটাগরি ‘জীবজগৎ’ নেই। ‘জীবজগৎ’ সহ তালিকা তৈরি করতে চাইলে কোডটি কিছুটা পরিবর্তন করতে হবে। তবে তার আগে Recursive CTE-এর কোডগুলো বুঝে নিই।

৪। Recursive CTE-এর বর্ণনা:

লাইন ১: যেসব কলামের টেবিল হবে তা এখানে উল্লেখ করতে হবে।

WITH RECURSIVE cte (id, category) AS (
  

লাইন ২-৪: Recursive CTE-এর দুটি অংশ থাকে। একটি হলো পুনরাবৃত্তিহীন স্টেটমেন্ট, অপরটি পুনরাবৃত্তিমূলক স্টেটমেন্ট। ২-৪ লাইনে পুনরাবৃত্তিহীন স্টেটমেন্ট ব্যবহৃত হয়েছে। লাইন ৪-এ ভিত্তি/প্যারেন্ট ক্যাটাগরির আইডি ব্যবহৃত হয়েছে।

	SELECT id, category
	FROM categories
	WHERE parent_id = 1

লাইন ৮-১১: এটি হলো পুনরাবৃত্তিমূলক স্টেটমেন্ট। এর সাহায্যে পরবর্তী সাব-ক্যাটাগরিগুলো যুক্ত হয়।

	SELECT sub.id, sub.category
	FROM categories sub
	INNER JOIN cte
		ON sub.parent_id = cte.id

লাইন ১৩: SELECT স্টেটমেন্টের সাহায্যে তৈরিকৃত টেবিল অ্যাক্সেস করা হয়েছে।

৫। প্যারেন্ট ক্যাটাগরি অন্তর্ভুক্ত করা:

একটু আগেই জানলাম যে, Recursive CTE-এর দুটি অংশ থাকে। একটি হলো পুনরাবৃত্তিহীন স্টেটমেন্ট।

	SELECT id, category
	FROM categories
	WHERE parent_id = 1

আমাদের আগের কোডে পুনরাবৃত্তিহীন স্টেটমেন্ট দ্বারা কোনো রো (Row) তৈরি হয়নি। কেননা সর্বোচ্চ ক্যাটাগরির ক্ষেত্রে প্যারেন্ট ক্যাটাগরি আইডি 0 (Zero)। WHERE parent_id = 1 এর জায়গায় WHERE id = 1 বসালে ‘জীবজগৎ’ ক্যাটাগরি যোগ হবে।

WITH RECURSIVE cte (id, category) AS (
	SELECT id, category
	FROM categories
	WHERE id = 1
	
	UNION ALL
	
	SELECT sub.id, sub.category
	FROM categories sub
	INNER JOIN cte
		ON sub.parent_id = cte.id
)
SELECT * FROM cte;