search

Indicia-Team / warehouse

GNU General Public License v3.0
5 stars 3 forks source link

Species alerts being set for all items in a taxon list even if the user has specified alerts for one item #210

Closed andrewvanbreda closed 7 years ago

andrewvanbreda commented 8 years ago

Species alerts being sent for all items in a taxon list even if the user has specified alerts for one item using an external_key or meaning_id

Look at the speices alert 55,116753,Pól_Mac Cana_1,TRUE,TRUE,,,23,iRecord,NHMSYS0021109804,,Tamias sibiricus,Siberian Chipmunk,NI Invasive Red Alert List,01/04/2016 08:44,1,admin,01/04/2016 08:44,1,admin,171

The 171 is the taxon list, but they have an external key specified. They are getting sent alerts for every species in the list not just Siberian Chipmunk

This code here, (sa.taxon_meaning_id = od.taxon_meaning_id OR sa.external_key = od.taxa_taxon_list_external_key OR sa.taxon_list_id = cttlall.taxon_list_id)

I think should look more like this (not tested yet, this might not be the correct fix)

    ((sa.taxon_meaning_id = od.taxon_meaning_id
      OR
      sa.external_key = od.taxa_taxon_list_external_key)
      AND
      ((sa.taxon_list_id = cttlall.taxon_list_id) OR sa.taxon_list_id IS NULL))
andrewvanbreda commented 7 years ago

Implemented and live, code implemented is as follows after JVB point out without the NOT NULL check it might match on two empty external keys (although in practice tests show it doesn't, NOT NULL check included for safety)

  LEFT JOIN cache_taxa_taxon_lists cttlall on cttlall.taxon_meaning_id=od.taxon_meaning_id 
      OR (cttlall.external_key IS NOT NULL AND od.taxa_taxon_list_external_key IS NOT NULL AND cttlall.external_key=od.taxa_taxon_list_external_key)
  JOIN index_websites_website_agreements iwwa on iwwa.to_website_id=od.website_id and iwwa.receive_for_reporting=true
  JOIN species_alerts sa ON 
    (sa.location_id IS NULL OR sa.location_id=ils.location_id)
    AND 
      (sa.taxon_meaning_id = od.taxon_meaning_id
      OR
      sa.external_key = od.taxa_taxon_list_external_key
      OR
      sa.taxon_list_id = cttlall.taxon_list_id)
    AND
      (sa.alert_on_entry='t' AND od.cud='C'
      OR
      (sa.alert_on_verify='t' AND (od.record_status='V' AND od.cud='U')))
    AND
      sa.website_id=iwwa.from_website_id
    AND 
      sa.deleted='f'
andrewvanbreda commented 7 years ago

Not that the system is not intended for use with a single item specified and a taxon list at the same time