[Dbix-class] $c->stash for complex/cascaded relationship bridges

[Roland Philibert]

Thanks Peter, I'll explain further.
At the moment to DBIX relationship bridge many_to_many allows me to
retrieve easily this equivalent SQL example:

mysql> select table1.table1id,table2.table2id from table1,table2,table3
where table1.table1id='1' AND table3.ftable1id=table1.table1id AND
table3.ftable2id=table2.table2id;

by doing this simple search in my controller:
    $c->stash( rs =>
[$c->model('DB')->resultset('Table1')->search({table1id => '1',}, {})
],);

and retrieving table2 items in my TT template is easy too.

...but I'd like also to retrieve items related to table4 for each of my
table2 items....this is where I am stuck, what would you do in the
controller to stash those table4 items?

At the end I envisage a template wrote then like this:

[% FOREACH table1rows IN rs -%]
<table>
	<tr>
		<th>Table1id</th><td>&rarr;</td>
		<td>[% table1rows.table1id -%]</td>
	</tr>
	[% FOREACH table2rows IN table1rows.bridge_to_table2 -%]
	<tr>
		<td></td><td
colspan="2">&rarr;</td><th>Table2id(s)</th><td>&rarr;</td><td>[%
table2rows.table2id -%]</td>
	</tr>
		[% FOREACH table4rows IN ???????? -%]
		<tr>
		<td
colspan="5"></td><td>&rarr;</td><th>Table4id(s)</th><td>&rarr;</td><td>[
% table4rows.table4id -%]</td>
		
		</tr>
		[% END %]

	[% END %]
</table>
[% END %]

------------------------------------------------------------------------
--------------------------------------

Rendered HTML in this case would be:

Table1id	-> 	1
			->	Table2id(s)	->	1
							->
Table4id(s)	->   1	
       		->	Table2id(s)	->	2
							->
Table4id(s)	-> NULL	


With a following table contents:

mysql> select * from table1;
+----------+------------+------------+------------+
| table1id | col1table1 | col2table1 | col3table1 |
+----------+------------+------------+------------+
|        1 | 1_1        | 2_1        | 3_1        |
|        2 | 1_1_#2     | 2_1_#2     | 3_1_#2     |
+----------+------------+------------+------------+
2 rows in set (0.00 sec)

mysql> select * from table2;
+----------+------------+------------+------------+
| table2id | col1table2 | col2table2 | col3table2 |
+----------+------------+------------+------------+
|        1 | 1_2        | 2_2        | 3_2        |
|        2 | 1_2_#2     | 2_2_#2     | 3_2_#2     |
|        3 | 1_2_#3     | 2_2_#3     | 3_2_#3     |
+----------+------------+------------+------------+
3 rows in set (0.00 sec)

mysql> select * from table3;
+-----------+-----------+
| ftable1id | ftable2id |
+-----------+-----------+
|         1 |         1 |
|         1 |         2 |
+-----------+-----------+
2 rows in set (0.00 sec)

mysql> select * from table4;
+----------+------------+------------+------------+
| table4id | col1table4 | col2table4 | col3table4 |
+----------+------------+------------+------------+
|        1 | 1_4        | 2_4        | 3_4        |
+----------+------------+------------+------------+
1 row in set (0.00 sec)

mysql> select * from table5;
+-----------+-----------+
| ftable2id | ftable4id |
+-----------+-----------+
|         1 |         1 |
+-----------+-----------+
1 row in set (0.00 sec)


Many thanks for your help.
Roland
Aptina (UK) Limited, Century Court, Millennium Way, Bracknell, Berkshire, RG12 2XT. Registered in England No. 06570543.

This e-mail and any attachments contain confidential information and are solely for the review and use of the intended recipient. If you have received this e-mail in error, please notify the sender and destroy this e-mail and any copies.