Searching for the next record and the previous record using two columns for sorting different tables

Question

I have the following database structure:

___ Table: modules___
id int(10)
name varchar(50)
position int(10)

___ Table: lessons ___
id int(10)
module_id int(10) FK
position int(10)
name varchar(50)

Whereas I have the following data for the table modules:

1 - 1 - Módulo 1
2 - 2 - Módulo 2
3 - 3 - Módulo 3

And for the table lessons I have the following data:

1 - 1 - 1 - Aula 1
2 - 1 - 2 - Aula 2
3 - 1 - 3 - Aula 3
4 - 1 - 4 - Aula 4

5 - 2 - 1 - Aula 1
6 - 2 - 2 - Aula 2
7 - 2 - 3 - Aula 3
8 - 2 - 4 - Aula 4

9  - 3 - 1 - Aula 1
10 - 3 - 2 - Aula 2
11 - 3 - 3 - Aula 3
12 - 3 - 4 - Aula 4

So, I have the following query seeking a lesson:

SELECT * FROM lessons WHERE id = 8

In the above query the next class would be Lesson with id 9 and the previous class would be Lesson with id 7.

How to do the above querys, and the pagination will depend on the position of both table modules and table lessons?

I have tried based on the position of the table lessons catch the next class so:

SELECT * FROM lessons WHERE position > 4 ORDER BY position ASC LIMIT 1;

In case number 4 is the position of the Lesson with id 8. Of course it did not catch since it has no position greater than 4.

I also tried in the position column of the table lessons to save the values like this:

0000000001.0000000001
0000000001.0000000002
0000000001.0000000003
0000000001.0000000004

0000000002.0000000001
0000000002.0000000002
0000000002.0000000003
0000000002.0000000004

0000000003.0000000001
0000000003.0000000002
0000000003.0000000003
0000000003.0000000004

Following the standard MODULE_ID.LESSON_ID but I believe it would not be a good option since the ordering of modules and lessons can be changed.

I wish I knew how to do it right. If I have to think of another database structure or how to make the querys to get the previous Lesson and the next Lesson.

Thank you.

Answer 1

You first need to merge the two tables, and then establish the desired condition (same module and top/bottom position, or top/bottom module):

SELECT *
FROM lessons l
  JOIN modules m on l.module_id = m.id
WHERE (m.position = 2 AND l.position > 4) OR m.position > 2
ORDER BY m.position ASC, l.position ASC
LIMIT 1;

SELECT *
FROM lessons l
  JOIN modules m on l.module_id = m.id
WHERE (m.position = 2 AND l.position < 4) OR m.position < 2
ORDER BY m.position DESC, l.position DESC
LIMIT 1;

Example in Sqlfiddle. When ordering, it first considers the module after the position inside the module.