media: get_actresses

Custom SQL query returning 60 rows (hide)

SELECT
  a.uid,
  a.name,
  a.nameJa,
  a.image,
  json(a.alias) AS alias,
  (SELECT count(*) FROM video_star vs WHERE vs.a_uid = a.uid) AS videoCount
FROM actresses a
WHERE :q = '' OR lower(a.name) LIKE '%' || lower(:q) || '%'
   OR lower(coalesce(a.nameJa, '')) LIKE '%' || lower(:q) || '%'
   OR lower(a.alias) LIKE '%' || lower(:q) || '%'
ORDER BY
  CASE WHEN :sort = 'name'   AND :order = 'asc'  THEN a.name END ASC,
  CASE WHEN :sort = 'name'   AND :order = 'desc' THEN a.name END DESC,
  CASE WHEN :sort = 'videos' AND :order = 'asc'
    THEN (SELECT count(*) FROM video_star vs WHERE vs.a_uid = a.uid) END ASC,
  CASE WHEN :sort = 'videos' AND :order = 'desc'
    THEN (SELECT count(*) FROM video_star vs WHERE vs.a_uid = a.uid) END DESC,
  CASE WHEN :sort = 'createdAt' AND :order = 'asc'  THEN a.createdAt END ASC,
  CASE WHEN :sort = 'createdAt' AND :order = 'desc' THEN a.createdAt END DESC,
  a.name
LIMIT  CAST(COALESCE(NULLIF(:limit,  ''), 60) AS INTEGER)
OFFSET CAST(COALESCE(NULLIF(:offset, ''), 0)  AS INTEGER)

Query parameters

q

sort

order

limit

offset