SQL report for QA feedback on **locations**

[lisabettefay]

to mirror queries we have on reports that do this.

ex. for queries for reports:
https://vial.calltheshots.us/dashboard/vol-wb-feedback-reporter-lookup/
https://vial.calltheshots.us/dashboard/general-wb-qa-feedback/

Here is a query that is returning no records but is generally what would be needed:

--This query is meant to mirror the QA feedback query that we use to allow volunteers easy access to the feedback on their reports.

SELECT date(location.created_at AT TIME ZONE 'America/Los_Angeles') as Date, location.public_id, 'https://vial.calltheshots.us/admin/core/location/' || location.id as "location link", string_agg(core_locationreviewtag.tag, ', ') as tags, core_locationreviewnote.note

FROM location

JOIN reporter 
  ON location.created_by_id = reporter.id	

LEFT JOIN core_locationreviewnote 
  ON core_locationreviewnote.location_id = location.id	

LEFT JOIN core_locationreviewnote_tags 
  ON core_locationreviewnote_tags.locationreviewnote_id = core_locationreviewnote.id	

LEFT JOIN core_locationreviewtag 
  ON core_locationreviewnote_tags.locationreviewtag_id = core_locationreviewtag.id AND core_locationreviewtag.tag != 'Approved'

WHERE reporter.auth0_role_names LIKE '%%VIAL WB limited%%' AND reporter.auth0_role_names NOT LIKE '%%CC1%%' AND core_locationreviewtag.id is not null

GROUP BY date, reporter.id, location.id, core_locationreviewnote.note

ORDER BY location.created_at desc, reporter.id

[lisabettefay]

okay. adrian helped push me a little further along, but now i am getting a new error. here is the new almost working code:

--This query is meant to mirror the QA feedback 
--query that we use to allow volunteers easy access
--to the feedback on their reports.

SELECT date(location.created_at AT TIME ZONE 'America/Los_Angeles') as Date, location.created_by_id, reporter.name, location.public_id, 'https://vial.calltheshots.us/admin/core/location/' || location.id as "location link", 
string_agg(core_locationreviewtag.tag, ', ') as tags, core_locationreviewnote.note, 
CASE WHEN core_reportreviewnote.author_id = 25 THEN 'Eve' 
WHEN core_reportreviewnote.author_id = 33 THEN 'Lisabette' ELSE '????' END

FROM location

JOIN reporter 
  ON location.created_by_id = reporter.user_id

LEFT JOIN core_locationreviewnote 
  ON core_locationreviewnote.location_id = location.id

LEFT JOIN core_locationreviewnote_tags 
  ON core_locationreviewnote_tags.locationreviewnote_id = core_locationreviewnote.id

LEFT JOIN core_locationreviewtag 
  ON core_locationreviewnote_tags.locationreviewtag_id = core_locationreviewtag.id AND core_locationreviewtag.tag != 'Approved'

WHERE core_locationreviewtag.id is not null

GROUP BY date, reporter.name, reporter.user_id, location.id, core_locationreviewnote.note, core_reportreviewnote.author_id, created_by_id

ORDER BY location.created_at desc, reporter.user_id

and here is the error: