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

[Roland Philibert]

Hello all.

I have the following situation in my database where table3 and table5 are acting as relationship bridges between a common table2:
table1-><-table3-><-table2-><-table5-><-table4>

So for correlated queries and for a given primary key in table1, how does one retrieve resultset(s) to stash to content in order to get all related columns of table4 for each related object of table2?
What would be the recommended/simplest way of doing this?
Any examples greatly appreciated.

Hope this make sense.
Many thanks in advance.

Roland


CREATE TABLE table1 (
table1id  MEDIUMINT NOT NULL,
col1table1 VARCHAR(10) NOT NULL,
col2table1 VARCHAR(10) NOT NULL,
col3table1 VARCHAR(10) NOT NULL,
PRIMARY KEY  (table1id)
) ENGINE = InnoDB;
/* defined in table1.pm by
__PACKAGE__->has_many(  "table3s",  "MyApp::Schema::Result::Table3",  { "foreign.ftable1id" => "self.table1id" },  { cascade_copy => 0, cascade_delete => 0 },);
__PACKAGE__->many_to_many('bridge_to_table2', 'table3s', 'ftable2id');

*/


CREATE TABLE table2 (
table2id  MEDIUMINT NOT NULL,
col1table2 VARCHAR(10) NOT NULL,
col2table2 VARCHAR(10) NOT NULL,
col3table2 VARCHAR(10) NOT NULL,
PRIMARY KEY  (table2id)
) ENGINE = InnoDB;
/* defined in table2.pm by
__PACKAGE__->has_many(  "table3s",  "MyApp::Schema::Result::Table3",  { "foreign.ftable2id" => "self.table2id" },  { cascade_copy => 0, cascade_delete => 0 },);
__PACKAGE__->has_many(  "table5s",  "MyApp::Schema::Result::Table5",  { "foreign.ftable2id" => "self.table2id" },  { cascade_copy => 0, cascade_delete => 0 },);
__PACKAGE__->many_to_many('bridge_to_table1', 'table3s', 'ftable1id');
__PACKAGE__->many_to_many('bridge_to_table4', table5s', 'ftable4id');

*/

/*--- relationship bridge between table1 and table2 --- */
CREATE TABLE table3 (
ftable1id MEDIUMINT NOT NULL,
ftable2id MEDIUMINT NOT NULL,
PRIMARY KEY (ftable1id,ftable2id),
FOREIGN KEY (ftable1id) REFERENCES table1(table1id),
FOREIGN KEY (ftable2id) REFERENCES table2(table2id)
) ENGINE = InnoDB;
/*--- defined in table3.pm by: 
__PACKAGE__->belongs_to(  "ftable1id",  "MyApp::Schema::Result::Table1",  { table1id => "ftable1id" },  { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },);
__PACKAGE__->belongs_to(  "ftable2id",  "MyApp::Schema::Result::Table2",  { table2id => "ftable2id" },  { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },);
*/


CREATE TABLE table4 (
table4id  MEDIUMINT NOT NULL,
col1table4 VARCHAR(10) NOT NULL,
col2table4 VARCHAR(10) NOT NULL,
col3table4 VARCHAR(10) NOT NULL,
PRIMARY KEY  (table4id)
) ENGINE = InnoDB;
/* defined in table4.pm by
__PACKAGE__->has_many(  "table5s",  "MyApp::Schema::Result::Table5",  { "foreign.ftable4id" => "self.table4id" },  { cascade_copy => 0, cascade_delete => 0 },);
__PACKAGE__->many_to_many('bridge_to_table2', 'table5s', 'ftable2id');

*/

/*--- relationship bridge between table2 and table4 --- */
CREATE TABLE table5 (
ftable2id MEDIUMINT NOT NULL,
ftable4id MEDIUMINT NOT NULL,
PRIMARY KEY (ftable2id,ftable4id),
FOREIGN KEY (ftable2id) REFERENCES table2(table2id),
FOREIGN KEY (ftable4id) REFERENCES table4(table4id)
) ENGINE = InnoDB;
/*--- defined in table5.pm by: 
__PACKAGE__->belongs_to(  "ftable2id",  "MyApp::Schema::Result::Table2",  { table2id => "ftable2id" },  { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },);
__PACKAGE__->belongs_to(  "ftable4id",  "MyApp::Schema::Result::Table4",  { table4id => "ftable4id" },  { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },);
*/






























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.