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

Navigation
1

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)

Considering that you have the following data for the modules table: 

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

And for the lessons table 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 fetching a class: 

SELECT * FROM lessons WHERE id = 8

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

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

I've already tried based on the position of the lessons table pick up the next lesson like this: 

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

In case the number 4 is the position of the lesson with id 8. Only of course it has not picked up since it has no position greater than 4.

I also tried in the position column of the lessons table 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 that would not be a good option since the ordering of modules and lessons can be changed.

I would like to know how to do the best. If I have to think about another database structure or how to do queries to get the previous lesson and the next lesson.

Thank you.

    
asked by anonymous 22.08.2014 / 00:22

1 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 . At the time of sorting, it first considers the module after the position within the module.

    
22.08.2014 / 00:43
Format cell in DataGrid at runtime C # Add thousandths in the jquery counter