Component Tracking and recursive layout

[DSMejantel]

Hi !
I would like use the tracking component to watch quickly where I have to do something.
I can use a Common Table Expression with the list component like on the screenshot.
But the tracking component is more pretty and give more informations.
Il can ask him to display the 3 groups bit only the last contains all the datas :

How can I apply the recursive expression to the top-parameters and then to the row-parameters of the component ?

This is the code :

SET rangees= (SELECT count(distinct rang) FROM colonie where rucher_id=$id);

WITH RECURSIVE rangees(rangee,nombre) AS (
  SELECT rang, count(distinct numero) FROM colonie  WHERE colonie.rucher_id=$id and disparition::int<>1 and colonie.rang=1
  UNION ALL
  SELECT rang +1, (SELECT count(distinct numero) FROM colonie WHERE colonie.rucher_id=$id and disparition::int<>1 and colonie.rang=rangees.rangee +1)
  FROM colonie
  INNER JOIN rangees ON rangees.rangee = colonie.rang
  and rang < $rangees::int
)
select 
    'tracking'       as component,
    'rangée '||rangee||' : '||(SELECT count(distinct numero)||' ruches' FROM colonie WHERE colonie.rucher_id=$id and disparition::int<>1 and colonie.rang=rangee) as title,
    'bottom'            as placement,
    12               as width
    FROM rangees,colonie
    WHERE $tab='1' and colonie.rang=rangee GROUP BY rang
select 
    CASE WHEN colonie.tracing=2
    THEN 'warning'
    WHEN colonie.tracing=3
    THEN 'danger'
    ELSE 'success'
    END as color,
    'ruche n°'||numero||' (rangée '||rang||')' as title
    FROM colonie LEFT JOIN colvisite on colonie.numero=colvisite.ruche_id WHERE colonie.rucher_id=$id and disparition::int<>1 and $tab='1' GROUP BY colonie.numero ORDER BY rang;

[lovasoa]

Hi @DSMejantel !

I see you are pushing SQLPage to the edge of its capabilities. The problem in the code you are giving above is that the component selections are not at the right place. You are doing component, component, component, data, data, data instead of component, data, component, data, component, data. The latter is hard to do in sql directly, but you can use the dynamic component to achieve what you are trying to do.

Here is a short example

select 'dynamic' as component,
  '[' ||
    json_object(
          'component', 'tracking',
          'title', 'y=' || y
    ) || ',' ||
    group_concat(json_object(
        'title', 'x=' || x
    )) ||
  ']' as properties
from t GROUP BY y ORDER BY y;

[lovasoa]

Here is the example running on replit: https://replit.com/@pimaj62145/SQLPage-dynamic-multi-component-example#index.sql

[DSMejantel]

Hi,
Dynamic component is magic ! Just like you ! Thanks.

select 'dynamic' as component,
  '[' ||
    json_object(
          'component', 'tracking',
          'title', 'Rangée ' || rang,
          'width', 4
    ) || ',' ||
    group_concat(json_object(
        'title', 'Ruche '||numero,
        'color', case tracing when 3 then 'red' when 2 then 'orange' else 'green' end
    )) ||
  ']' as properties
from colonie WHERE colonie.rucher_id=$id and disparition::int<>1 and $tab='1' GROUP BY rang ORDER BY rang;