Bug in get_tags()

[peterdesmet]

Reported by @aubrivliz:

Ik denk dat er een bug is in de query van get_tag()

etn/R/get_tags.R

Line 106 in 6553d4f

tag.tag_type AS tag_type,

Als je dit uitvoert:

tags <- get_tags(connection = my_con, tag_type = "acoustic-archival")

Dan krijg je niet enkel de acoustic-archival tags terug maar ook de sensor-tags van acoustic telemetry.

In het schema common heb je de volgende table:

SELECT id_pk, name, description, telemetry_type_fk FROM common.tag_device_type

Het zou moeten gefilterd worden op telemetry_type_fk

Dus ik denk dat er nog een join mist in de query 😊

[peterdesmet]

@aubrivliz the three categories are currently defined like this:

etn/inst/sql/tag.sql

Lines 4 to 8 in 6553d4f

	CASE
	WHEN tag_type.name = 'id-tag' THEN 'acoustic'
	WHEN tag_type.name = 'sensor-tag' AND acoustic_tag_id IS NOT NULL THEN 'acoustic-archival'
	WHEN tag_type.name = 'sensor-tag' THEN 'archival'
	END AS tag_type,

What would be a better way to assign those categories?

[aubrivliz]

This should be:

  CASE
    WHEN tag_type.id_pk = 1 THEN 'acoustic telemetry'
    WHEN tag_type.id_pk = 2 THEN 'acoustic telemetry'
    WHEN tag_type.id_pk = 3 THEN 'archival data'
    WHEN tag_type.id_pk = 4 THEN 'acoustic Archival'
    WHEN tag_type.id_pk = 5 THEN 'pit telemetry'
    WHEN tag_type.id_pk = 6 THEN 'radio telemetry'  
  END AS tag_type

But the tag_type field is misinterpreted.

If we speak of tag type this should be: "id-tag", "sensor-tag", "pit tag", "radio tag".
Those tags can be used in multiple telemetry types: "acoustic telemetry", "archival data" ...

Ideally there should be 2 fields, "tag_type" and "tag_telemetry_type".

this should be the correct way to add those fields:

SELECT
  tag_type.name as tag_type,
  tag_telemetry_type.name as tag_telemetry_type 
 FROM
  common.tag_device_limited AS tag_device
  LEFT JOIN common.tag_device_type as tag_type ON tag_device.tag_device_type_fk = tag_type.id_pk
  LEFT JOIN common.telemetry_type as tag_telemetry_type on  tag_telemetry_type.id_pk = tag_type.telemetry_type_fk