[Dbix-class] Get a list of parent objects based on child object properties (possible n00bish question)

[Mon-Chaio Lo]

Hello,

I couldn't find the answer to this question in the documentation, so I
turn to the community.  I have the following example tables:

projects
--------
pid  name    status
 1   proj1   completed
 2   proj2   pending
 3   proj3   completed

project_attributes
-------------------
pid   aid   vid   active
 1     1     1      1
 1     1     2      1
 2     1     3      1
 3     1     4      1
 3     2     6      0

attributes
----------
aid   name     created_date
 1    attr1    Jan 1
 2    attr2    Jan 2

attribute_values
----------------
aid  vid   name
 1    1    value_for_attr1
 1    2    another_value_for_attr_1
 1    3    ...
 2    6    ...

I'm trying to find all completed projects and their active attributes.
So I have the following code:

my $proj_rs = $schema->resultset('Project')->search( { status =>
'completed' } ); my $rs = $proj_rs->search_related(
'project_attributes', { active => 1 }, { prefetch => [qw(attributes
values)] } ); while ( my $proj_attribute = $rs->next() ) {
    ...
}

Now I have a list of project_attribute objects.  What I've accomplished
is to get a list of project_attribute objects whose parent project is
completed.  What I'd like to do instead is get a list project objects,
but only those projects that are completed have at least one active
child attribute.  I know I can do that with code that looks like:

my $proj_rs = $schema->resultset('Project')->search( {
project_attributes.active => 1 }, { join => ['project_attributes'] } );

Here's the thing.  I've already specified the table relationships in the
schema objects.  Now I'm specifying them again in the search.  So now if
I rename the project_attributes table, I need to change things in
multiple places, namely the Schema representing that table, as well as
all the explicit joins I have in my search() code.

Is there a way around this?  Simply stated, I'd like to get back a list
of parent objects whose child objects exhibit a specific property,
without resorting to explicit join statement arguments to the search
method.  Oh, and the first code chunk only calls one SQL statement, so
it'd be nice to minimize the number of calls to the DB as well.

Thanks in advance for your assistance; I'd be happy to clarify my
problem if anyone has questions.

mcl

--
the rubicon project

MON-CHAIO LO| ENGINEERING LEAD

*** P 310 207 0272 | x187
*** F 310  207 0528
*** M 206 718 6205

1925 S. BUNDY  DRIVE
LOS ANGELES, CALIFORNIA 90025

WWW.RUBICONPROJECT.COM


"COMPANY OF THE YEAR" ALWAYSON ONMEDIA 2009
#24 ON FAST COMPANY FAST 50 READER FAVORITES
2008  ALWAYSON 250 GLOBAL WINNER
2008 ALWAYSON ONMEDIA 100 WINNER
TWIISTUP3  BEST IN SHOW
PRICEWATERHOUSECOOPERS - ENTRETECH  BEST STARTUP AMERICAN BUSINESS
AWARDS - STEVIES  '08 FINALIST BEST NEW COMPANY AMERICAN BUSINESS
AWARDS - STEVIES '08 FINALIST MOST INNOVATIVE COMPANY AMERICAN BUSINESS
AWARDS - STEVIES '08 FINALIST NEW PRODUCT OR SERVICE -  SERVICES

P          Please consider the environment before printing this e-mail