When accessing the Carto database for: counting results, downloading CSV results, and creating Assignments; the counted/downloaded/selected results now ignore empty cameras.
Status
Ready for review
Details
The cause of the problem is in the use of LEFT JOIN instead of INNER JOIN when we're, e.g. downloading results in a CSV file.
Let's say we have a setup like this:
CAMERA | dist_water
-------------------
A | 10
B | 30
SUBJECT | count | camera
------------------------
Lion | 5 | A
Baboon | 13 | A
When we use CAMERA LEFT JOIN SUBJECT we'll get
SUBJECT | count | CAMERA | dist_water
-------------------------------------
Lion | 5 | A | 10
Baboon | 13 | A | 10
null | null | B | 30
There's an invalid row at the end of results. (i.e. the empty row won't make sense when creating Assignments or when creating a CSV results file.) We need to use CAMERA INNER JOIN SUBJECT to get the desired
SUBJECT | count | CAMERA | dist_water
-------------------------------------
Lion | 5 | A | 10
Baboon | 13 | A | 10
This change affects the following SQL Queries in mapexplorer.config.json:
sqlQueryCountItemsCountOnly: used when setting "Viewing X results" in the Map Explorer.
sqlQueryDownload: used when calling the "Download" (as CSV) functionality of the SuperDownloadButton.
sqlQuerySubjectIDs: used when calling the "Select for Assignment" functionality.
NOTE!
The following SQL Query still uses LEFT JOIN since we do want the empty cameras:
sqlQueryCountItems: used to paint the cameras in the Map Explorer, used in MapVisuals.
PR Overview
Fixes #305
When accessing the Carto database for: counting results, downloading CSV results, and creating Assignments; the counted/downloaded/selected results now ignore empty cameras.
Status
Ready for review
Details
The cause of the problem is in the use of
LEFT JOINinstead ofINNER JOINwhen we're, e.g. downloading results in a CSV file.Let's say we have a setup like this:
When we use
CAMERA LEFT JOIN SUBJECTwe'll getThere's an invalid row at the end of results. (i.e. the empty row won't make sense when creating Assignments or when creating a CSV results file.) We need to use
CAMERA INNER JOIN SUBJECTto get the desiredThis change affects the following SQL Queries in
mapexplorer.config.json:NOTE! The following SQL Query still uses
LEFT JOINsince we do want the empty cameras: