Ok so this is the partial query, working 100% as we speak. There's a comment as you can see where the query of the 4 columns is supposed to go.
SELECT DISTINCT
p.id AS "Project ID",
p.title AS "Project Title",
p.summary AS "Project Summary",
to_char(p.expected_start_date, 'YYYY-MM-dd') AS "Expected Start Date",
to_char(p.expected_end_date, 'YYYY-MM-dd') AS "Expected End Date",
to_char(p.actual_start_date, 'YYYY-MM-dd') AS "Actual Start Date",
to_char(p.actual_end_date, 'YYYY-MM-dd') AS "Actual End Date",
d.name AS "Center/Department",
/* THIS IS WHERE THE Query has to go.*/
SELECT DISTINCT string_agg(distinct gd.name ||' ('||gs.name||')', ' | ') AS "GRANT NAME AND STATUS"
from grant_detail gd
JOIN grant_status gs on gd.status_id = gs.id
JOIN project_budget pb ON gd.id = pb.grant_id
WHERE pb.project_id = p.id
group by p.id),
SELECT DISTINCT string_agg(distinct o.name, ', ') AS "FUNDER"
FROM organization o
JOIN organization_type ot ON ot.id = o.type_id
JOIN grant_detail gt ON gt.organization_id = o.id
JOIN project_budget pb ON pb.grant_id = gt.id
WHERE pb.project_id = p.id
AND ot.name = 'Funder'),
SELECT string_agg(pc.name, ', ') AS "Categories"
FROM project_project_categories ppc
JOIN project_category pc ON ppc.project_category_id = pc.id
WHERE ppc.project_id = p.id),
( /*========= Student Researcher Full Name ( email) =========*/
SELECT DISTINCT string_agg(distinct s.first_name || ' ' ||
s.last_name ||' ('||
s.email ||')', ', ')
AS "Student Researcher"
FROM project_stakeholder ps
JOIN stakeholder s ON s.id = ps.stakeholder_id
JOIN project_role pr ON ps.role_id = pr.id
WHERE pr.name = 'Student Researcher'
AND ps.project_id = p.id
GROUP BY p.id)
FROM
/*CONTEXT*/
project p
LEFT JOIN innovation_category c2 ON p.innovation_category_id = c2.id
LEFT JOIN department d ON p.department_id = d.id
JOIN project_health ph ON p.health_id = ph.id
JOIN project_status ps ON p.status_id = ps.id
-- WHERE
-- -- p.actual_start_date <= '__beforeActualStartDate'
-- -- AND p.actual_end_date >= '__afterActualEndDate'
-- -- AND p.expected_start_date <= '__beforeStartDate'
-- -- AND p.expected_end_date >= '__afterEndDate'
ORDER BY
p.title
UPDATE: For Phelipe: You can see where I've commented my code in the place where the four columns need to be que
CodePudding user response：
Here is a fully dynamic solution that can work with a variable list of roles.
This solution relies on the creation of a composite type role_type which includes the list of roles and which can be called at the runtime :
CREATE OR REPLACE PROCEDURE role_type () LANGUAGE plpgsql AS
$$
DECLARE
role_list text ;
BEGIN
SELECT string_agg(r_name || ' text', ',' ORDER BY r_id)
INTO role_list
FROM role ;
EXECUTE 'DROP TYPE IF EXISTS role_type' ;
EXECUTE 'CREATE TYPE role_type AS (' || role_list || ')' ;
END ;
$$
After calling the procedure role_type(), the result is provided by a simple query using the jsonb_object_agg and jsonb_populate_record standard functions :
CALL role_type () ;
SELECT a.p_id AS "Project id"
, a.p_title AS "Project Title"
, (jsonb_populate_record(NULL :: role_type, jsonb_object_agg(lower(a.r_name), (a.usr_list)))).*
FROM
( SELECT p.p_id
, p.p_title
, r.r_name
, string_agg(u.fname || ' ' || u.lname, ', ') AS usr_list
FROM project_usr AS pu
INNER JOIN project AS p
ON p.p_id = pu.project_id
INNER JOIN usr AS u
ON u.usr_id = pu.usr_id
INNER JOIN role AS r
ON r.r_id = pu.role_id
GROUP BY p.p_id, p.p_title, r.r_name
) AS a
GROUP BY a.p_id, a.p_title
ORDER BY a.p_id
Finally, instead of calling the role_type() procedure each time before executing the query, it can be called by trigger when the role list is changing :
CREATE OR REPLACE FUNCTION role_after_insert_update ()
RETURNS trigger LANGUAGE plpgsql AS
$$
BEGIN
CALL role_type () ;
RETURN NEW ;
END ;
$$ ;
CREATE OR REPLACE TRIGGER role_after_insert_update AFTER INSERT OR UPDATE ON role
FOR EACH ROW EXECUTE FUNCTION role_after_insert_update() ;
CREATE OR REPLACE FUNCTION role_after_delete ()
RETURNS trigger LANGUAGE plpgsql AS
$$
BEGIN
CALL role_type () ;
RETURN OLD ;
END ;
$$ ;
CREATE OR REPLACE TRIGGER role_after_delete AFTER DELETE ON role
FOR EACH ROW EXECUTE FUNCTION role_after_delete() ;
see the full test result in dbfiddle
CodePudding user response：
you can do this but it supposes that role table is always the same with same id
select pu.project_id,
case r.r_id when 1 then u.fname||' '||u.lname else '' end as Teacher,
case r.r_id when 2 then u.fname||' '||u.lname else '' end as Student,
case r.r_id when 3 then u.fname||' '||u.lname else '' end as Volunteer
from project_usr pu join role r on r.r_id = pu.role_id
join usr u on pu.usr_id = u.usr_id
Crosstab
You could use crosstab too but the problem will be the same, you have to know the roles
select * from crosstab($$
select pu.project_id,r.r_name, u.fname||' '||u.lname
from project_usr pu join role r on r.r_id = pu.role_id
join usr u on pu.usr_id = u.usr_id
$$,'select r_name from role')
as final_result (project_id integer, Teacher varchar,Student varchar,Volunteer varchar)
Maybe something like this (not tested, not enough informations)
with r as (
select * from crosstab($$
select pu.project_id,r.r_name, u.fname||' '||u.lname
from project_usr pu join role r on r.r_id = pu.role_id
join usr u on pu.usr_id = u.usr_id
$$,'select r_name from role')
as final_result (project_id integer, Teacher varchar,Student varchar,Volunteer varchar)
)
SELECT DISTINCT
p.id AS "Project ID",
p.title AS "Project Title",
p.summary AS "Project Summary",
to_char(p.expected_start_date, 'YYYY-MM-dd') AS "Expected Start Date",
to_char(p.expected_end_date, 'YYYY-MM-dd') AS "Expected End Date",
to_char(p.actual_start_date, 'YYYY-MM-dd') AS "Actual Start Date",
to_char(p.actual_end_date, 'YYYY-MM-dd') AS "Actual End Date",
d.name AS "Center/Department",
/* THIS IS WHERE THE Query has to go.*/
r.teacher,
r.student,
r.volunteer,
SELECT DISTINCT string_agg(distinct gd.name ||' ('||gs.name||')', ' | ') AS "GRANT NAME AND STATUS"
from grant_detail gd
JOIN grant_status gs on gd.status_id = gs.id
JOIN project_budget pb ON gd.id = pb.grant_id
WHERE pb.project_id = p.id
group by p.id),
SELECT DISTINCT string_agg(distinct o.name, ', ') AS "FUNDER"
FROM organization o
JOIN organization_type ot ON ot.id = o.type_id
JOIN grant_detail gt ON gt.organization_id = o.id
JOIN project_budget pb ON pb.grant_id = gt.id
WHERE pb.project_id = p.id
AND ot.name = 'Funder'),
SELECT string_agg(pc.name, ', ') AS "Categories"
FROM project_project_categories ppc
JOIN project_category pc ON ppc.project_category_id = pc.id
WHERE ppc.project_id = p.id),
( /*========= Student Researcher Full Name ( email) =========*/
SELECT DISTINCT string_agg(distinct s.first_name || ' ' ||
s.last_name ||' ('||
s.email ||')', ', ')
AS "Student Researcher"
FROM project_stakeholder ps
JOIN stakeholder s ON s.id = ps.stakeholder_id
JOIN project_role pr ON ps.role_id = pr.id
WHERE pr.name = 'Student Researcher'
AND ps.project_id = p.id
GROUP BY p.id)
FROM
/*CONTEXT*/
project p
LEFT JOIN innovation_category c2 ON p.innovation_category_id = c2.id
LEFT JOIN department d ON p.department_id = d.id
JOIN project_health ph ON p.health_id = ph.id
JOIN project_status ps ON p.status_id = ps.id
/* don't know the key */ join r on ...
ORDER BY
p.title