Error filtering on a nested field


#1

On type PointMeasurementReport there is a field measurement: PointMeasurement. The following fetch:

PointMeasurementReport.fetch({filter: Filter.intersects('measurement.parent.id', registerIds).and.contains('facility.id', 's00313').and.le('measurement.insertionDate', sd), include:"measurement.this"})

cannot be executed due to errors of this form:

Unable to execute sql SELECT T.FACILITY_R, T.METER_R, T.REGISTER_R, T.START_T, T.START_I, T.VALUE_D, T.UNIT_R, T.NATURE_S, T.QUALIFICATION_S, T.HEADER_R, T.MEASUREMENT_R, T.ID, T.VERSION, T.NAME, T.TENANT_TAG_ID, T.CREATED, T.CREATED_I, T.CREATED_BY, T.UPDATED, T.UPDATED_I, T.UPDATED_BY, T.META_TIMESTAMP_T, T.META_TIMESTAMP_I, T.META_COMMENT, T.PROVENANCE, T.SOURCE
FROM C3_2_PTPMRPT T
  LEFT OUTER JOIN C3_2_STRUCTURE_POINTMEASUREMENT T2 ON T2.TENANT_TAG_ID = T.TENANT_TAG_ID AND T2.ID = T.MEASUREMENT_R
WHERE T.TENANT_TAG_ID=:v1 AND (T2.INSERTIONDATE_T<=TO_TIMESTAMP(:v2, 'YYYY-MM-DD"T"HH24:MI:SS.MSH:M') AND T.FACILITY_R LIKE CONCAT('%', :v3, '%'))
ORDER BY T.ID
LIMIT 2001
ERROR: relation "c3_2_structure_pointmeasurement" does not exist
  Position: 385

There is a workaround using two fetches but it is slow. How can we make the above work?

Thanks


#2

@AlexBakic The problem is that PointMeasurement is stored in Cassandra and, I’m assuming that PointMeasurementReport is stored in Postgres. I’m not sure what the purpose of PointMeasurementReport is, but if there is a report for each measurement, that would be ill advised as the PG table would grow too large. If that is the case, then I strongly recommend you reconsider the decision to store PointMeasurementReport in Postgres.

The actual error you are seeing is a bug in that we didn’t detect that the target of the intersects function is in Cassandra. You should have received an error indicating that the filter was invalid for that reason. Please file a ticket for that.

We do not support filters that span from PG to Cassandra or vice versa as there is no underlying db support and client side filtering would be prohibitively expensive. So you pretty much will have to do 2 queries, regardless.


#3

Thanks, @trothwein, I should have added @db(datastore='cassandra') to PointMeasurementReport. So if both types are in Cassandra, perhaps it will work, if I understood correctly.


#4

@AlexBakic Unfortunately not. Cassandra isn’t really a database at all. As a result, filtering is very limited. You can’t have a filter that spans Cassandra types (e.g. your query). Within a single Cassandra type, you can only search by id (or partition key) 'AND’ed with other more qualifying predicates (e.g. you can’t search for all entries that satisfy a criteria.


#5

Can we at least have an error when we try such a filter, instead of a silent failure with the empty result? It could save us a lot of time.
Thanks


#6

Can you describe what you are trying to achieve with PointMeasurementReport type? Maybe there’s another way to do this.


#7

The customer wants to know all the details about the PointMeasurements (and basic consumption, i.e. their differences). The bills are tightly related to these. The customer tries to extract as much correspondence as possible between these and complex metrics.


#8

Why can’t we store whatever information necessary on the measurements themselves? Extend the type into PointMeasurementWithAllDetails and you don’t need to do joins?


#9

Perhaps, some fields have been duplicated for filtering purposes. If the queries become wild, we’ll do so. Except that the reports are also about estimated measurements and consumptions, which are not stored as PointMeasurements.


#10

@AlexBakic I believe you would get an error if both types were in Cassandra and you tried that filter. If not, please add a ticket.