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

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 เปิดดู 3,110 ป้ายกำกับ SQL
^