[Dbix-class] How to code an 'equi-join'

[Matt S Trout]

On Mon, Feb 13, 2006 at 10:16:30AM +0800, Maurice Height wrote:
> I want to do a simple 'equi-join' on 2 MySQL tables
> but cannot figure out how to do it.
> 
> My tables are defined in DBIC as:
> 
> package DB_Schema::History;
> use base qw/DBIx::Class/;
> __PACKAGE__->load_components(qw/Core/);
> __PACKAGE__->table('history');
> __PACKAGE__->add_columns(qw/code day open high low close volume/);
> __PACKAGE__->set_primary_key(qw/code day/);
> 1;
> 
> And
> 
> package DB_Schema::Company;
> use base qw/DBIx::Class/;
> __PACKAGE__->load_components(qw/Core/);
> __PACKAGE__->table('company');
> __PACKAGE__->add_columns(qw/code name igroup/);
> __PACKAGE__->set_primary_key(qw/code/);
> 1;
> 
> The following code works OK in MySQL Query Browser:
> 
> SELECT h.code, c.name, (h.close-h.open) as diff
> FROM   history h
> JOIN   company c ON h.code = c.code
> WHERE  h.code = 'ANZ' AND day = '2006-02-10'
> 
> How do I code this in DBIx::Class?
> Am I missing a relationship statement somewhere?
> These 2 tables are ONLY related by the column 'code'.

DB_Schema::History->belongs_to('code', 'DB_Schema::Company');

DB_Schema::Company->has_many('history_entries', 'DB_Schema::History', 'code');

should do the trick. There are a few applicable examples in the test suite
(and probably in the docs - one of the documentation contributors will probably
reply pointing that out shortly :)

-- 
     Matt S Trout       Offering custom development, consultancy and support
  Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +