event_attribute.sql

WITH
  event_event_attribute AS (
    SELECT
      cantor_tuple(ARRAY[eea1.id, 0, 0, 0]) id,
      eea1.event_id,
      eea1.event_attribute_id,
      ea1.nid attribute_nid,
      'event_event_attribute' source
    FROM event_event_attribute eea1
    INNER JOIN event_attribute ea1 ON ea1.id = eea1.event_attribute_id
    UNION ALL
    SELECT
      cantor_tuple(ARRAY[0, efcea1.id, 0, 0]) id,
      efcea1.event_id,
      eafcea1.event_attribute_id,
      ea1.nid attribute_nid,
      'event_foreign_cinema_event_attribute' source
    FROM event_foreign_cinema_event_attribute efcea1
    INNER JOIN foreign_cinema_event_attribute fcea1 ON fcea1.id = efcea1.foreign_cinema_event_attribute_id
    INNER JOIN event_attribute_foreign_cinema_event_attribute eafcea1 ON eafcea1.foreign_cinema_event_attribute_id = fcea1.id
    INNER JOIN event_attribute ea1 ON ea1.id = eafcea1.event_attribute_id
    UNION ALL
    SELECT
      cantor_tuple(ARRAY[0, 0, cmnea1.id, 0]) id,
      e1.id event_id,
      cmnea1.event_attribute_id,
      ea1.nid attribute_nid,
      'cinema_movie_name_event_attribute' source
    FROM event e1
    INNER JOIN cinema_movie_name_event_attribute cmnea1 ON cmnea1.cinema_movie_name_id = e1.cinema_movie_name_id
    INNER JOIN event_attribute ea1 ON ea1.id = cmnea1.event_attribute_id
  ),
  -- Identify events that do not have 2D or 3D attribute.
  -- Use these events to assign 2D attribute.
  append_attribute_event AS
  (
    SELECT event_id
    FROM event_event_attribute
    GROUP BY event_id
    HAVING
      MAX(CASE attribute_nid WHEN '2D' THEN 1 ELSE 0 END) = 0 AND
      MAX(CASE attribute_nid WHEN '3D' THEN 1 ELSE 0 END) = 0
  )
SELECT DISTINCT ON (event_id, event_attribute_id)
  id,
  event_id,
  event_attribute_id,
  source
FROM
  (
    SELECT
      id,
      event_id,
      event_attribute_id,
      source
    FROM event_event_attribute
    UNION ALL
    SELECT
      cantor_tuple(ARRAY[0, 0, 0, 1]) id,
      event_id,
      (SELECT id FROM event_attribute WHERE nid = '2D') event_attribute_id,
      'implied' "source"
    FROM append_attribute_event
  ) AS event_event_attribute
ORDER BY
  event_id ASC,
  event_attribute_id ASC,
  id ASC