48 lines
2.4 KiB
MySQL
48 lines
2.4 KiB
MySQL
|
|
||
|
|
DROP VIEW user_syncs;
|
||
|
|
|
||
|
|
CREATE VIEW user_syncs AS
|
||
|
|
SELECT DISTINCT b.id AS recorded_track_id,
|
||
|
|
CAST(NULL as BIGINT) AS mix_id,
|
||
|
|
CAST(NULL as BIGINT) AS quick_mix_id,
|
||
|
|
CAST(NULL as BIGINT) AS recorded_backing_track_id,
|
||
|
|
b.id AS unified_id,
|
||
|
|
a.user_id AS user_id,
|
||
|
|
b.fully_uploaded,
|
||
|
|
recordings.created_at AS created_at,
|
||
|
|
recordings.id AS recording_id
|
||
|
|
FROM recorded_tracks a INNER JOIN recordings ON a.recording_id = recordings.id AND duration IS NOT NULL AND all_discarded = FALSE AND deleted = FALSE INNER JOIN recorded_tracks b ON a.recording_id = b.recording_id
|
||
|
|
UNION ALL
|
||
|
|
SELECT CAST(NULL AS BIGINT) AS recorded_track_id,
|
||
|
|
CAST(NULL as BIGINT) AS mix_id,
|
||
|
|
CAST(NULL as BIGINT) AS quick_mix_id,
|
||
|
|
a.id AS recorded_backing_track_id,
|
||
|
|
a.id AS unified_id,
|
||
|
|
a.user_id AS user_id,
|
||
|
|
a.fully_uploaded,
|
||
|
|
recordings.created_at AS created_at,
|
||
|
|
recordings.id AS recording_id
|
||
|
|
FROM recorded_backing_tracks a INNER JOIN recordings ON a.recording_id = recordings.id AND duration IS NOT NULL AND all_discarded = FALSE AND deleted = FALSE
|
||
|
|
UNION ALL
|
||
|
|
SELECT CAST(NULL as BIGINT) AS recorded_track_id,
|
||
|
|
mixes.id AS mix_id,
|
||
|
|
CAST(NULL as BIGINT) AS quick_mix_id,
|
||
|
|
CAST(NULL as BIGINT) AS recorded_backing_track_id,
|
||
|
|
mixes.id AS unified_id,
|
||
|
|
claimed_recordings.user_id AS user_id,
|
||
|
|
NULL as fully_uploaded,
|
||
|
|
recordings.created_at AS created_at,
|
||
|
|
recordings.id AS recording_id
|
||
|
|
FROM mixes INNER JOIN recordings ON mixes.recording_id = recordings.id INNER JOIN claimed_recordings ON recordings.id = claimed_recordings.recording_id WHERE claimed_recordings.discarded = FALSE AND deleted = FALSE
|
||
|
|
UNION ALL
|
||
|
|
SELECT CAST(NULL as BIGINT) AS recorded_track_id,
|
||
|
|
CAST(NULL as BIGINT) AS mix_id,
|
||
|
|
quick_mixes.id AS quick_mix_id,
|
||
|
|
CAST(NULL as BIGINT) AS recorded_backing_track_id,
|
||
|
|
quick_mixes.id AS unified_id,
|
||
|
|
quick_mixes.user_id,
|
||
|
|
quick_mixes.fully_uploaded,
|
||
|
|
recordings.created_at AS created_at,
|
||
|
|
recordings.id AS recording_id
|
||
|
|
FROM quick_mixes INNER JOIN recordings ON quick_mixes.recording_id = recordings.id AND duration IS NOT NULL AND all_discarded = FALSE AND deleted = FALSE;
|