video-playlist.sql

CREATE OR REPLACE VIEW view_playlists_videos AS
  (SELECT
     videos.id,
     videos.titulo,
     videos.slug,
     videos.duracao_segundos,
     videos.imagem_destacada,
     videos.hits,
     videos.content_type_id,
     videos.published_at,
     videos.created_at,
     'video' COLLATE utf8_unicode_ci AS type
   FROM `DB`.videos
   WHERE videos.deleted_at IS NULL
         AND videos.id NOT IN (
     SELECT featuredables.featuredable_id
     FROM videos
       INNER JOIN featuredables ON (videos.id = featuredables.featuredable_id)
       INNER JOIN featured ON featuredables.featured_id = featured.id
     WHERE featuredables.featuredable_type = "App\\Entities\\Videos"
           AND featured.local = 'destaques_page_videos'
   )
         AND videos.published_at <= NOW() COLLATE utf8_unicode_ci)
  UNION
  (SELECT
     playlists.id AS playlist_id,
     playlists.titulo,
     playlists.slug,
     NULL,
     (SELECT videos.imagem_destacada
      FROM playlists
        INNER JOIN playlists_videos ON (playlists.id = playlists_videos.playlists_id)
        INNER JOIN videos ON (playlists_videos.videos_id = videos.id)
      LIMIT 1)    AS imagem_destacada,
     NULL,
     playlists.content_type_id,
     playlists.published_at,
     playlists.created_at,
     'playlist'   AS type
   FROM `DB`.playlists
   WHERE playlists.deleted_at IS NULL
         AND playlists.published_at <= NOW() COLLATE utf8_unicode_ci);