Home » Structures » Structures_DataGrid » Bug #8490

[2006-08-17 14:53 UTC] michel dot dhooge at gmail dot com

Description:
------------
I extended SDG_DataSource_Array to take a DB_DO instance
and extract data using groupBy and Where. I'd happily
share my code if you find it globally useful. Below are 2
examples:

Using a simple DB_DO, the generated SQL request is:
SELECT CONCAT(version, " ", config) AS Version , date ,
count(*)AS __summary FROM myTable WHERE ( date IN
('2006-04-21','2006-04-22','2006-04-23') ) GROUP BY
Version , date WITH ROLLUP

And the generated table is:
+-------------+------------+------------+------------+
| Version | ven 21 avr | sam 22 avr | dim 23 avr |
+-------------+------------+------------+------------+
| 05.00 ? | 5 | | |
| 05.46 ? | 1 | | |
| 05.50 EPX06 | 370 | | |
| 05.51 ? | 1 | | |
| ? ? | 2 | | |
| Total | 379 | 0 | 0 |
+-------------+------------+------------+------------+

Second example:
SELECT foo AS Field, bar AS F2, date,
GROUP_CONCAT(distinct time order by 1 SEPARATOR ", ")AS
__summary FROM table WHERE ( date IN
('2006-04-21','2006-04-22') ) GROUP BY Field, F2, date
WITH ROLLUP

+-------+-----+--------------------+------------+
| Field | F2 | ven 21 avr | sam 22 avr |
+-------+-----+--------------------+------------+
| 125 | 63 | 05:37:35 | |
| 166 | 56 | 12:02:07 | |
| 182 | 61 | 06:38:20, 15:17:27 | |
| 186 | 59 | 06:29:28 | |
| 196 | 62 | 06:38:42, 15:56:09 | |
| 352 | 44 | 05:28:02 | |
| 87 | 58 | 09:18:47, 09:41:36 | |
| ? | 0 | 00:33:44 | |
+-------+-----+--------------------+------------+

[2006-08-17 18:08 UTC] michel dot dhooge at gmail dot com

Even though it extends SDG_DS_Array, this class is in fact
an extension of SDG_DS_DataObject. In short, SDG_DS_DO
creates an array with one row for each record of the
provided DO. I'd say the result is 1 dimension.

My class on the contrary creates a 2D result. It
summarizes the content of the database table according to
2 axes. The first one (vertical) displays all available
values of some DB fields. The 2nd one (horizontal) is used
to filter on some other fields; in the previous examples,
these fields are dates.

Let's say I have a table with login time of users. It
looks like:
SELECT Date, Time, Name FROM login;
2006-04-21, 05:37, foo
2006-04-21, 08:27, foo
2006-04-21, 15:37, bar
2006-04-22, 05:27, bar
2006-04-22, 12:51, bar
2006-04-22, 18:39, foo
...
Now, I only want to know which users logged-in, and how
many times. The configuration is simply Vertical: name,
horizontal: date = {2006-04-21, 2006-04-22} and the result
is
Name 2006-04-21 2006-04-22
bar 1 2
foo 2 1
Total 3 3

In addition to vertical and horizontal cfg, I can also
define the content of the cells. For instance cells: time
and the result is
Name 2006-04-21 2006-04-22
bar 15:37 05:27, 12:51
foo 05:37, 08:27 18:39

[2006-08-17 20:11 UTC] michel dot dhooge at gmail dot com

Yes, I already found #8353 but in my case the result is a real table with rows & columns.

Nevertheless it's true that the code I wrote simply creates an array that is handled to SDG_DS_Array. So the only relation with DS is the format of the array. However I need this kind of "summariser" in my project and maybe others also would. It is up to you to decide...

Thinking of it, a maybe faster implementation of SDG_DS_Array would be to use a table-array with row-arrays indexed by integer instead of strings (and with first row describing the columns' titles).

[2006-10-18 14:54 UTC] olivierg at php dot net

I agree with Mark, this is similar to request #8353. However, I think your request is more relevant, since you
don't produce a data tree, but a sort of tabular representation of a data tree.

But I really don't want to overload the DBDO driver with
these features. That would get heavy.

So I suggest that you submit your new driver on PEPr at:
http://pear.php.net/pepr/pepr-proposal-edit.php

Dan Rossi has recently done this for his Flexy renderer, and
it actually worked, his package was accepted:
http://pear.php.net/pepr/pepr-proposal-show.php?id=418

Mark: if it's okay with you, I propose that we now mark this bug as a Wont fix.