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

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 ส่งผลให้ผลลัพท์ที่ได้ไม่ถูกต้อง