From 57ef0bf0a715f2a3f7ace7ad481d27e566798a14 Mon Sep 17 00:00:00 2001 From: Marius Peter Date: Sat, 1 Nov 2025 15:19:37 +0100 Subject: Realign nutrient-target model with nutrient-measurement. --- models/nutrient-target.rkt | 39 +++++++++++++-------------------------- 1 file changed, 13 insertions(+), 26 deletions(-) diff --git a/models/nutrient-target.rkt b/models/nutrient-target.rkt index 4e43ef1..6f25564 100644 --- a/models/nutrient-target.rkt +++ b/models/nutrient-target.rkt @@ -20,7 +20,7 @@ (or/c nutrient-target? #f))] [get-nutrient-target-values (-> nutrient-target? (listof (cons/c nutrient? number?)))] [get-nutrient-target-value (-> nutrient-target? nutrient? number?)] - ;; Before the first target is createed, the "latest" value is basically false. + ;; The "latest" value is false before the first target is created. [get-latest-nutrient-target-value (-> nutrient? (or/c number? #f))] [delete-nutrient-target! (-> nutrient-target? void?)])) @@ -144,38 +144,25 @@ (define (get-nutrient-target-values nutrient-target) (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_targets nm ON nm.id = nvs.nutrient_target_id" - "JOIN nutrients n ON n.id = nv.nutrient_id" - "WHERE nm.id = $1")) - (nutrient-target-id nutrient-target))]) + (select n.id n.canonical_name n.formula nv.value_ppm + #:from (TableExpr:AST ,joined) + #:where (= nm.id ,(nutrient-target-id nutrient-target))))]) (cons (nutrient nutrient-id name formula) value_ppm))) (define (get-nutrient-target-value nutrient-target 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_targets nm ON nm.id = nvs.nutrient_target_id" - "WHERE nm.id = $1 AND nv.nutrient_id = $2")) - (nutrient-target-id nutrient-target) - (nutrient-id nutrient))) + (select value_ppm + #:from (TableExpr:AST ,joined) + #:where (and (= nm.id ,(nutrient-target-id nutrient-target)) + (= nv.nutrient_id ,(nutrient-id nutrient)))))) (define (get-latest-nutrient-target-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_targets nm ON nm.id = nvs.nutrient_target_id" - "WHERE nv.nutrient_id = $1" - "ORDER BY nm.effective_on DESC" - "LIMIT 1")) - (nutrient-id nutrient))) + (select value_ppm + #:from (TableExpr:AST ,joined) + #:where (= nv.nutrient_id ,(nutrient-id nutrient)) + #:order-by nm.effective_on #:desc + #:limit 1))) ;; UPDATE -- cgit v1.2.3