[Dbix-class] Newbie question

[David Ihnen]

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