grocery-manager/sqlpage/groups.sql

SET title = 'Groups';
SET products = COALESCE($products, sqlpage.cookie('products'));

DROP TABLE IF EXISTS sqlpage_groups;
CREATE TEMPORARY TABLE IF NOT EXISTS sqlpage_groups AS (
SELECT
  count(DISTINCT p.id) AS "Products",
  count(DISTINCT c.id) AS "Categories",
  count(DISTINCT g.id) AS "Groups",
  p.name AS product,
  c.name AS category,
  g.name AS "group"
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN groups g ON c.group_id = g.id
WHERE
  (p.name IN (SELECT v#>>'{}' FROM json_array_elements($products::json) j(v)) OR $products IS NULL)
AND
  (c.name IN (SELECT v#>>'{}' FROM json_array_elements($categories::json) j(v)) OR $categories IS NULL)
AND
  (g.name IN (SELECT v#>>'{}' FROM json_array_elements($groups::json) j(v)) OR $groups IS NULL)
GROUP BY ROLLUP ("group", category, product)
);

SET product_options = (
SELECT
  json_agg(json_build_object(
    'label', value,
    'value', value,
    'selected', value IN (SELECT v#>>'{}' FROM json_array_elements($products::json) j(v))
  ) ORDER BY value)
FROM (
  SELECT v#>>'{}'
  FROM json_array_elements($products::json) j(v)
  UNION
  SELECT product
  FROM sqlpage_groups
) o(value)
WHERE value IS NOT NULL
);

SELECT 'dynamic' AS component, sqlpage.run_sql('cookie.sql') AS properties;
SELECT 'dynamic' AS component, sqlpage.run_sql('theme.sql') AS properties;
SELECT 'dynamic' AS component, sqlpage.run_sql('nav.sql') AS properties;

SELECT
  'table' AS component,
  TRUE AS search,
  TRUE AS striped_rows,
  TRUE AS small,
  'Notice' AS markdown
;

SELECT
  "Products",
  "Categories",
  COALESCE("group", "Groups"||'') "Group"
FROM sqlpage_groups q
WHERE q.category IS NULL
ORDER BY product, category, "group"
;

SELECT '# No Data' AS "Notice"
WHERE NOT EXISTS(SELECT * FROM sqlpage_groups LIMIT 1);