ปัญหาการค้นหาบทความที่เกี่ยวข้อง

SELECT *
FROM (
    SELECT * FROM (
        (
            SELECT @row:=@row+1 AS `row`,Q3.* FROM (
                SELECT * FROM (
                    SELECT .....
                    FROM `poo_index`
                    WHERE `id` > 3 AND (`relate` LIKE '%related%')
                    ORDER BY `create_date` ASC
                ) AS Q2, (SELECT @row:=0) r) AS Q3
        ) UNION (
            SELECT @row2:=@row2+1 AS `row2`,Q3.* FROM (
                SELECT * FROM (
                    SELECT  .....
                    FROM `poo_index`
                    WHERE `id` < 3 AND (`relate` LIKE '%related%')
                    ORDER BY `create_date` DESC
            ) AS Q2, (SELECT @row2:=0) r) AS Q3
        )
    ) AS X ORDER BY X.`row` LIMIT 2
) AS Y ORDER BY `create_date` DESC

โค้ดนี้ทำเอาผมมึนไปวันหนึ่งเต็มๆเลยครับ มันเป็นโค้ดสำหรับ query หาบทความใกล้เคียง (Related Topics) มาแสดงผล (อยู่ใน Widget Realte ของ GCMS)

ก่อนอื่นผมขอสรุปปัญหาที่ผมพบดังนี้นะครับ
  • ทดสอบการ Query บน phpMyAdmin บนเครื่อง Server ✓
  • ทดสอบการ Query ผ่านหน้าเว็บไซต์ บนเครื่อง Server ✓
  • ทดสอบการ Query บน phpMyAdmin บน localhost ✓
  • ทดสอบการ Query ผ่านหน้าเว็บไซต์ บน localhost ✘
จะเห็นว่ามีความผิดพลาดเพียงที่เดียวคือผ่านเว็บไซต์บน localhost ซึ่งข้อผิดพลาดที่พบคือ ให้ผลลัพท์ผิดพลาด (ไม่เหมือนที่อื่น) โดยที่ไม่มี Error
จากการตรวจสอบ Query บน Query Log ตลอดจนการ debug ด้วย วิธีต่างๆ ไม่พบข้อผิดพลาด แต่ประการใด

ข้อมูล Server ที่มีปัญหา
  • PHP Server : PHP Version 5.6.19-1+deb.sury.org~trusty+1
  • MySQL Server Server version: 5.5.47-MariaDB-1ubuntu0.14.04.1-log - (Ubuntu)

การแก้ปัญหา
จริงๆการแก้ปัญหาก็มีหลายวิธีนะครับ วิธีแรกที่ผมเลือกใช้คือ การแตก query ออก และมาจัดการแสดงผลด้วย PHP อีกที วิธีนี้ก็เป็นการแก้ปัญหาแบบตรงไปตรงมา ใช้วิธีพื้นฐานที่สุด แต่ผมกลับพบว่า การจัดกลุ่มบทความที่เกี่ยวข้องเพื่อให้ได้ผลลัพท์ที่ถูกต้อง ซับซ้อนเกินไป อันเนื่องมาจากเงื่อนไขที่ซับซ้อน ผมจึงกลับมาหาวิธีแก้ปัญหาด้วยการ Query อีกครั้ง แต่ครั้งนี้จะเปลี่ยนวิธีเขียนโดยการไปศึกษาเพิ่มเติมทฤษฎีการ Query
ID topic relate create_date
1 บทที่ 1 related 10000
2 บทที่ 2 related 10001
3 บทที่ 3 related 10002
4 บทที่ 4 related 10003
5 บทที่ 5 related 10004
ตารางด้านบนเป็นตัวอย่างข้อมูลนะครับ

แนวคิด ของการแสดงบทความที่เกี่ยวข้องคือ
  • บทความที่เกี่ยวข้องหาได้จากฟิลด์ relate โดยที่ ฟิลด์ที่มีข้อมูลเดียวกันจะถือว่าเกี่ยวข้องกัน
  • ฟิลด์ create_date ใช้กำหนดลำดับของบทความ แนวคิดคือ บทความแรกจะต้องเขียนก่อนบทความหลังๆเสมอ (บทที่ 1 เขียนขึ้นก่อน บทที่ 2)
ดังนั้นการ query บทความใกล้เคียงก็คือ การ query บทความที่มี relate ตรงกัน เรียงตามลำดับ วันที่สร้าง

แต่ปัญหาไม่ได้จบแค่นั้น เพราะโดยปกติแล้ว ถ้าเราต้องการบทความใกล้เคียง สักสองบทความ เราจะต้องการบทความก่อนหน้า 1 บทความ และบทความหลังจากนี้อีก 1 บทความ เช่น หากกำลังดู บทที่ 3 อยู่ บทความใกล้เคียงควรเป็น บทที่ 2 และ บทที่ 4

วิธีการแก้ปัญหา
  • แบ่ง query ออกเป็น 2 ส่วน คือ บทความก่อนหน้า และบทความถัดไป
  • ใส่เลขลำดับให้กับบทความ ทั้งสองส่วน
row_number ID topic relate create_date
2 1 บทที่ 1 related 10000
1 2 บทที่ 2 related 10001
3 บทที่ 3 related 10002
1 4 บทที่ 4 related 10003
2 5 บทที่ 5 related 10004

จะเห็นว่ารายการที่เราต้องการคือรายการที่มี row_number น้อยที่สุด 2 รายการ นั่นเอง

รู้ทฤษฏีไปแล้ว ต่อมาเราก็ต้องมาแปลงจากทฤษฎีให้เป็นโค้ด

อย่างแรกเลยก็คือการใส่ row_number ลงใน Query ซึ่งผมเดาว่านี่คือปัญหาของโค้ดด้านบนที่ผิดพลาด ดังนั้นผม เลยเลือกที่จะไปศึกษาวิธีการใส่ row_number ใหม่ โดยไม่ใช้วิธีเดิม โดยเริ่มต้นจากการศึกษาทฤษฎีในการใส่ row_number
SET @row = 0;
SELECT @row := @row + 1 AS `row_number`, table.field ....
    FROM table
    WHERE ....

โค้ดด้านบนคือคำสั่งในการใส่ row_number ในรูปของฟังก์ชั่น ซึ่งผมนำมาแปลงเป็น query ได้ดังนี้
SELECT @row := @row + 1 AS `row_number`, Q.* FROM (SELECT ... FROM ... WHERE ...) AS Q, (SELECT @row := 0) AS R
อย่างที่สองคือ สร้าง Query นี้ 2 ครั้ง โดยครั้งแรกเป็นรายการก่อนหน้า (ID < 3) และครั้งที่สองคือรายการถัดไป (ID > 3) เสร็จแล้วเอาทั้งสอง Query มารวมกัน ด้วย UNION และเรียงลำดับด้วย row_number
SELECT * FROM (
    (SELECT @row1 := @row1 + 1 AS `row_number`, Q.* FROM (SELECT * FROM table WHERE `id` < 3) AS Q, (SELECT @row1 := 0) AS R)
    UNION
    (SELECT @row2 := @row2 + 1 AS `row_number`, Q.* FROM (SELECT * FROM table WHERE `id` > 3) AS Q, (SELECT @row2 := 0) AS L)
) AS Q ORDER BY `row_number`

ถึงตอนนี้ก็จะได้ผลลัพท์แบบนี้
row_number ID topic relate create_date
1 2 บทที่ 2 related 10001
1 4 บทที่ 4 related 10003
2 1 บทที่ 1 related 10000
2 5 บทที่ 5 related 10004
ถัดจากนี้ ก็จำกัดให้เหลือเพียง 2 รายการที่ต้องการ คือ 2 รายการแรกเท่านั้น
SELECT Y.* FROM (
    SELECT * FROM (
        (
            SELECT @n:=@n+1 AS `row`,Q.* FROM (
                SELECT .....
                FROM `poo_index`
                WHERE `id` > 3 AND (`relate` LIKE '%related%')
                ORDER BY `create_date`
            ) AS Q, (SELECT @n:=0) AS R
        ) UNION (
            SELECT @m:=@m+1 AS `row`,Q.* FROM (
                SELECT .....
                FROM `poo_index`
                WHERE `id` < 3 AND (`relate` LIKE '%related%')
                ORDER BY `create_date` DESC
            ) AS Q, (SELECT @m:=0) AS L
        )
    ) AS N ORDER BY N.`row` LIMIT 2
) AS Y ORDER BY Y.`create_date`

ผลลัพท์ของการ Query อ้นล่างสุดตรงกันในทุกที่ครับ
สรุปอีกครั้งถึงปัญหาความผิดพลาด เกิดขึ้นมาจากเวอร์ชั่นของ MySQL ครับ ซึ่งใน MySQL เวอร์ชั่นใหม่ๆ จะไม่สามารถเรียงลำดับข้อมูลใน Query ย่อยได้ ทำให้คำสั่ง ORDER BY ไม่สามารถทำงานได้ใน SUB Query ส่งผลให้ผลลัพท์ที่ได้ไม่ถูกต้อง
ผู้เขียน Goragod โพสต์เมื่อ 03 เม.ย. 2559 เปิดดู 1,076 ป้ายกำกับ SQL

เรื่องที่เกี่ยวข้อง

^