ivan wrote:
I wish to realise SQL in DBIx syntax:
select * from table1 left join ( select * from table2 where date =
'mydate' ) as F ON ( table1.id = F.table1_id );
Is it possible ?
package DB::Schema::table1;
__PACKAGE__->load_components(qw/core/);
__PACKAGE__->table('table1');
__PACKAGE__->add_columns('id');
__PACKAGE__->set_primary_key('id');
package DB::Schema::table2;
__PACKAGE__->load_components(qw/InflateColumn::DateTime core/);
__PACKAGE__->table('table2');
__PACKAGE__->add_columns('id');
__PACKAGE__->set_primary_key('id');
__PACKAGE__->add_columns(qw/table1_id/);
__PACKAGE__->add_columns(datecolumn => { data_type => 'datetime' });
__PACKAGE__->has_many(table1s => 'DB::Schema::table1', 'table1_id');
package main;
$rs = $schema->resultset('table2')->search({ datecolumn => $mydate }, {
prefetch => 'table1' );
foreach my $table2 ($rs->all) {
foreach my $table1 ($_->table1s) {
print $table2->date . " => " . $table1->id . "\n";
}
}
... like that?
It mystifies me why people use so many subqueries for simple inner joins...
David
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
