API not capturing conversion_id property update

[tilersmyth]

Describe the problem/bug

When converting an input measurement the 'conversion_id' prop of "device measurement settings" in the API stays null while the db reflects the update

Versions:

• Mycodo Version: 8.10.1
• Raspberry Pi Version: 3B+
• Raspbian OS Version: Raspberry Pi OS Lite

Reproducibility

Convert an input measurement via UI
GET api/settings/device_measurements/by_device_id/[unique_id]
Bug: DB has conversion_id ref but API does not

thank you

[kizniche]

I'm not sure what's causing this. Without db.ForeignKey('conversion.unique_id'), it works. I'm not sure how to fix it while keeping the Foreign Key.

[tilersmyth]

Interesting. I'm not as familiar with SQLAlchemy as I'd like to be, but I know other ORMs (such as TypeORM) require that you have a separate property if you want access to just the related table's id. Ex: "conversion": ForeignKey, "conversion_id": string..

Would you consider joining the conversion table for the api? I'd imagine that users would prefer having that data included if they've converted a measurement.

"device measurements": [
  {
  "channel": 0,
  "conversion": {
      "unique_id": "5b5e378a-42ac-4863-a2da-1e71897b244b",
      "unit": "F"
  },
  ...
  }
],

[kizniche]

That would be fine, I just don't know how to dot hat.

[tilersmyth]

Not sure if it's something you're interested in implementing but I've accomplished what I described above by doing the following:

In ~/Mycodo/databases/measurements.py

1. Add to DeviceMeasurements class:
   conversion = relationship("Conversion", foreign_keys="DeviceMeasurements.conversion_id")
2. Add to DeviceMeasurementsSchema:
   conversion = Nested(ConversionSchema)

In ~/Mycodo/mycodo/mycodo_flask/api/sql_schema_fields.py

1. Insert:

conversion_fields = api.model('Measurement Conversion Fields', {
    'id': fields.Integer,
    'unique_id': fields.String,
    ...
})

2. Update the following in the device_measurement_fields api model:
   + 'conversion': fields.Nested(conversion_fields)
   - 'conversion_id': fields.String (Optional to remove, but currently always null)

In ~/Mycodo/mycodo/mycodo_flask/api/input.py

1. in SettingsInputsUniqueID class add join to device_measurements query:
   change this:

list_measurements = return_list_of_dictionaries(
                measure_schema.dump(
                    DeviceMeasurements.query.filter_by(
                        device_id=unique_id).all(), many=True))

to this:

list_measurements = return_list_of_dictionaries(
                measure_schema.dump(
                    DeviceMeasurements.query.filter_by(
                        device_id=unique_id).join(DeviceMeasurements.conversion).all(), many=True))

Now the API returns the nested field if not null:

[kizniche]

I'm very interested. Thanks for figuring that out and sharing. I'll see about implementing it for all the foreign keys.

[kizniche]

conversion = Nested(ConversionSchema)

What is the import for this?

[tilersmyth]

from marshmallow_sqlalchemy.fields import Nested

[tilersmyth]

Hey - quick fix for this solution. Need to make the change below to ensure this is a LEFT JOIN or "device measurements" will return null if conversion_id is undefined

- device_id=unique_id).join(DeviceMeasurements.conversion).all(), many=True))

+ device_id=unique_id).join(DeviceMeasurements.conversion, isouter=True).all(), many=True))

I'll close this issue. Thanks for implementing.

[kizniche]

Thanks for figuring this out