Membuat query recursive untuk SQLite

Istilah recursive pada pemrograman digunakan apabila sebuah rutin memanggil rutin itu sendiri. Recursive berguna ketika kita perlu men-traverse data struktur berbentuk tree, linked list dari satu node ke node lain.

Apabila pada memory referensi ke node lain dapat berupa pointer, Pada sebuah tabel SQL referensi tersebut dapat berupa sebuah FOREIGN KEY yang menunjuk id row lain pada tabel yang sama.

Misal pada sebuah tabel employee

CREATE TABLE employee (
  	id INTEGER PRIMARY KEY,
	name TEXT,
  	manager INTEGER,
    FOREIGN KEY(manager) REFERENCES employee(id)
);

Tabel employee menunjukan daftar pegawai dan managernya. Yang apabila digambarkan dalam tree berbentuk:

Andi
  ├── Budi
  │   ├── David
  │   └── Evan
  └── Caca

Andi adalah root karena tidak memiliki manager diatasnya. Andi adalah manager dari Budi, Budi adalah manager dari David dan seterusnya.

Lalu bagaimana caranya untuk mengambil informasi seorang employee dan semua bawahannya?

Praktek

Buat tabel employee masukan data sample dengan query berikut

INSERT INTO employee(name, manager) 
VALUES 
	('Andi', NULL), 
	('Beni',1),
	('David',2),
	('Caca',1),
	('Evan',2);

Maka apabila sesuai table employee akan memiliki data sebagai berikut:

Dan inilah recursive query untuk mencari employee dengan suatu id = 1 (Andi) dan semua bawahannya.

WITH RECURSIVE
traverse(id, name, manager) AS (
 SELECT id, name, manager FROM employee WHERE employee.id = 1
 UNION ALL
 SELECT employee.id, employee.name, employee.manager 
  FROM employee JOIN traverse
  ON employee.manager = traverse.id
)
SELECT * FROM traverse;

Hasil run

| id  | name   | manager  |
|-----|--------|----------|
|  1  | Andi   | NULL     |
|  2  | Beni   | 1        |
|  3  | David  | 1        |
|  4  | Caca   | 2        |
|  5  | Evan   | 2        |

Apabila kita ingin mengambil Beni (id=2) dan semua bawahannya maka ganti employee.id pada SELECT id, name, manager FROM employee WHERE employee.id = 1 dengan 2 menjadi SELECT id, name, manager FROM employee WHERE employee.id = 2

Hasil run

| id  | name   | manager  |
|-----|--------|----------|
|  2  | Beni   | 1        |
|  3  | David  | 1        |
|  5  | Evan   | 2        |

Penjelasan

SELECT id, name, manager FROM employee WHERE employee.id = 1 adalah root dari query. Ini adalah employee atau node teratas pada pencarian. Ganti employee.id = 1 untuk menentukan root dari query ini.

Bagian recursive

UNION ALL
 SELECT employee.id, employee.name, employee.manager 
  FROM employee JOIN traverse
  ON employee.manager = traverse.id

Ketika me-UNION table employee dengan traverse. Dengan ON employee.manager = traverse.id kita mencari employee dimana managernya adalah employee yang sudah ada di dalam table traverse. Di dalam contoh ini, pada iterasi pertama sudah didapat andi dengan id 1. Maka pada traverse selanjutnya akan mengambil employee yang memiliki nilai manager yang sama dengan id Andi.