media: get_videos

Custom SQL query returning 60 rows (hide)

SELECT
  v.uid,
  v.code,
  v.titleEn,
  v.titleJa,
  v.jacketFullUrl,
  v.jacketThumbUrl,
  v.runtimeMins,
  v.releaseDate,
  v.createdAt,

  se.uid  AS seriesUid,
  se.name AS series,

  json((
    SELECT json_group_array(
      json_object('uid', a.uid, 'name', a.name, 'image', a.image)
    )
    FROM video_star vs
    JOIN actresses a ON a.uid = vs.a_uid
    WHERE vs.v_uid = v.uid
  )) AS actresses,

  json((
    SELECT json_group_array(json_object('uid', m.uid, 'name', m.name))
    FROM video_maker vm
    JOIN makers m ON m.uid = vm.m_uid
    WHERE vm.v_uid = v.uid
  )) AS makers,

  json((
    SELECT json_group_array(json_object('uid', c.uid, 'name', c.name))
    FROM video_category vc
    JOIN categories c ON c.uid = vc.c_uid
    WHERE vc.v_uid = v.uid
  )) AS categories,

  (SELECT count(*) FROM streams s WHERE s.v_uid = v.uid) AS streamCount

FROM videos v
LEFT JOIN series se ON se.uid = v.s_uid

WHERE
  (
    :q = ''
    OR lower(v.code)    LIKE '%' || lower(:q) || '%'
    OR lower(v.titleEn) LIKE '%' || lower(:q) || '%'
    OR lower(v.titleJa) LIKE '%' || lower(:q) || '%'
  )

  AND (:series = '' OR v.s_uid = :series)

  -- Only videos that can actually be watched. Off by default: a
  -- catalogue entry with no link yet is still a real record, and
  -- hiding it by default would make every count disagree with the
  -- table it came from.
  AND (
    :has_stream = ''
    OR EXISTS (SELECT 1 FROM streams s WHERE s.v_uid = v.uid)
  )

  AND (
    :actress = ''
    OR EXISTS (
      SELECT 1 FROM video_star vs
      WHERE vs.v_uid = v.uid
        AND ',' || :actress || ',' LIKE '%,' || vs.a_uid || ',%'
    )
  )

  AND (
    :maker = ''
    OR EXISTS (
      SELECT 1 FROM video_maker vm
      WHERE vm.v_uid = v.uid
        AND ',' || :maker || ',' LIKE '%,' || vm.m_uid || ',%'
    )
  )

  AND (
    :label = ''
    OR EXISTS (
      SELECT 1 FROM video_label vl
      WHERE vl.v_uid = v.uid
        AND ',' || :label || ',' LIKE '%,' || vl.l_uid || ',%'
    )
  )

  AND (
    :director = ''
    OR EXISTS (
      SELECT 1 FROM video_director vd
      WHERE vd.v_uid = v.uid
        AND ',' || :director || ',' LIKE '%,' || vd.d_uid || ',%'
    )
  )

  AND (
    :category = ''
    OR EXISTS (
      SELECT 1 FROM video_category vc
      WHERE vc.v_uid = v.uid
        AND ',' || :category || ',' LIKE '%,' || vc.c_uid || ',%'
    )
  )

ORDER BY
  -- An exact code match comes first, then a prefix match. Somebody
  -- typing "MUM-135" wants that video, not the newest release that
  -- happens to mention it.
  CASE
    WHEN :q != '' AND lower(v.code) = lower(:q) THEN 0
    WHEN :q != '' AND lower(v.code) LIKE lower(:q) || '%' THEN 1
    ELSE 2
  END,

  -- NULLs last, always. A video with no release date must not sort
  -- above every dated one, which is what a bare DESC would do.
  CASE WHEN :sort = 'releaseDate' THEN (v.releaseDate IS NULL) END,
  CASE WHEN :sort = 'releaseDate' AND :order = 'asc'  THEN v.releaseDate END ASC,
  CASE WHEN :sort = 'releaseDate' AND :order = 'desc' THEN v.releaseDate END DESC,

  CASE WHEN :sort = 'code' AND :order = 'asc'  THEN v.code END ASC,
  CASE WHEN :sort = 'code' AND :order = 'desc' THEN v.code END DESC,

  CASE WHEN :sort = 'titleEn' AND :order = 'asc'  THEN v.titleEn END ASC,
  CASE WHEN :sort = 'titleEn' AND :order = 'desc' THEN v.titleEn END DESC,

  CASE WHEN :sort = 'runtimeMins' THEN (v.runtimeMins IS NULL) END,
  CASE WHEN :sort = 'runtimeMins' AND :order = 'asc'  THEN v.runtimeMins END ASC,
  CASE WHEN :sort = 'runtimeMins' AND :order = 'desc' THEN v.runtimeMins END DESC,

  CASE WHEN :sort = 'createdAt' AND :order = 'asc'  THEN v.createdAt END ASC,
  CASE WHEN :sort = 'createdAt' AND :order = 'desc' THEN v.createdAt END DESC,

  CASE WHEN :sort = 'streams' AND :order = 'asc'
    THEN (SELECT count(*) FROM streams s WHERE s.v_uid = v.uid) END ASC,
  CASE WHEN :sort = 'streams' AND :order = 'desc'
    THEN (SELECT count(*) FROM streams s WHERE s.v_uid = v.uid) END DESC,

  (v.releaseDate IS NULL), v.releaseDate DESC, v.uid

LIMIT  CAST(COALESCE(NULLIF(:limit,  ''), 60) AS INTEGER)
OFFSET CAST(COALESCE(NULLIF(:offset, ''), 0)  AS INTEGER)

Query parameters

q

series

has_stream

actress

maker

label

director

category

sort

order

limit

offset