There is such a database:
And such a stored function:
CREATE FUNCTION fetch_mentor(direction_type_list_in text, education_type_list_in text, name_in text, city_id_in integer) RETURNS json
LANGUAGE plpgsql
AS
$$
DECLARE
direction_type_list_inner INT[];
education_type_list_inner INT[];
BEGIN
direction_type_list_inner = STRING_TO_ARRAY(direction_type_list_in, ',');
education_type_list_inner = STRING_TO_ARRAY(education_type_list_in, ',');
RETURN (SELECT JSON_AGG(rows)
FROM (SELECT m.id AS "ID"
, FORMAT('%s %s', m.firstname, m.lastname) AS "fullName"
, m.photo_url AS "photoURL"
, m.video_url AS "videoURL"
, (SELECT JSON_AGG(mc.name)
FROM mentors.mentor_competence mc
WHERE mc.mentor_id = m.id) AS "competenceList"
, (SELECT JSON_AGG(el.name)
FROM mentors.mentor_employment me
INNER JOIN lists.employment_list el ON el.id = me.employment_id
WHERE me.mentor_id = m.id) AS "competenceList"
, m.certified AS "certified"
, cl.display_name AS "cityName"
, (SELECT JSON_AGG(dtl.display_name)
FROM mentors.mentor_direction_type mdt
INNER JOIN lists.direction_type_list dtl ON mdt.direction_type_id = dtl.id
WHERE mdt.mentor_id = m.id) "directionList"
, (SELECT JSON_AGG(etl.display_name)
FROM mentors.mentor_education_type met
INNER JOIN lists.education_type_list etl ON met.education_type_id = etl.id
WHERE met.mentor_id = m.id) "educationList"
FROM mentors.mentor m
LEFT JOIN lists.city_list cl ON m.city_id = cl.id
WHERE approved = TRUE
AND (direction_type_list_in ISNULL OR
m.id IN (SELECT m.id
FROM mentors.mentor_direction_type mdt
WHERE mdt.direction_type_id = ANY (direction_type_list_inner)))
AND (education_type_list_inner ISNULL OR
m.id IN (SELECT m.id
FROM mentors.mentor_education_type met
WHERE met.education_type_id = ANY (education_type_list_inner)))
AND (name_in ISNULL OR
(m.firstname LIKE FORMAT('%%%s%%', $3) OR m.lastname LIKE FORMAT('%%%s%%', $3)))
AND (city_id_in ISNULL OR m.city_id = city_id_in)
) rows);
END;
$$;
ALTER FUNCTION fetch_mentor(TEXT, TEXT, TEXT, INTEGER) OWNER TO postgres;
Which returns such a json:
[
{
"ID": 3,
"fullName": "fsafd 413",
"photoURL": "sadf",
"videoURL": "dsa",
"competenceList": [
"a",
"s",
"f"
],
"employmentList": [
"a",
"b",
"c"
],
"certified": false,
"cityName": null,
"directionList": [
"x",
"z"
],
"educationList": [
"offline"
]
}
]
The following parameters come to the input:
type FetchMentorsParams struct {
DirectionTypeList []int `json:"directionTypeList"`
EducationTypeList []int `json:"educationTypeList"`
MentorName *string `json:"mentorName"`
}
All filters are optional. That is, if there are no filters, then all records are output. If, for example, Direction Type List = [1,2], then only those records should be returned where mentor_id in the table mentor_direction_type is in the column where direction_type_id = 1 or 2. And so on.
But is there a way to form a query without a stored function that is resistant to SQL Injection? If you do something like len(direction Type List) != 0 and then add subqueries in the for loop, then the query will be vulnerable to sql injection.
And in general, how good is it to try to do everything in one query? I see an option: before the main query, make query to mentor_direction_type and to mentor_education_type and then do where mentor_id in (the result of querys).
And it is normal to make such querys as - (SELECT JSON_AGG(el.name) FROM mentors.mentor_employment me INNER JOIN lists.employment_list el ON el.id = me.employment_id WHERE me.mentor_id = m.id) AS "competenceList", I'm not sure if it's optimized, but I don't see any other option.
Im using Go, SQLX, PGX.
