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

Jess Robinson castaway at desert-island.me.uk
Wed Mar 11 07:53:07 GMT 2009


To answer the bits of your qyestion that nobody else did:

On Tue, 3 Feb 2009, Mon-Chaio Lo wrote:

> 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.

You're mixing up "relationship names" with "table names". Your table name 
is defined once, in a result class, changing it (why would you do that 
anyway, other than in dev?), does not force you to rename your 
relationships. Or, if you really do that, you would do it in a controlled 
fashion 
everywhere.

To not have this query many places in your code that would need changing, 
you can add a method to the "projects" resultset (see cookbook), which 
contains the query in one place, and just call it everywhere you need this 
list. Now changing it becomes a one-place change.

Something like:

   package Foo::ResultSet::Project;
   use base 'DBIx::Class::ResultSet';
   sub attributes {
 	return $self->search({ .. stuff from your search above }, { .. });
   }

> 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.

No, the only way to search across joined tables at the moment is to 
specify them in the query. DBIC can't yet read your mind as to which data 
you wanted ;)

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

As a further solution, you could also add another Result class, that has 
the table set to project_attributes again, but has a permanent "where 
active =1" on it, eg:

__PACKAGE__->resultset_attributes ({ where => { active => 1 }});

and use this one when you want the active attrs. If you dont like having 
the same result class with all its columns twice in your source.. use a 
base class ;)

Hope that helps.

> mcl
>

Jess



More information about the DBIx-Class mailing list