-- @param {Int} $1:userId
-- @param {Int} $2:parentEntryId
-- @param {Int} $3:pageSize
-- @param {Int} $4:page
-- @param {String} $5:query
-- @param {String} $6:sort
WITH EntryWithFiles AS (
SELECT
e.id,
e.name,
e.created_at,
e.user_id,
e.parent_entry_id,
CASE
WHEN f.id IS NOT NULL THEN 'folder'
ELSE 'file'
END as "entryType",
COALESCE(files.size, null) as size,
COALESCE(files.status, null) as status,
COALESCE(files.type, null) as file_type,
COUNT(*) OVER() as total_count
FROM entry e
LEFT JOIN folder f ON f.id = e.id AND f.user_id = e.user_id
LEFT JOIN LATERAL (
SELECT f.size, f.status, f.type
FROM file f
WHERE f.id = e.id AND f.user_id = e.user_id
LIMIT 1
) files ON true
WHERE
e.user_id = $1
AND e.parent_entry_id = $2
AND CASE
WHEN $5 IS NOT NULL THEN
e.name ILIKE CONCAT('%',$5,'%')
ELSE true
END
)
SELECT *
FROM EntryWithFiles
ORDER BY
CASE WHEN $6 = 'newest' THEN created_at END DESC,
CASE WHEN $6 = 'oldest' THEN created_at END ASC,
-- For a-z, sort folders first, then by name
CASE WHEN $6 = 'a-z' THEN
CASE "entryType"
WHEN 'folder' THEN 0
ELSE 1
END
END ASC,
CASE WHEN $6 = 'a-z' THEN name END ASC,
-- For z-a, sort folders last, then by name
CASE WHEN $6 = 'z-a' THEN
CASE "entryType"
WHEN 'folder' THEN 1
ELSE 0
END
END ASC,
CASE WHEN $6 = 'z-a' THEN name END DESC,
CASE WHEN $6 = 'smallest' THEN size END ASC,
CASE WHEN $6 = 'largest' THEN size END DESC
LIMIT $3
OFFSET ($4 - 1) * $3;