From ab694964e37b88b5f4275b6620292a0aca0a8fe0 Mon Sep 17 00:00:00 2001
From: Shane Jaroch <nutratracker@protonmail.com>
Date: Sat, 6 Mar 2021 10:58:36 -0500
Subject: [PATCH] save query results for nutrient overview in table

---
 sql/import.sql     |  4 ++--
 sql/init.sql       |  1 +
 sql/tables_aux.sql | 17 +++++++++++++++++
 3 files changed, 20 insertions(+), 2 deletions(-)
 create mode 100644 sql/tables_aux.sql

diff --git a/sql/import.sql b/sql/import.sql
index 61095fd..4f90795 100644
--- a/sql/import.sql
+++ b/sql/import.sql
@@ -39,9 +39,9 @@ PRAGMA foreign_keys = 1;
 
 .import '| tail -n +2 ../data/nt/data_src.csv' data_src
 -- TODO: fix "INSERT failed: FOREIGN KEY constraint failed"
--- PRAGMA foreign_keys = 0;
+PRAGMA foreign_keys = 0;
 .import '| tail -n +2 ../data/nt/datsrcln.csv' datsrcln
--- PRAGMA foreign_keys = 1;
+PRAGMA foreign_keys = 1;
 
 .import '| tail -n +2 ../data/nt/serv_desc.csv' serv_desc
 .import '| tail -n +2 ../data/nt/serving.csv' serving
diff --git a/sql/init.sql b/sql/init.sql
index 9039d81..b0c0fee 100644
--- a/sql/init.sql
+++ b/sql/init.sql
@@ -1,2 +1,3 @@
 .read tables.sql
 .read import.sql
+.read tables_aux.sql
diff --git a/sql/tables_aux.sql b/sql/tables_aux.sql
new file mode 100644
index 0000000..0873060
--- /dev/null
+++ b/sql/tables_aux.sql
@@ -0,0 +1,17 @@
+# Saves time intensive query in new table
+CREATE TABLE nutrients_overview AS
+SELECT
+  id,
+  rda,
+  unit,
+  tagname,
+  nutr_desc,
+  COUNT(nut_data.nutr_id) AS n_foods,
+  ROUND(avg(nut_data.nutr_val), 3) AS avg_val
+FROM
+  nutr_def
+  INNER JOIN nut_data ON nut_data.nutr_id = id
+GROUP BY
+  id
+ORDER BY
+  id;
-- 
2.52.0