Is might_have the most efficient way to do this?

[prev] [thread] [next] [Date index for 2004/10/20]

Given the following table structure:

CREATE TABLE foo(
	control NUMBER PRIMARY KEY,
	foo VARCHAR2(20),
	has_bar NUMBER(1),
	has_baz NUMBER(1)
);

CREATE TABLE bar(
	control NUMBER PRIMARY KEY,
	bar VARCHAR2(20),
);

CREATE TABLE baz(
	control NUMBER PRIMARY KEY,
	baz VARCHAR2(20),
);

I'm trying to set up CDBI to only fetch from tables bar and baz if the value
of foo.has_bar or foo.has_baz
is 1.  Is there an idiom for doing this?  If I use might_have:

Bar->has_a( control => 'Foo' );
Baz->has_a( control => 'Foo' );
Foo->might_have( bar => 'Bar' );
Foo->might_have( baz => 'Baz' );

Then DBI still does a search against the subordinate tables:

 T  <- FETCH('Active')= 1 at DBI.pm line 316
 T  <- ping= 1 at DBI.pm line 316
 T  <- prepare_cached('SELECT control
FROM   foo
WHERE  control = ?
')= ( DBIx::ContextualFetch::st=HASH(0x810634c) ) [1 items] at DBI.pm line
391
 T  <- FETCH('Active')= '' at DBI.pm line 1124
 T  <- FETCH('Taint')= 1 at ContextualFetch.pm line 49
    <- STORE('Taint' 0)= 1 at ContextualFetch.pm line 50
    <- execute(24032)= '0E0' at ContextualFetch.pm line 51
 T  <- STORE('Taint' 1)= 1 at ContextualFetch.pm line 52
 T  <- FETCH('NAME_lc')= [ 'control' ] at DBI.pm line 1125
 T  <- bind_columns(SCALAR(0x847b378))= 1 at DBI.pm line 1125
 T  <- fetchrow_arrayref= [ '24032' ] row1 at ContextualFetch.pm line 59
 T  <- fetchrow_arrayref= undef row1 at ContextualFetch.pm line 59
 T  <- FETCH('Active')= 1 at DBI.pm line 316
 T  <- ping= 1 at DBI.pm line 316
 T  <- prepare_cached('SELECT control
FROM   bar
WHERE  control = ?
')= ( DBIx::ContextualFetch::st=HASH(0x847b7e0) ) [1 items] at DBI.pm line
391
 T  <- FETCH('Active')= '' at DBI.pm line 1124
 T  <- FETCH('Taint')= 1 at ContextualFetch.pm line 49
    <- STORE('Taint' 0)= 1 at ContextualFetch.pm line 50
    <- execute('24032')= '0E0' at ContextualFetch.pm line 51
 T  <- STORE('Taint' 1)= 1 at ContextualFetch.pm line 52
 T  <- FETCH('NAME_lc')= [ 'control' ] at DBI.pm line 1125
 T  <- bind_columns(SCALAR(0x847b9e4))= 1 at DBI.pm line 1125
 T  <- fetchrow_arrayref= [ '24032' ] row1 at ContextualFetch.pm line 59
 T  <- fetchrow_arrayref= undef row1 at ContextualFetch.pm line 59

I have tried using has_a and specifying my own inflate and deflate subs, but
that gets messy very
quickly.

Any ideas on how best to achieve this?

Thanks.

        -- 
        j.
 
James FitzGibbon
Systems Developer, Primus Telecommunications Canada Inc.
416.644.6111

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.776 / Virus Database: 523 - Release Date: 10/12/2004
 



----------------------------------------------------------------------------
This electronic message contains information from Primus Telecommunications
Canada Inc. ("PRIMUS") , which may be legally privileged and confidential.
The information is intended to be for the use of the individual(s) or entity
named above. If you are not the intended recipient, be aware that any
disclosure, copying, distribution or use of the contents of this information
is prohibited. If you have received this electronic message in error, please
notify us by telephone or e-mail (to the number or address above)
immediately. Any views, opinions or advice expressed in this electronic
message are not necessarily the views, opinions or advice of PRIMUS.
It is the responsibility of the recipient to ensure that
any attachments are virus free and PRIMUS bears no responsibility
for any loss or damage arising in any way from the use
thereof.The term "PRIMUS" includes its affiliates.
----------------------------------------------------------------------------
Pour la version en français de ce message, veuillez voir
 http://www.primustel.ca/fr/legal/cs.htm
----------------------------------------------------------------------------