Questions about Null values with Columns Component

[DSMejantel]

Hi,
I enjoy to work with the new 0.31 release.
I was very interested by this : Optionally skip displaying items (null as item).
I can build this page :

But, I have some problems with 'Null Values'
I have to write this not pretty lines to create real Null values :

-- Enregistrement d'un nouvelle prestation dans la base
INSERT INTO repas(jour,formule_id,service_id,total) SELECT :date_rep as jour, :formule as formule_id, :service as service_id, :total as total where :total is not Null;
UPDATE menu SET entree=Null WHERE LENGTH(entree)<1;
UPDATE menu SET plat=Null WHERE LENGTH(plat)<1;
UPDATE menu SET dessert=Null WHERE LENGTH(dessert)<1;

and then these lines with condition in the columns component

    CASE WHEN entree is not Null THEN json_object('icon','salad','color','green','description',entree) END as item,
    CASE WHEN plat is not Null THEN json_object('icon','meat','color','red','description',plat) END as item,
    CASE WHEN dessert is not Null THEN json_object('icon','cheese','color','orange','description',dessert) END as item,
    json_object('icon','receipt-2','color','vk','description',printf("%.2f",prix)||' euros') as item,

Is there a way to make this work easier and more elegant ?

[lovasoa]

Hi David !

I see you are building a new app already, you are unstoppable !

What I would have done here is store the different meal courses (entrée, plat, dessert) in a separate table.

Something like

SQLite Schema:

-- Table to store individual course details
CREATE TABLE courses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,         -- Name of the course (e.g., "Salade au chèvre chaud")
    description TEXT,           -- Optional description
    icon TEXT,                  -- Icon name for UI (e.g., "salad", "meat")
    color TEXT                 -- Color for UI (e.g., "green", "red")
);

-- Table to store meals (combination of courses for a given date and service)
CREATE TABLE meals (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date DATE NOT NULL,             -- Date of the meal
    service TEXT NOT NULL,          -- Service type (e.g., "Midi", "Soir")
    total_price REAL DEFAULT 0.0    -- Total price for the meal
);

-- Junction table to associate meals with specific courses
CREATE TABLE meal_courses (
    meal_id INTEGER NOT NULL,       -- Foreign key referencing meals
    course_id INTEGER NOT NULL,     -- Foreign key referencing courses
    PRIMARY KEY (meal_id, course_id),
    FOREIGN KEY (meal_id) REFERENCES meals(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

INSERT INTO courses (name, icon, color) VALUES
('Salade au chèvre chaud', 'salad', 'green'),
('Petit salé aux lentilles du Puy', 'meat', 'red'),
('Fouace et sorbet aux fruits rouges', 'cheese', 'orange'),
('Tartelette au citron meringuée', 'dessert', 'yellow'),
('Salade gourmande du sud-ouest', 'salad', 'green'),
('Tarte aux pommes', 'dessert', 'brown');

INSERT INTO meals (date, service, total_price) VALUES
('2024-11-28', 'Midi', 15.00),
('2024-12-03', 'à emporter', 2.50),
('2024-12-13', 'Midi', 8.00);

INSERT INTO meal_courses (meal_id, course_id) VALUES
-- Meal on 2024-11-28 (Repas)
(1, 1), -- Salade au chèvre chaud
(1, 2), -- Petit salé aux lentilles du Puy
(1, 3), -- Fouace et sorbet aux fruits rouges

-- Meal on 2024-12-03 (Plat)
(2, 4), -- Tartelette au citron meringuée

-- Meal on 2024-12-13 (Formule)
(3, 5), -- Salade gourmande du sud-ouest
(3, 6); -- Tarte aux pommes

---

select 
  (
    select json_group_array(json_object(
      'icon', c.icon,
      'color', c.color,
      'description', c.name
    ))
    FROM (
      select icon,color,name from courses c, meal_courses mc where c.id = mc.course_id AND mc.meal_id = meals.id 
      UNION ALL
      SELECT 'receipt-2' AS icon, 'vk' AS color, printf('%.2f', meals.total_price) aS name
    ) c
  ) as item
from meals;

The query creates a JSON array for each meal, including its courses and total price. It joins courses and meal_courses for course data, and appends a formatted total price using UNION ALL in a subquery.

No need for any particular null handling anywhere. When a course does not exist in a meal, just don't add it to meal_courses.

[DSMejantel]

Thanks, I Will try that

[DSMejantel]

Hi, I understand what you mean.
But are you sure we can order the courses in the meal ? If we have lemon ice before sup, it's not very logical...
Maybe we can add a position value like this :

CREATE TABLE courses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,         -- Name of the course (e.g., "Salade au chèvre chaud")
    position INTEGER,
    allergenes TEXT,           -- Optional description
    icon TEXT,                  -- Icon name for UI (e.g., "salad", "meat")
    color TEXT                 -- Color for UI (e.g., "green", "red")
);

For moment, I tried to use nullif(:value,'') in the insert instead of my UPDATE menu SET dessert=Null WHERE LENGTH(dessert)<1;
So we can continue to work with CASE WHEN for item in columns component

[lovasoa]

Indeed, you are right! We should add position column and order by it

[nofue]

How about putting the 'position' into the junction table? Then the user will be able to sort specific menus -- if there's a salad before and one more after the main course, f.e. Things might become even trickier if you want to have wine with courses, as the same wine might accompany different courses in different menus. The 'position' variable in menu_courses will take care of all of these possibilities.

[DSMejantel]

Hi,

Using the "fusion table" to manage courses separately is a good idea, but I’m encountering some issues. Without using the workaround of NULLIF, I face problems: the clause for NULL entries doesn’t work, and I end up with courses without names in my table.

What might I be doing wrong?

-- Registering the menu  
-- Starter  
INSERT INTO plats(name, position_id, repas_id, icon, color)  
SELECT :entree, 1, :id, 'salad', 'green' WHERE :entree IS NOT NULL;  

-- Intermediate course  
INSERT INTO plats(name, position_id, repas_id, icon, color)  
SELECT :entremets, 2, :id, 'soup', 'green' WHERE NULLIF(:entremets, '') IS NOT NULL;  

[lovasoa]

The second form seems correct to me. You can do a single insert statement:

-- Registering the menu  
-- Starter  
INSERT INTO plats(name, position_id, repas_id, icon, color)  
  SELECT :entree, 1, :id, 'salad', 'green' WHERE :entree <> ''
  UNION ALL
  SELECT :entremets, 2, :id, 'soup', 'green' WHERE :entremets <> '';