[CDBI] newbie: how can I put a placeholder on a query of a has_many relationship?

Victor Churchill victorchurchill at gmail.com
Thu Jun 7 20:52:46 BST 2007


I am just getting started with C::DBI ...
I feel that this must be very simple but I've failed to find a way to do it.

SImply, I have a table of 'Readings' and a table of 'Meters'. Rows in
'Readings' have a foreign key with their meter_id.

(For legacy reasons the Meters table is actually called Allocated_Meters).

#############
package QG::Reading;
#############
use strict;
use base 'QG::QGDB';

QG::Reading->table('readings');
QG::Reading->columns(Primary => qw/rea_id/);
QG::Reading->columns(Essential => qw/
rea_reading rea_reading_date rea_reading_type_code rea_alm_id
/);
QG::Reading->has_a(rea_alm_id => 'QG::ALM');
1;

###########
package QG::ALM;
###########

use strict;
use base 'QG::QGDB';

QG::ALM->table('allocated_meters');

QG::ALM->columns(Primary   => qw/alm_id/);
QG::ALM->columns(Essential => qw/alm_mtr_id alm_sp_id alm_lp_id
alm_channel alm_channel_type/);

QG::ALM->has_a(alm_mtr_id => 'QG::Meter');
QG::ALM->has_a(alm_sp_id => 'QG::SupplyPoint');

QG::ALM->has_many(readings => 'QG::Reading');

1;

(I've left out some 'Others' columns.)

For a given ALM I'd like to query the 'readings' relationship:
    my $iterator = $ALM->readings ;
The above works but gives me /all/ the readings, ever. I'd like to
query the readings between two dates.

Now I get that I can query the readings class itself , e.g.
my $extraSQL = qq(REA_READING_DATE >= \'$to\' - ? AND
                  REA_READING_DATE <= \'$to\' );

QG::Reading->add_constructor(rdgs_past_days => $extraSQL);

but that's a constructor on the Readings class not the ALM class with
the 'readings' "method".

So far I can't see how to add a where clause to the 'readings' call
defined in the has_many statement. From here it looks as of, to do
what I want, I'd have to do a direct query on the Readings class, but
then I'd need to add an additional placeholder for the rea_alm_id
attribute, which kind of defeats the object of defining the has_many
and is getting close to good ole DBI.

Some kind soul tell me what I'm missing?

Thanks!

victor



More information about the ClassDBI mailing list