Hello all! Here's a problem I've hit twice recently:
I'm trying to construct a list in list-builder that is pulled from 3 tables, with grouping using the <group> tag. The problem is that two of the tables map back to the first with a several-to-one relationship.
Table test_a contains unique entries. Table test_b contains 0, one, or multiple entries mapped to the id's in the test_a table. Table test_c ALSO contains 0, one, or multiple entries for the test_a entries.
here's example.sql (on postgres):
template::list::create \
-name "example" \
-multirow "example" \
-elements {
a_name {
label "A Name"
}
b_name {
label "B Name"
display_template { <group column="a_name"> @example.b_name@<br> </group> }
}
c_name {
label "C Name"
display_template { <group column="b_name"> @example.c_name@<br> </group> }
}
} \
db_multirow example example_query "
select test_a.a_id, a_name, b_name, c_name
from test_a left outer join test_b on test_a.a_id = test_b.a_id
left outer join test_c on test_a.a_id = test_c.a_id
Here's the underlying data:
create table test_a ( a_id integer not null, a_name varchar(30)) ;
insert into test_a (a_id, a_name) values (1, 'first') ;
insert into test_a (a_id, a_name) values (2, 'second') ;
insert into test_a (a_id, a_name) values (3, 'third') ;
insert into test_a (a_id, a_name) values (4, 'fourth') ;
create table test_b ( a_id integer not null, b_name varchar(30)) ;
insert into test_b (a_id, b_name) values (1, '1b 1 of 2') ;
insert into test_b (a_id, b_name) values (1, '1b 2 of 2') ;
insert into test_b (a_id, b_name) values (2, '2b 1 of 2');
insert into test_b (a_id, b_name) values (2, '2b 1 of 2');
insert into test_b (a_id, b_name) values (3, '3b 1 of 1') ;
create table test_c ( a_id integer not null, c_name varchar(30) ) ;
insert into test_c (a_id, c_name) values (1, '1c 1 of 1') ;
insert into test_c (a_id, c_name) values (2, '2c 1 of 2') ;
insert into test_c (a_id, c_name) values (2, '2c 2 of 2') ;
insert into test_c (a_id, c_name) values (4, '4c 1 of 1') ;
The desired result is something like:
first , 1b 1 of 2 1b 2 of 2 , 1c 1 of 1
second , 2b 1 of 2 2b 2 of 2, 2c 1 of 2 2c 2 of 2
third 3b 1 of 1 (blank)
fourth (blank) 4c 1 of 1
However, I'm actually getting:
first B: 1b 1 of 2 1b 2 of 2 C: 1c 1 of 1
second B: 2b 1 of 2 2b 1 of 2 2b 1 of 2 2b 1 of 2 C: 2c 2 of 2
third B: 3b 1 of 1 C: (blank)
fourth B: (blank) C: 4c 1 of 1
Note the duplication of entries in second's b, and the missing entry in second's c.
Suggestions? The first time I hit this bug, I actually wrote a plsql function to grab and string together the 'C' values. That works, removes the problem with needing to group multiple colums, but means having to put any markup into the sql function, which hardly seems ideal!
TIA
