diff options
| author | Marius Peter <dev@marius-peter.com> | 2025-11-01 12:42:18 +0100 |
|---|---|---|
| committer | Marius Peter <dev@marius-peter.com> | 2025-11-01 12:42:18 +0100 |
| commit | 59f5f45bca1a4474fbe4f51bff2ec6c759ea4dd6 (patch) | |
| tree | 03f95b791ea9c47647cd8d377a539413d856b328 /models/nutrient-measurement.rkt | |
| parent | 3ef575dce9a873184f57078e5765b47698302c66 (diff) | |
Use the 'joined' table expression throughout.
No more string-based SQL queries! Only DSL!
Diffstat (limited to 'models/nutrient-measurement.rkt')
| -rw-r--r-- | models/nutrient-measurement.rkt | 37 |
1 files changed, 12 insertions, 25 deletions
diff --git a/models/nutrient-measurement.rkt b/models/nutrient-measurement.rkt index 3b997c8..523888b 100644 --- a/models/nutrient-measurement.rkt +++ b/models/nutrient-measurement.rkt @@ -144,38 +144,25 @@ (define (get-nutrient-measurement-values nutrient-measurement) (for/list ([(nutrient-id name formula value_ppm) (in-query (current-conn) - (string-join - '("SELECT n.id, n.canonical_name, n.formula, nv.value_ppm" - "FROM nutrient_values nv" - "JOIN nutrient_value_sets nvs ON nvs.id = nv.value_set_id" - "JOIN nutrient_measurements nm ON nm.id = nvs.nutrient_measurement_id" - "JOIN nutrients n ON n.id = nv.nutrient_id" - "WHERE nm.id = $1")) - (nutrient-measurement-id nutrient-measurement))]) + (select n.id n.canonical_name n.formula nv.value_ppm + #:from (TableExpr:AST ,joined) + #:where (= nm.id ,(nutrient-measurement-id nutrient-measurement))))]) (cons (nutrient nutrient-id name formula) value_ppm))) (define (get-nutrient-measurement-value nutrient-measurement nutrient) (query-maybe-value (current-conn) - (string-join - '("SELECT value_ppm" - "FROM nutrient_values nv" - "JOIN nutrient_value_sets nvs ON nvs.id = nv.value_set_id" - "JOIN nutrient_measurements nm ON nm.id = nvs.nutrient_measurement_id" - "WHERE nm.id = $1 AND nv.nutrient_id = $2")) - (nutrient-measurement-id nutrient-measurement) - (nutrient-id nutrient))) + (select value_ppm + #:from (TableExpr:AST ,joined) + #:where (and (= nm.id ,(nutrient-measurement-id nutrient-measurement)) + (= nv.nutrient_id ,(nutrient-id nutrient)))))) (define (get-latest-nutrient-measurement-value nutrient) (query-maybe-value (current-conn) - (string-join - '("SELECT value_ppm" - "FROM nutrient_values nv" - "JOIN nutrient_value_sets nvs ON nvs.id = nv.value_set_id" - "JOIN nutrient_measurements nm ON nm.id = nvs.nutrient_measurement_id" - "WHERE nv.nutrient_id = $1" - "ORDER BY nm.measured_on DESC" - "LIMIT 1")) - (nutrient-id nutrient))) + (select value_ppm + #:from (TableExpr:AST ,joined) + #:where (= nv.nutrient_id ,(nutrient-id nutrient)) + #:order-by nm.measured_on #:desc + #:limit 1))) ;; UPDATE |