user1575921 user1575921 - 6 months ago 8
SQL Question

with if/else column value, select different table and column

objective

I have table like below,
I want to select

comment
tbl,
but only return
comment
rows check related table
status
column all equal input param.

problem

In query 1, comment out I highlight, and it works otherwise return zero row.
or
query 2 get error syntax

How to solve this?

table

comment
id | endpoint_code | endpoint_id | status | create_by_user_id
1 | 1 | 56 | 0 | 1
2 | 0 | 27 | 0 | 1

description:
endpoint_code 0:gallery tbl 1:media tbl
use `endpoint_code` value if `0` means `endpoint_id` is point to `gallery` tbl,
if `1` means point to `media` tbl.


user
id | status
1 | 0

gallery
id | status | ...
56 | 0

media
id | status | ...
27 | 0

category_gallery
id | category_id | gallery_id | ...
...

gallery_media
id | gallery_id | media_id | ...
...


query 1

SELECT c.* ,
row_to_json(m.*) as media,
row_to_json(mg.*) as media_gallery,
row_to_json(g.*) as gallery
FROM comment c
LEFT JOIN "user" u ON u.id = c.create_by_user_id

LEFT JOIN media m ON m.id = c.endpoint_id
AND c.endpoint_code = 1
LEFT JOIN "user" mcbu ON mcbu.id = m.create_by_user_id
AND c.endpoint_code = 1
LEFT JOIN gallery_media gm ON gm.media_id = c.endpoint_id
AND c.endpoint_code = 1
LEFT JOIN gallery mg ON mg.id = gm.gallery_id
AND c.endpoint_code = 1

LEFT JOIN gallery g ON g.id = c.endpoint_id
AND c.endpoint_code = 0
LEFT JOIN "user" gcbu ON gcbu.id = g.create_by_user_id
AND c.endpoint_code = 0
LEFT JOIN category_gallery cg ON cg.gallery_id = g.id
AND c.endpoint_code = 0
LEFT JOIN category ca ON ca.id = cg.category_id
AND c.endpoint_code = 0

WHERE c.status = $1
AND u.status = $1

// comment out these for c.endpoint_code 0 (gallery)
AND
CASE WHEN c.endpoint_code = 1
THEN
m.status = $1
END
AND
CASE WHEN c.endpoint_code = 1
THEN
mcbu.status = $1
END
AND
CASE WHEN c.endpoint_code = 1
THEN
mg.status = $1
END
//


// or comment out these for c.endpoint_code 1 (media)
AND
CASE WHEN c.endpoint_code = 0
THEN
g.status = $1
END
AND
CASE WHEN c.endpoint_code = 0
THEN
gcbu.status = $1
END
AND
CASE WHEN c.endpoint_code = 0
THEN
ca.status = $1
END
//

AND c.id = $2


query 2

error

SELECT c.* ,
row_to_json(m.*) as media,
row_to_json(mg.*) as media_gallery,
row_to_json(g.*) as gallery
FROM comment c
LEFT JOIN "user" u ON u.id = c.create_by_user_id

LEFT JOIN media m ON m.id = c.endpoint_id
AND c.endpoint_code = 1
LEFT JOIN "user" mcbu ON mcbu.id = m.create_by_user_id
AND c.endpoint_code = 1
LEFT JOIN gallery_media gm ON gm.media_id = c.endpoint_id
AND c.endpoint_code = 1
LEFT JOIN gallery mg ON mg.id = gm.gallery_id
AND c.endpoint_code = 1

LEFT JOIN gallery g ON g.id = c.endpoint_id
AND c.endpoint_code = 0
LEFT JOIN "user" gcbu ON gcbu.id = g.create_by_user_id
AND c.endpoint_code = 0
LEFT JOIN category_gallery cg ON cg.gallery_id = g.id
AND c.endpoint_code = 0
LEFT JOIN category ca ON ca.id = cg.category_id
AND c.endpoint_code = 0

WHERE c.status = $1
AND u.status = $1

CASE WHEN c.endpoint_code = 1
THEN
AND
m.status = $1
AND
mcbu.status = $1
AND
mg.status = $1
END


CASE WHEN c.endpoint_code = 0
THEN
AND
g.status = $1
AND
gcbu.status = $1
AND
ca.status = $1
END

AND c.id = $2


I doing this in javascript generate query string from a function

input
status
params is option like below, I hope to find a way solve above problem,
prefer not restruct the code too much will be best

query 2 for example

var query = `

SELECT c.* ,
row_to_json(m.*) as media,
row_to_json(mg.*) as media_gallery,
row_to_json(g.*) as gallery
FROM comment c
LEFT JOIN "user" u ON u.id = c.create_by_user_id


LEFT JOIN media m ON m.id = c.endpoint_id
AND c.endpoint_code = 1
`;

if (inputOption.status != 'undefined') {
query += `
LEFT JOIN "user" mcbu ON mcbu.id = m.create_by_user_id
AND c.endpoint_code = 1
LEFT JOIN gallery_media gm ON gm.media_id = c.endpoint_id
AND c.endpoint_code = 1
`;
}
query += `
LEFT JOIN gallery mg ON mg.id = gm.gallery_id
AND c.endpoint_code = 1
`;

query += `
LEFT JOIN gallery g ON g.id = c.endpoint_id
AND c.endpoint_code = 0
`;
if (inputOption.status != 'undefined') {
query += `
LEFT JOIN "user" gcbu ON gcbu.id = g.create_by_user_id
AND c.endpoint_code = 0
LEFT JOIN category_gallery cg ON cg.gallery_id = g.id
AND c.endpoint_code = 0
LEFT JOIN category ca ON ca.id = cg.category_id
AND c.endpoint_code = 0
`;
}

if (inputOption.status != 'undefined') {
query += `
WHERE c.status = $1
AND u.status = $1

CASE WHEN c.endpoint_code = 1
THEN
AND
m.status = $1
AND
mcbu.status = $1
AND
mg.status = $1
END


CASE WHEN c.endpoint_code = 0
THEN
AND
g.status = $1
AND
gcbu.status = $1
AND
ca.status = $1
END
`;
}
query += `
AND c.id = $2
`;


Edit: further questions



after I do select

SELECT c.* ,
row_to_json(mg.*) as media_gallery,
row_to_json(m.*) as media,
row_to_json(g.*) as gallery
FROM comment c

LEFT JOIN media m ON m.id = c.endpoint_id

.... other query
query += `
WHERE c.status = $1
AND u.status = $1
AND
(
(c.endpoint_code = 1
AND m.status = $1
AND mcbu.status = $1
AND mg.status = $1
)
OR
....


the json result because
endpoint_code = 0
so in json key
media_gallery
and
media
( from above query part
row_to_json(mg.*) as media_gallery, ...
) value will be null.

or
endpoint_code = 1
key
gallery
value is null.

How to filter/remove if
endpoint_code = 0
then don't output
media_gallery
media
, means don't do
row_to_json(mg.*) as media_gallery, ...
in query? or other way ?

rows:
[ { id: 7,
endpoint_code: 0,
endpoint_id: 27,
status: 0,

media_gallery: null,
media: null,
gallery:
{ id: 27,
status: 0,
create_date:
...


try to get result like

rows:
[ { id: 7,
endpoint_code: 0,
endpoint_id: 27,
status: 0,

gallery:
{ id: 27,
status: 0,
create_date:
...


or

rows:
[ { id: 7,
endpoint_code: 1,
endpoint_id: 27,
status: 0,

media_gallery: {
id: ....
},
media:
{ id: 27,
status: 0,
create_date:
...

Answer

CASE doesn't work as a template, but rather as a function. Update your javascript as follows:

if (inputOption.status != 'undefined') {
query += `
    WHERE c.status = $1 
      AND u.status = $1 

      AND CASE WHEN c.endpoint_code = 1 -- this will return a boolean
      THEN m.status = $1
       AND mcbu.status = $1
       AND mg.status = $1
      END   

      AND CASE WHEN c.endpoint_code = 0 -- this too
      THEN g.status = $1
       AND gcbu.status = $1
       AND ca.status = $1
      END
`;

The above works, but it doesn't allow PostgreSQL to optimise the query. It is much better if you use AND and OR, as @jarlh states in the comments:

if (inputOption.status != 'undefined') {
query += `
    WHERE c.status = $1 
      AND u.status = $1 
      AND
      (
        (c.endpoint_code = 1
         AND m.status = $1
         AND mcbu.status = $1
         AND mg.status = $1
        )
        OR
        (c.endpoint_code = 0
         AND g.status = $1
         AND gcbu.status = $1
         AND ca.status = $1)
      )
`;