media: get_video

Custom SQL query (hide) 

SELECT
  v.uid,
  v.code,
  v.titleEn,
  v.titleJa,
  v.descriptionEn,
  v.descriptionJa,
  v.jacketFullUrl,
  v.jacketThumbUrl,
  v.sampleUrl,
  v.runtimeMins,
  v.releaseDate,
  v.createdAt,
  v.updatedAt,

  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', l.uid, 'name', l.name))
    FROM video_label vl
    JOIN labels l ON l.uid = vl.l_uid
    WHERE vl.v_uid = v.uid
  )) AS labels,

  json((
    SELECT json_group_array(json_object('uid', d.uid, 'name', d.name))
    FROM video_director vd
    JOIN directors d ON d.uid = vd.d_uid
    WHERE vd.v_uid = v.uid
  )) AS directors,

  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,

  -- The ORDER BY has to be INSIDE the subquery. A trailing one would
  -- order the aggregate's single output row rather than the stills
  -- being aggregated, and the gallery would come back shuffled.
  json((
    SELECT json_group_array(u) FROM (
      SELECT g.fullUrl AS u FROM video_gallery g
      WHERE g.v_uid = v.uid AND g.fullUrl IS NOT NULL
      ORDER BY g.position
    )
  )) AS galleryFull,

  json((
    SELECT json_group_array(u) FROM (
      SELECT g.thumbUrl AS u FROM video_gallery g
      WHERE g.v_uid = v.uid AND g.thumbUrl IS NOT NULL
      ORDER BY g.position
    )
  )) AS galleryThumb,

  json((
    SELECT json_group_array(
      json_object(
        'id', s.id,
        'source', s.source,
        'sourceUrl', s.sourceUrl,
        'embedUrl', s.embedUrl,
        'proxyUrl', s.proxyUrl,
        'directLink', s.directLink,
        'kind', s.kind
      )
    )
    FROM streams s WHERE s.v_uid = v.uid
  )) AS streams,

  (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 (:uid  != '' AND v.uid  = :uid)
   OR (:code != '' AND v.code = upper(:code))

Query parameters

0 results