Rails - Jan 2006 - Generating nice tables

Making tables in templates is pretty easy, except for one minor problem. 
They tend to be fairly ugly.

If you have a model with three attributes, it''s very easy to create an 
html table that looks like this..

Col1 Col2 Col3
 A    B    D
 A    B    E
 A    C    F
 A    C    G

Which becomes difficult to read when you have a lot of repeated data.
What I would really like to generate should look more like this..

Col1 Col2 Col3
 A    B    D
           E
      C    F
           G

Using rowspans and align=middle as appropriate.  Basically I want to 
combine adjacent rows with the same data into a single cell with a 
rowspan.

Does anyone know of a simple algorithm to generate such tables (ideally 
from a collection of activerecord objects, of course)?

Thanks,
_Kevin

-- 
Posted via http://www.ruby-forum.com/.

Kevin,

I couldn''t give you the ruby syntax being a newby. But
wouldn''t a colspan be
easier to use. Then a simple nested loop would suffice.

The algorithm in human language would be something like this:

do loop for all rows
	reset colspan counter
	do loop for all cells
		If  contents next cell = 0
			colspan =  colspan + 1
		else
			set colspan variable and print contents
		end if
	end columnloop
end rowloop

Like I said, I''m a newbie, so sorry for not being able to hand you the
proper
syntax. But flow wise this should work.

Regards,

Gerard.

On Monday 02 January 2006 17:37, Kevin Olbrich tried to type something
like:
> Making tables in templates is pretty easy, except for one minor problem.
> They tend to be fairly ugly.
>
> If you have a model with three attributes, it''s very easy to
create an
> html table that looks like this..
>
> Col1 Col2 Col3
>  A    B    D
>  A    B    E
>  A    C    F
>  A    C    G
>
> Which becomes difficult to read when you have a lot of repeated data.
> What I would really like to generate should look more like this..
>
> Col1 Col2 Col3
>  A    B    D
>            E
>       C    F
>            G
>
> Using rowspans and align=middle as appropriate.  Basically I want to
> combine adjacent rows with the same data into a single cell with a
> rowspan.
>
> Does anyone know of a simple algorithm to generate such tables (ideally
> from a collection of activerecord objects, of course)?
>
> Thanks,
> _Kevin
-- 
"Who cares if it doesn''t do anything?  It was made with our new
Triple-Iso-Bifurcated-Krypton-Gate-MOS process ..."

My $Grtz =~ Gerard;
~
:wq!

Gerard wrote:
> Kevin,
> 
> I couldn''t give you the ruby syntax being a newby. But
wouldn''t a
> colspan be
> easier to use. Then a simple nested loop would suffice.
>
Colspans would be easier, but they wouldn''t solve the problem.  What
I''m
looking for is a table like this..

+===+===+===+
|   |   | D |
+   + B +===+
|   |   | E |
+ A +===+===+
|   |   | F |
+   + C +===+
|   |   | G |
+===+===+===+

I don''t see how I could do this with colspans.

-- 
Posted via http://www.ruby-forum.com/.

Kevin,

Sorry, I misread the valign middle in the previous email. I thought you just 
wanted ''white space'' like this.
+===+===+===+
| E | B | D | 
+===+   +===+   
| A |   | E |   
+   +===+===+
|   | C | F | 
+   +   +===+   
|   |   | G |   
+===+===+===+

That wouldn''t be that hard. Just don''t print out the cells
that are empty.

Quite a challenge you created btw .. :-)

It can be done though. One way could be to keep a counter for every column, 
and print out the whole table at the end of it. This could be done in a loop. 
But, i think, with a lot of columns this could result in poor performance.

I don''t know where you pull your data from but filling a two
dimensional array
could be a step in the right direction. Because you need to keep track of how 
many cell you have left on a row depending on the rowspans of previous cells.

I''ve created a quick html file with table and rowspans in it, and it
seems
that keeping counters is a (the only?) way to do it. If you look at the code 
below. The following would be a fact for in the loops. (Assuming you can go 
through the formentioned array, to establish what ''cells'' will
have the same
content and therefore calculate the rowspan value):

A cell having a rowspan of 4 means you have 1 less cell on this and the 
following 3. (And so on)

I''m willing to give this some more though if you''d like. But
in the
Netherlands it''s 22:00 right now. And I perform the biggest brain
teasers
usually in the morning, right after an espresso and a sigaret .. :-)

Let me know how you get along sofar.

regards,

Gerard.

<table border="2">
<tr>
  <td rowspan="4">A</td>
  <td>B</td>
  <td>C</td>
  <td>D</td>

</tr>
<tr>
  <td rowspan="3">B</td>
  <td>C</td>
  <td>D</td>
</tr>
<tr>
  <td>B</td>
  <td>C</td>
</tr>
<tr>
  <td>B</td>
  <td>C</td>
</tr>

</table>






On Monday 02 January 2006 21:11, Kevin Olbrich tried to type something
like:
> Gerard wrote:
> > Kevin,
> >
> > I couldn''t give you the ruby syntax being a newby. But
wouldn''t a
> > colspan be
> > easier to use. Then a simple nested loop would suffice.
>
> Colspans would be easier, but they wouldn''t solve the problem. 
What I''m
> looking for is a table like this..
>
> +===+===+===+
>
> |   |   | D |
>
> +   + B +===+
>
> |   |   | E |
>
> + A +===+===+
>
> |   |   | F |
>
> +   + C +===+
>
> |   |   | G |
>
> +===+===+===+
>
> I don''t see how I could do this with colspans.
-- 
"Who cares if it doesn''t do anything?  It was made with our new
Triple-Iso-Bifurcated-Krypton-Gate-MOS process ..."

My $Grtz =~ Gerard;
~
:wq!

Perhaps I''m misunderstanding the question. This looks like it can be 
handled in the database quite nicely.

Did you check out the SQL GROUP BY clause? If your database supports 
ROLLUP, it may be what you are looking for. E.g.,

@result_set = MyModel.find_by_sql(''SELECT c1, c2, c3, SUM(c1), SUM(c2),
SUM(c3) WHERE ? GROUP BY c1, c2, c3 WITH ROLLUP'', my_criterion)

The resultant table will contain sum values for c1, c2, and c3, 
respectively, but additional rows are injected on category breaks. At 
these category breaks, the fields are set to null. I.e.,

value, value, nil === break for c3
value, nil, nil === break for c2
nil, nil, nil === break for c1

There''s probably some ultra-concise Ruby way to iterate this
collection,
setting the appropriate column values to blank except on first 
occurrence and to ''subtotal'' at the break, ''grand
total'' when all are
nil. I haven''t thought about that.

Does this help?

Kevin Olbrich wrote:
> Making tables in templates is pretty easy, except for one minor problem. 
> They tend to be fairly ugly.
> 
> If you have a model with three attributes, it''s very easy to
create an
> html table that looks like this..
> 
> Col1 Col2 Col3
>  A    B    D
>  A    B    E
>  A    C    F
>  A    C    G
> 
> Which becomes difficult to read when you have a lot of repeated data.
> What I would really like to generate should look more like this..
> 
> Col1 Col2 Col3
>  A    B    D
>            E
>       C    F
>            G
> 
> Using rowspans and align=middle as appropriate.  Basically I want to 
> combine adjacent rows with the same data into a single cell with a 
> rowspan.
> 
> Does anyone know of a simple algorithm to generate such tables (ideally 
> from a collection of activerecord objects, of course)?
> 
> Thanks,
> _Kevin

-- 
Posted via http://www.ruby-forum.com/.

Steve Ross wrote:
> Perhaps I''m misunderstanding the question. This looks like it can
be
> handled in the database quite nicely.
> 
> Did you check out the SQL GROUP BY clause? If your database supports 
> ROLLUP, it may be what you are looking for. E.g.,
> 
This is an interesting idea, I will have to look into it some more.

-- 
Posted via http://www.ruby-forum.com/.

Steve,

Very nifty what you wrote down here. Getting ahead of things, IMHO, it would 
make a nice practical performance test to see what mysql does with this in 
comparison to ruby. I must admit there''s some curiosity on this side ..
;-)

Regards,

Gerard.

On Monday 02 January 2006 22:48, Steve Ross tried to type something
like:
> Perhaps I''m misunderstanding the question. This looks like it can
be
> handled in the database quite nicely.
>
> Did you check out the SQL GROUP BY clause? If your database supports
> ROLLUP, it may be what you are looking for. E.g.,
>
> @result_set = MyModel.find_by_sql(''SELECT c1, c2, c3, SUM(c1),
SUM(c2),
> SUM(c3) WHERE ? GROUP BY c1, c2, c3 WITH ROLLUP'', my_criterion)
>
> The resultant table will contain sum values for c1, c2, and c3,
> respectively, but additional rows are injected on category breaks. At
> these category breaks, the fields are set to null. I.e.,
>
> value, value, nil === break for c3
> value, nil, nil === break for c2
> nil, nil, nil === break for c1
>
> There''s probably some ultra-concise Ruby way to iterate this
collection,
> setting the appropriate column values to blank except on first
> occurrence and to ''subtotal'' at the break,
''grand total'' when all are
> nil. I haven''t thought about that.
>
> Does this help?
>
> Kevin Olbrich wrote:
> > Making tables in templates is pretty easy, except for one minor
problem.
> > They tend to be fairly ugly.
> >
> > If you have a model with three attributes, it''s very easy to
create an
> > html table that looks like this..
> >
> > Col1 Col2 Col3
> >  A    B    D
> >  A    B    E
> >  A    C    F
> >  A    C    G
> >
> > Which becomes difficult to read when you have a lot of repeated data.
> > What I would really like to generate should look more like this..
> >
> > Col1 Col2 Col3
> >  A    B    D
> >            E
> >       C    F
> >            G
> >
> > Using rowspans and align=middle as appropriate.  Basically I want to
> > combine adjacent rows with the same data into a single cell with a
> > rowspan.
> >
> > Does anyone know of a simple algorithm to generate such tables
(ideally
> > from a collection of activerecord objects, of course)?
> >
> > Thanks,
> > _Kevin
-- 
"Who cares if it doesn''t do anything?  It was made with our new
Triple-Iso-Bifurcated-Krypton-Gate-MOS process ..."

My $Grtz =~ Gerard;
~
:wq!

On 1/2/06, Kevin Olbrich <kevin.olbrich@duke.edu>
wrote:
> Gerard wrote:
> > Kevin,
> >
> > I couldn''t give you the ruby syntax being a newby. But
wouldn''t a
> > colspan be
> > easier to use. Then a simple nested loop would suffice.
> >
>
> Colspans would be easier, but they wouldn''t solve the problem. 
What I''m
> looking for is a table like this..
>
> +===+===+===+
> |   |   | D |
> +   + B +===+
> |   |   | E |
> + A +===+===+
> |   |   | F |
> +   + C +===+
> |   |   | G |
> +===+===+===+
>
> I don''t see how I could do this with colspans.
 The HTML that generates the output you want looks like this:

<table border="1">
  <tr>
    <td rowspan="4" valign="middle">A</td>
    <td rowspan="2" valign="middle">B</td>
    <td rowspan="1" valign="middle">D</td>
  </tr><tr>
    <td rowspan="1" valign="middle">E</td>
  </tr><tr>
    <td rowspan="2" valign="middle">C</td>
    <td rowspan="1" valign="middle">F</td>
  </tr><tr>
    <td rowspan="1" valign="middle">G</td>
  </tr>
</table>

The following methods, stuffed in a helper, will print out a table
like that when given an array.  If you want to send in an arbitrary
collection of AR objects, either adjust the methods, or use Array#map.

def bracketted_table(arr)
  "<table>\n 
<tr>\n#{table_brackets(arr)}</table>\n"
end

def table_brackets(arr, depth=0, start=0)
  result = ''''
  return result if arr[start].nil?

  height = 2 ** (Math.log(arr.length).ceil - depth)

  result << "    <td rowspan=\"#{height}\"
valign=\"middle\">#{arr[start]}</td>\n"

  result << table_brackets(arr, depth+1, start*2+1)
  result << table_brackets(arr, depth+1, start*2+2)

  if depth == Math.log(arr.length).ceil
    result << "  </tr>\n"
    result << "  <tr>\n" unless start == arr.length - 1
  end
  return result
end

To test, try this:
    test_array =
[''A'',''B'',''C'',''D'',''E'',''F'',''G'']
    puts bracketted_table(test_array)

The only quirks from a standard walk-binary-tree-stored-in-an-array
algorithm are due to the logic of adding in the <tr> and </tr> tags
around the various entries.  Also note that if your result set does
not have 2^n - 1 elements in it (3, 7, 15, etc), it will probably
render looking a bit screwed up.

- Jamie Macey

I can''t give you a good benchmark comparison to MySQL-to-Ruby but it 
might be irrelevant. Remember, aggregate functions in SQL will most 
often return fewer rows than had you queried the entire database and 
then aggregated the data yourself. Moving the data from your database 
server to your Web application can be expensive.

Even running the database locally, I find that the aggregation I 
described runs a bit quicker than returning a complete result set and 
iterating the list (and I didn''t even write the code to aggregate the 
results).

Sometimes the practicality of using database functions so far outweighs 
the benefits of loose coupling that find_by_sql is a good choice.

I''m still not clear if this solves the initial problem, though.


Gerard wrote:
> Steve,
> 
> Very nifty what you wrote down here. Getting ahead of things, IMHO, it 
> would
> make a nice practical performance test to see what mysql does with this 
> in
> comparison to ruby. I must admit there''s some curiosity on this
side ..
> ;-)
> 
> Regards,
> 
> Gerard.
> 
> On Monday 02 January 2006 22:48, Steve Ross tried to type something 
> like:
>> respectively, but additional rows are injected on category breaks. At
>>
>> >  A    B    D
>> >       C    F
>> > _Kevin
> --
> "Who cares if it doesn''t do anything?  It was made with our
new
> Triple-Iso-Bifurcated-Krypton-Gate-MOS process ..."
> 
> My $Grtz =~ Gerard;
> ~
> :wq!

-- 
Posted via http://www.ruby-forum.com/.

Steve,

On Tuesday 03 January 2006 00:30, Steve Ross tried to type something
like:
> I can''t give you a good benchmark comparison to MySQL-to-Ruby but
it
> might be irrelevant. Remember, aggregate functions in SQL will most
> often return fewer rows than had you queried the entire database and
> then aggregated the data yourself. Moving the data from your database
> server to your Web application can be expensive.
So true! Your absolutely right (I gues it was late yesterday). Seen some bad 
examples of this in the past. pulling 100''s of MB''s from a db
server into a
local office network to query only of a couple of rows.
> Even running the database locally, I find that the aggregation I
> described runs a bit quicker than returning a complete result set and
> iterating the list (and I didn''t even write the code to aggregate
the
> results).
> Sometimes the practicality of using database functions so far outweighs
> the benefits of loose coupling that find_by_sql is a good choice.
Again I agree completely .. :-)
> I''m still not clear if this solves the initial problem, though.
Curious myself.

Regards,

Gerard.
>
> Gerard wrote:
> > Steve,
> >
> > Very nifty what you wrote down here. Getting ahead of things, IMHO, it
> > would
> > make a nice practical performance test to see what mysql does with
this
> > in
> > comparison to ruby. I must admit there''s some curiosity on
this side ..
> > ;-)
> >
> > Regards,
> >
> > Gerard.
> >
> > On Monday 02 January 2006 22:48, Steve Ross tried to type something
> >
> > like:
> >> respectively, but additional rows are injected on category breaks.
At
> >>
> >> >  A    B    D
> >> >       C    F
> >> > _Kevin
> >
> > --
> > "Who cares if it doesn''t do anything?  It was made with
our new
> > Triple-Iso-Bifurcated-Krypton-Gate-MOS process ..."
> >
> > My $Grtz =~ Gerard;
> > ~
> >
> > :wq!
-- 
"Who cares if it doesn''t do anything?  It was made with our new
Triple-Iso-Bifurcated-Krypton-Gate-MOS process ..."

My $Grtz =~ Gerard;
~
:wq!