I have 4 models in my django project:
Journey
-------
id
user
Journey_times
------------
journey_id
time_stage1
time_stage2
time_stage3
ManyToMany(task_history)
Task_history
------------
journey_id
task_id
timestamp
Task
----
name
When a journey is created, when a user arrives to a stage X, timestamp is saved in parameter time_stageX. The user does some tasks during his journey, and the times will be saved as well.
In an specific case, if I have this tables:
journey
| id | user |
|---|---|
| 1 | Carl |
| 2 | Elsa |
| 3 | Joe |
journey_times
| id | journey_id | time_stage1 | time_stage2 | time_stage3 |
|---|---|---|---|---|
| 1 | 1 | 10:00:00 | 11:00:00 | 12:00:00 |
| 2 | 2 | NULL | 15:00:00 | 16:00:00 |
| 3 | 3 | 18:00:00 | 19:00:00 | 20:00:00 |
task_history
| id | journey_id | task_id | timestamp |
|---|---|---|---|
| 1 | 1 | 1 | 11:15:00 |
| 2 | 1 | 2 | 11:30:00 |
| 3 | 2 | 2 | 15:30:00 |
task
| id | name |
|---|---|
| 1 | jump |
| 2 | swim |
(ManyToMany creates a relation) journey_times_task_history
| id | journey_times_id | task_history_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
I want to make a query that return all the times of stages and tasks, like:
Query result:
| journey_id | time_stage1 | time_stage2 | time_stage3 | jump | swim |
|---|---|---|---|---|---|
| 1 | 10:00:00 | 11:00:00 | 12:00:00 | 11:15:00 | 11:30:00 |
| 2 | NULL | 15:00:00 | 16:00:00 | NULL | 15:30:00 |
| 3 | 18:00:00 | 19:00:00 | 20:00:00 | NULL | NULL |
