Table footer!

[francesco-cattoglio]

What are you building with SQLPage ?

Still building the same reporting tool with big numbers in a table 😄

What is your problem ? A description of the problem, not the solution you are proposing.

Since my tables contain a lot of data, it would be nice if it was possible to create a footer at the end of the table with some computed values (e.g. totals or averages)

Describe the solution you'd like

I have already hacked a solution by (ab)using the fact that in HTML you can have a <tfoot> before a <tbody>, and yet everything will be rendered correctly in the end. The solution currently works like this:

• inside the {{#each_row}} block in handlebars it is easy to know if this is the first row, but it is not possible to know if this is the last one
• therefore, I have added a top-level parameter called first_row_is_footer, so that inside the handlebars template the first row of data is used to build a <tfoot> tag. There are a few ifs here and there to make sure the rendering is still correct when no footer is present.
• This might seem strange at first, but it actually works out for most situations. I have a temporary table, and before the select that shows all table contents, I just do a select with some sum()s from the same temporary table, so that the code duplication in my SQL files is very little.
• As a bonus, it is easy to also add a freeze_footers top level parameter to have the footer sticky as well!
• As a malus, one has to decide if the footer should have the same "number formatting" using the toLocaleString function as the table body. For me it is a solid yes, but perhaps in some situations one might not want it, and as far as my changes goes this cannot be choosen user-side

Additional context

I have to clean up some code, and perhaps write an example, but if you are interested I might be able to open a pull request by tomorrow!

[lovasoa]

Hello !

I think I'm missing something. Why is there anything special to do to render a footer; why couldn't the footer be a normal table row ?

select 'table' as component;
select product, quantity, price from bill_items where id=$id;
select 'total' as product, sum(quantity) as quantity, sum(price) as price from my_table where id=$id; -- this is the "footer"; it's just a normal row at the end of the table 

[francesco-cattoglio]

Hi!
The footer is just a normal row in the same way the header could be a normal row. However in many situations having some data in the footer has nice advantages:

• When sorting by any column the footer is ignored, so the row with totals or averages always stays at the bottom, which is very useful for tables with the TRUE as sort property.
• The footer can be made sticky just like the header currently can
• If one wants to, footers can have special CSS applied to them. This is less relevant because we could already apply custom CSS for a given row in SQLPage

[aedion-a]

You can test on the example https://sql-page.com/component.sql?component=table (0.34 version)
When the table is sorted, the footer doesn't stay on the last row.
I don't know if it is a bug or a feature.

On the 33.1, I use a workaround to keep sticky footer with a normal row with a specific value in one cell and opacity:0 to hide this value.
With filter, for most use cases, the user doesn't know the "footer" row meets the search criterion and so remains displayed.
But in my case, I don't need sort on this table.
To have always this line on screen, I use this for both (second) header row and footer row to table > 15 lines.

Second solution, use a second table (no sort, no filter, only a normal row as footer row) with the same columns name (to have alignment). You can active sort option to have exactly same alignment with table above.

Maybe the first or the second solution works for you.

[aedion-a]

With release of version 0.34, the sample on the documentation works well.
Thanks.