Created
October 5, 2023 17:26
-
-
Save danizavtz/6a810862ab45f3fff0575daacf156a5f to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE IF NOT EXISTS media( | |
id SERIAL PRIMARY KEY, | |
label VARCHAR(256), | |
type media_type, | |
"companyCode" VARCHAR(100), | |
"companyId" INT | |
); | |
CREATE TABLE IF NOT EXISTS media_file( | |
id SERIAL PRIMARY KEY, | |
height smallint CHECK (height >= 0), | |
width smallint CHECK (width >= 0), | |
"exibitionTime" smallint DEFAULT 5000, | |
sequence smallint CHECK(sequence >= 0), | |
img BYTEA, | |
mimetype VARCHAR(15), | |
media INT, | |
FOREIGN KEY (media) REFERENCES media(id) ON DELETE CASCADE | |
); | |
--a query está aqui | |
SELECT m."id", m."label", m."type", m."companyCode", m."companyId", | |
COALESCE(( | |
SELECT ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(d))) | |
FROM ( | |
SELECT md."id", md."label", md."type", | |
COALESCE(( | |
SELECT ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(f))) | |
FROM ( | |
SELECT mf."id",mf."height", mf."width", mf."sequence", mf."exibitionTime", mf.mimetype, ENCODE(mf.img,'base64') as img | |
FROM media_file mf | |
WHERE mf.media = md.id | |
) f | |
),'[]') AS files | |
FROM media md | |
WHERE md.id=m.id | |
) d | |
),'[]') AS media | |
FROM media m WHERE m."companyId"=$1 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment