search

building-envelope-data / metabase

Metabase of the product data network buildingenvelopedata.org
2 stars 3 forks source link

Contradictory search results #127

Open christoph-maurer opened 2 years ago

christoph-maurer commented 2 years ago

When I search https://www.buildingenvelopedata.org/data/optical for componentId equalTo "9dffecac-0903-4172-aea4-db8bed9d06b3", then I see one data set with Nearnormal Hemispherical Visible Transmittances 0.63.

When I search https://www.buildingenvelopedata.org/data/optical for Nearnormal Hemispherical Visible Transmittances greaterThanOrEqualTo 0.63, then I see no data sets at all. Why?

simon-wacker commented 2 years ago

I'm relying on HotChocolates support for filtering lists. This does not seem to work with PostgreSQL arrays as mapped by Npgsql. Independent of whether you use equalTo or greaterThanOrEqualTo, the resulting SQL where clause is WHERE o."NearnormalHemisphericalVisibleTransmittances" @> ARRAY[@__p_0]::double precision[]. Note that @> is the contains operator of PostgreSQL arrays. Three solutions come to mind:

  1. Do not use PostgreSQL arrays to store "mirrored values" and instead put them in their own tables and use foreign keys to link them to the data.
  2. Figure out where exactly code changes are necessary and ask the respective library developers to integrate them into the official codebase.
  3. Write all filters manually.

What fun it is to be a software developer working in a heterogeneous software world.

simon-wacker commented 2 years ago

Addition: As far as I can see, when we keep using PostgreSQL arrays, it's not possible to rewrite greaterThanOrEqualTo and lessThanOrEqualTo into SQL queries simply because PotgreSQL arrays do not have respective generic containment operators. With generic I mean that the developer cannot decide what containment means, that is, how values are compared with each other. With the built-in containment operators, elements are always compared by equality.