search

QutEcoacoustics / baw-server

The acoustic workbench server for storing and managing ecoacoustic data. Manages the structure and audio data. Provides an API for clients access.
Apache License 2.0
9 stars 4 forks source link

Ambiguous column reference for tzinfo_tz in expressions query #566

Closed atruskie closed 2 years ago

atruskie commented 2 years ago

Query from the client:

{"filter"=>{"projects.id"=>{"eq"=>1}, "recorded_end_date"=>{"greater_than_or_equal"=>{"expressions"=>["local_offset", "time_of_day"], "value"=>"07:00"}}, "recorded_date"=>{"less_than_or_equal"=>{"expressions"=>["local_offset", "time_of_day"], "value"=>"07:01"}}}, "sorting"=>{"order_by"=>"recorded_date", "direction"=>"desc"}, "paging"=>{"items"=>25}, "projection"=>{"include"=>["id", "recorded_date", "sites.name", "site_id", "canonical_file_name"]}, "page"=>"1", "format"=>"json", "controller"=>"audio_recordings", "action"=>"filter", "audio_recording"=>{}}

Generated an exception:

  PG::AmbiguousColumn: ERROR:  column reference "tzinfo_tz" is ambiguous
LINE 1: ...e_id" = "sites"."id" INNER JOIN "offset_table" ON "tzinfo_tz...
                                                             ^

  app/modules/api/response.rb:370:in `add_paging_and_sorting'

It looks like the extra join from the projection of sites.name makes the column reference ambiguous.