From 59f5f45bca1a4474fbe4f51bff2ec6c759ea4dd6 Mon Sep 17 00:00:00 2001 From: Marius Peter Date: Sat, 1 Nov 2025 12:42:18 +0100 Subject: Use the 'joined' table expression throughout. No more string-based SQL queries! Only DSL! --- models/nutrient-measurement.rkt | 37 ++++++++++++------------------------- 1 file changed, 12 insertions(+), 25 deletions(-) (limited to 'models') 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 -- cgit v1.2.3