gistfile1.sql

    WITH RECURSIVE lectures AS (
      SELECT lessons.id, title, ancestry, section_id, lessons.position FROM lessons
      JOIN sections ON sections.id = section_id AND sections.deleted_at IS NULL
      WHERE lessons.course_id = ? AND lessons.deleted_at IS NULL
      ORDER BY ancestry ASC NULLS FIRST, sections.position ASC, sections.created_at ASC, lessons.position ASC
    ), 
    
    nodes AS (
      SELECT ARRAY[section_id] AS path, * FROM lectures
      WHERE ancestry IS NULL
      UNION
      SELECT n.path || l.section_id, l.*
      FROM nodes n
      JOIN lectures l ON l.ancestry = array_to_string(n.path, '/')
    ), 
    
    pagination AS (
      SELECT
        id,
        lag(id) OVER sequence AS previous_id,
        lag(title) OVER sequence AS previous_title,
        lead(id) OVER sequence AS next_id,
        lead(title) OVER sequence AS next_title
      FROM nodes
      WINDOW sequence AS (ORDER BY path, position)
    )
    
    SELECT * FROM pagination WHERE id = ?