month order with chart component

[jhautefeuille]

Hello,

When I use this component, the order of the months is incorrect.

What mistake could I have made ?

Thank you.

SELECT 
    'chart'                              AS component,
    "Répartition des jours pour l'année" AS title,
    'bar'                                AS type,
    TRUE                                 AS stacked,
    TRUE                                 AS toolbar,
    10                                   AS ystep;
SELECT date,
        CASE strftime('%m', date)
            WHEN '01' THEN 'Janvier'
            WHEN '02' THEN 'Février'
            WHEN '03' THEN 'Mars'
            WHEN '04' THEN 'Avril'
            WHEN '05' THEN 'Mai'
            WHEN '06' THEN 'Juin'
            WHEN '07' THEN 'Juillet'
            WHEN '08' THEN 'Août'
            WHEN '09' THEN 'Septembre'
            WHEN '10' THEN 'Octobre'
            WHEN '11' THEN 'Novembre'
            WHEN '12' THEN 'Décembre'
        END AS x,
        status AS series,
        sum(quotient) AS value
FROM workflow
WHERE strftime('%Y', date) = strftime('%Y', 'now')
GROUP BY x, status
ORDER BY x;

[lovasoa]

Are you using the latest version of sqlpage ? Isn't this the same as #951 ?

[jhautefeuille]

[2025-10-29T09:10:21.482Z INFO sqlpage::app_config] Configuration loaded from /home/julien/Documents/00_PERSONNEL/10_HACKING/SQLPAGE/ActivityTime/sqlpage-linux/sqlpage ✨ SQLPage v0.39.0 started successfully! ✨

It seems to be the same problem. However, I just tested it with the latest version 0.39.0, but the problem persists.

[lovasoa]

Hey ! I had a bit of a deeper look at your code.

Categories in bar charts with sparse data (with missing values for some x,series combinations) are sorted by name. You can fix your chart by adding the month number as a prefix in the label (add strftime('%m', date) || ' - ' || before your case expression).

But the cleanest way to handle this is to ensure your request returns data for every value of month and status, and sort by month number, not month name:

WITH monthly AS (
    SELECT
        CAST(strftime('%m', date) AS INTEGER) AS month_num,
        status,
        SUM(quotient) AS quotient_total
    FROM workflow
    WHERE date BETWEEN strftime('%Y-01-01', 'now') AND strftime('%Y-12-31', 'now')
    GROUP BY month_num, status
)
SELECT
    CASE month_list.month_num
            WHEN '01' THEN 'Janvier'
            WHEN '02' THEN 'Février'
            WHEN '03' THEN 'Mars'
            WHEN '04' THEN 'Avril'
            WHEN '05' THEN 'Mai'
            WHEN '06' THEN 'Juin'
            WHEN '07' THEN 'Juillet'
            WHEN '08' THEN 'Août'
            WHEN '09' THEN 'Septembre'
            WHEN '10' THEN 'Octobre'
            WHEN '11' THEN 'Novembre'
            WHEN '12' THEN 'Décembre'
    END AS x,
    status_list.status AS series,
    COALESCE(monthly.quotient_total, 0) AS value
FROM
 (select distinct month_num from monthly) as month_list,
 (select distinct status from workflow) as status_list
 LEFT JOIN monthly ON month_list.month_num = monthly.month_num AND status_list.status = monthly.status;

[jhautefeuille]

Thank you, with your explanation, I understood the error, that the sorting was done on the character string rather than the integer.

Nevertheless, I opted for your second suggestion, which is cleaner, more elegant and more consistent with SQL logic.

Thank you for your help.