Post kali ini, saya ingin membahas contoh kasus yang ditanyakan di stackoverflow.
Dimana pertanyaan inti-nya adalah, bagaimana menampilkan data post yang memiliki relasi terhadap tag dalam table relasi, namun yang diinginkan adalah melakukan filter post yang memiliki beberapa tag, bukan hanya tag tertentu saja.
Misalnya,
- post “Hallo Dunia” memiliki tag “tag1”
- post “Apa Kabar” memiliki tag “tag2”
- post “Pilih Saya” memiliki tag “tag1” DAN “tag2”
Nah, yang diinginkan adalah menampilkan post yang memiliki 2 tag “tag1” dan “tag2”. Bukan salah satu tag saja, di contoh di atas, akan muncul post “Pilih Saya”.
Saya mendesain ulang contoh tabel yang digunakan seperti di bawah ini.
Table yang digunakan
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | konglie=# \d List OF relations Schema | Name | TYPE | Owner --------+-----------------+-------+--------- public | ppost | TABLE | konglie public | ptags | TABLE | konglie public | rel_ppost_ptags | TABLE | konglie (3 ROWS) konglie=# \d ppost TABLE "public.ppost" COLUMN | TYPE | Modifiers --------+-----------------------+----------- pid | INTEGER | pname | CHARACTER VARYING(20) | konglie=# \d ptags TABLE "public.ptags" COLUMN | TYPE | Modifiers --------+-----------------------+----------- tid | INTEGER | tname | CHARACTER VARYING(20) | konglie=# \d rel_ppost_ptags TABLE "public.rel_ppost_ptags" COLUMN | TYPE | Modifiers --------+---------+----------- pid | INTEGER | tid | INTEGER | |
Sample Data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | konglie=# SELECT * FROM ppost ; pid | pname -----+-------- 100 | post 1 101 | post 2 102 | post 3 (3 ROWS) konglie=# SELECT * FROM ptags ; tid | tname ------+------- 1000 | tag1 1001 | tag2 1002 | tag3 1004 | tag5 1005 | tag6 1003 | tag4 (6 ROWS) konglie=# SELECT * FROM rel_ppost_ptags ; pid | tid -----+------ 100 | 1000 * 100 | 1001 * 100 | 1003 * 101 | 1000 ** 101 | 1001 ** 102 | 1001 102 | 1002 102 | 1001 101 | 1001 101 | 1003 ** 102 | 1001 (11 ROWS) |
Data yang diinginkan adalah bagaimana mengambil data atau post, yang memiliki beberapa tag tertentu. Misalnya, tampilkan data post yang memiliki tag tag1,tag2, dan tag4. Data yang hanya memiliki salah satu tag tersebut tidak ikut ditampilkan. Hanya post yang memiliki ketiga tag tersebut saja, yang akan ditampilkan.
Dalam contoh ini, post yang diinginkan adalah post dengan pid 100 dan 101, perhatikan yang diberi tanda * dan ** di atas.
Solusi yang terpikirkan adalah sebagai berikut.
1. Tentukan tid dari tag yang diinginkan, dalam contoh ini, adalah mengambil tid dari table ptags berdasarkan tname.
1 2 3 4 5 6 7 | konglie=# SELECT tid FROM ptags WHERE tname IN ('tag1', 'tag2', 'tag4'); tid ------ 1000 1001 1003 (3 ROWS) |
2. Kumpulkan terlebih dahulu seluruh post yang memiliki tag tag yang diinginkan. Sampai tahap ini, post dengan tag yang tidak lengkap pun akan dimunculkan.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | konglie=# SELECT pid, tid FROM rel_ppost_ptags WHERE tid IN ( SELECT tid FROM ptags WHERE tname IN ('tag1', 'tag2', 'tag4' ) ); pid | tid -----+------ 100 | 1000 100 | 1001 100 | 1003 101 | 1000 101 | 1001 102 | 1001 * 102 | 1001 * 101 | 1001 101 | 1003 102 | 1001 * (10 ROWS) |
Perhatikan baris diberi tanda *, yang merupakan data-data yang terduplikasi. Sebenarnya data ini hanya memberi contoh saja, dalam kenyataannya, seharusnya hal ini tidak akan terjadi, dan table rel_ppost_ptags akan lebih baik jika memberikan batasan UNIQUE atau PRIMARY KEY pada 2 field, pid dan tid. Sehingga tidak akan ada data yang menduplikasi pid dan tid secara bersamaan.
Untuk itu, akan kita tambahkan keyword distinct
1 2 3 4 5 6 7 8 9 10 11 | konglie=# SELECT DISTINCT pid, tid FROM rel_ppost_ptags WHERE tid IN ( SELECT tid FROM ptags WHERE tname IN ('tag1', 'tag2', 'tag4' ) ); pid | tid -----+------ 100 | 1001 100 | 1003 102 | 1001 101 | 1000 100 | 1000 101 | 1001 101 | 1003 (7 ROWS) |
Dengan distinct, baris baris yang terduplikasi akan dihilangkan.
3. Hitung jumlah data berdasarkan pid
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | konglie=# SELECT pid, COUNT(*) FROM ( SELECT DISTINCT pid, tid FROM rel_ppost_ptags WHERE tid IN ( SELECT tid FROM ptags WHERE tname IN ('tag1', 'tag2', 'tag4' ) ) ) tmp GROUP BY pid; pid | COUNT -----+------- 101 | 3 100 | 3 102 | 1 (3 ROWS) |
4. Pada langkah kedua, kita sudah menampilkan data pid dan tid sesuai dengan tag yang kita inginkan, hanya saja belum menentukan apakah sebuah pid memiliki semua kriteria tag yang kita inginkan. Namun, kita dapat pastikan bahwa, apabila pid tersebut memiliki semua tag yang kita inginkan, maka jumlah pid dari hasil sql tersebut akan ada sebanyak n buah, dimana n adalah jumlah tag yang kita inginkan, dalam contoh ini adalah 3. Nah, pada langkah di atas, kita sudah menghitung jumlah pid yang ada, langkah terakhir ini hanya memfilter semuah pid yang berjumlah n tadi saja, dengan memberikan keyword having.
konglie=# select
pid, count(*)
from (
select distinct pid, tid from rel_ppost_ptags where tid in (
select tid from ptags where tname in ('tag1', 'tag2', 'tag4' )
)
) tmp
group by pid having count(*) = 3;
pid | count
-----+-------
101 | 3
100 | 3
(2 rows)
Dengan demikian, kita sudah dapat memfilter post yang memiliki tag-tag tertentu secara keseluruhan, bukan hanya memiliki salah satu tag saja.
Sebagai tambahan informasi, sql di atas diujicobakan menggunakan Database PostgreSQL 9.4, namun saya rasa tidak ada fungsi spesific database di atas sehingga besar kemungkinan akan dapat digunakan di database server lainnya.
Semoga bermanfaat.