Forum OpenACS Development: Crossing table rows to rows and columns

Request notifications

Hi there,

How would I convert/rotate (i.e. cross table) the following datasource, which is originally returned by rows (i.e. datetime and total), to rows as hours and columns as dates, where the columns (dates) will be assigned with "total" as their value.

Here it is the chunk of code to convert from base64url to binary PNG

        select date_trunc('hour', o.creation_date) AS datetime,                                                                                                                                                 
        COUNT(1) AS total                                                                                                                                                                                   
        FROM cr_items ci, acs_objects o, cr_revisions cr                                                                                                                                                    
        WHERE ci.item_id = o.object_id                                                                                                                                                                      
        AND ci.item_id = cr.item_id                                                                                                                                                                         
        AND ci.latest_revision = cr.revision_id                                                                                                                                                             
        AND ci.content_type = :content_type                                                                                                                                                                 
        AND o.creation_date BETWEEN :creation_date::date - INTERVAL '6 day' AND :creation_date::date + INTERVAL '1 day'                                                                                     
        GROUP BY 1 ORDER BY datetime ASC'' 

From the table structure, such as:

hour          | total 
------------------------+-------
 2020-07-26 02:00:00+00 |     1
 2020-07-26 04:00:00+00 |     7
 2020-07-26 05:00:00+00 |     6
 2020-07-26 06:00:00+00 |     6
 2020-07-26 07:00:00+00 |    17
 2020-07-26 08:00:00+00 |    17
 2020-07-26 09:00:00+00 |     6
 2020-07-26 10:00:00+00 |     8
 2020-07-26 11:00:00+00 |    14
 2020-07-26 12:00:00+00 |    16
 2020-07-26 13:00:00+00 |    10
 2020-07-26 14:00:00+00 |    17
 2020-07-26 15:00:00+00 |    15
 2020-07-26 16:00:00+00 |     2
 2020-07-27 00:00:00+00 |     1
 2020-07-27 06:00:00+00 |     1
...
2020-08-01 07:00:00+00 |     7
 2020-08-01 08:00:00+00 |     4
 2020-08-01 09:00:00+00 |     7
 2020-08-01 10:00:00+00 |    10
 2020-08-01 11:00:00+00 |    20
 2020-08-01 12:00:00+00 |    25
 2020-08-01 13:00:00+00 |    18
 2020-08-01 14:00:00+00 |    14
 2020-08-01 15:00:00+00 |    12
 2020-08-01 16:00:00+00 |     4
(91 rows)

It would result in the table, as in:

hour    2020-7-26   2020-7-27   ... 2020-7-31   2020-8-01
0:00:00                         
1:00:00                         
2:00:00                         
3:00:00                         
4:00:00                         
5:00:00                 1       
6:00:00 2   2   4   22      7   4
7:00:00     8   2   3       8   1
8:00:00 3   8   4   1       9   4
9:00:00 4   6   2           35  8
10:00:00    9   19  14      2   10  2
11:00:00    11  8   7   13  10  13  10
12:00:00    12  7   18  12  8   12  5
13:00:00    6   14  8   24  10  6   6
14:00:00    8   10  9   7   14  11  4
15:00:00    21  10  4   2   13  15  
16:00:00    12  15  11  10  22  22  
17:00:00    30  14  11  28  10  29  
18:00:00                        1   
19:00:00                            
20:00:00                            
21:00:00                            
22:00:00                            
23:00:00                            

I've tried to use crosstab(text sql), to solve the problem directly in the datasource layer, but apparently tablefunc is not supported in the OACS schema https://www.postgresql.org/docs/9.2/tablefunc.html

ERROR:  function crosstab(unknown, unknown) does not exist
LINE 1: select * from crosstab('select o.creation_date::date AS day ...
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Furthermore, as the final output is a CSV file, which I was intended to use adproc template::list::writecsv, so I decided to give a try with template::list and db_multirow structures, but it resulted in a very complex and dirty source.

Starting from creating list elements dynamically, as in:

        for {set i 0} {$i < 7} {incr i} {
        set from [clock format [clock scan {+1 day} -base [clock scan $from]] -format "%Y%m%d"]
            lappend elements $from {label ""}
            append columns "$from "
        }

        ns_log Notice "ELEMENTS $elements"
        ns_log notice "COLUMNS $columns"

        template::list::create \
            -name persons \
            -multirow persons \
            -key item_id \
            -elements $elements

... to the db_multirow, where the column "hour" is truncated to display only hour, and the date part must be converted in a column of the template::list.

        db_multirow -extend { textinfo $columns} persons select_p $sql   {
            ....
            set date [lindex [split [lindex $hour 0] " "] 0]
            set hour [lindex [split [lindex $hour 0] " "] 1]

...

So confusing and cluttered, that I gave up to continue writing this garbage code, and decide to post this thread.

Thus, I was wondering if there is a better approach to write a beautiful code out of it, using api-doc.

Does anyone have an idea on how to write this crosstable display using OACS API?

Best wishes, I

Collapse
Posted by Dave Bauer on
Iuri,

I recommend installing tablefunc. It's available as a PostgreSQL contrib module. OpenACS doesn't have any specific requirements for it, but there's a lot of create stuff in there, if you are still using PostgreSQL 9.

Look into how your OS installed the PostgreSQL contrib packages. Then you can just create a SQL upgrade script to install it into your database.

I've used tablefunc with OpenACS projects since PostgreSQL 9 was new.

Collapse
Posted by Gustaf Neumann on
Iuri, many PostgreSQL installations, many extensions are already included, can can do simply in psql:

CREATE EXTENSION IF NOT EXISTS tablefunc;

If this works, your are done, you can use the crosstab family

oacs-5-10=# \df crosstab
crosstab crosstab2 crosstab3 crosstab4

otherwise progress as Dave suggested

see also:
https://stackoverflow.com/questions/3002499/postgresql-crosstab-query
https://www.vertabelo.com/blog/creating-pivot-tables-in-postgresql-using-the-crosstab-function/