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

[David Ihnen]

Mon-Chaio Lo wrote:
> Hello,
>
>   
Mon-Chiao!  Its a pleasure to see you online.  Much has happened since I 
interviewed with you at Cardomain and was almost (but not quite) hired.  
(just as well with their downsize)  I see you too have skedaddled from 
Seattle - enjoying the winter sunshine down california?  Flagstaff is 
treating me tolerably.  Anyway.   Your queries.

> 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. 
But not which ones you're using at the moment for this query.  And the 
fact that your relationships don't define your restrictions is a problem 
in this regard.
>  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.
>
>   
Hm.  What you would do in manual sql amount to defining a new relation, 
one called 'active_attributes' maybe.  That has a compound join 
condition (aid = aid and active = 1)

FROM projects p
JOIN project_attributes pa ON (pa.pid = p.pid and pa.active = 1)

Which I would *expect* to define as

__PACKAGE__->has_many('active_attributes', 
'DB::Schema::project_attribute', { 'foreign.pid' => 'self.pid', 
self.active => 1 });

Allowing

my $valueset = 
$schema->resultset('Projects')->active_attributes->attribute_values->search(undef, 
{ prefetch => { project_attribute => project });

Each of which entries you could call

$attribute_value->attribute->project

to get your project as well.

But that just isn't how that works.  :( 

There seems to be a bug in DBIx::Class where you can't do that no matter 
how you tangle - this augmentation would help me in my queries too, 
greatly!  I puzzled at it for awhile, but there seemed to be something 
odd about how the join rels are defined that I couldn't quite grok.  
I'll revisit in awhile and see if I make any progress.  Maybe you'll be 
able to see it...

But I would, until I can define custom compound relations, just collect 
the whole data set in one query.  (forgive my barewords, they shoudl be 
quoted)

my @rows = $schema->resultset('Project')->search
  ( { project.status => 'completed'
    , 'project_attributes.active' => 1
    }
  ,  { prefetch => { project_atributes => { attributes, attribute_values } }
     , join => { project_atributes => [ attributes , attribute_values ] }
  );

Which is terribly denormalized because you duplicate the join pattern in 
the prefetch clause (I think this is another bug.  patch?), but does let 
you do fun things like

print "found project " . $_->name . " attribute " . 
$_->project_attributes->attribute_values->name . "\n" foreach (@rows);


And though you had to mention what direction of joins to take (there can 
be many in a complex schema!) to use for the query, you did NOT have to 
define which columns are utilized for each of the joins - merely define 
that they're joined and what you wanted in your resultset.

I hope we can figure out how to do complex join relations, that would be 
awesome.

Helpfully,

David Ihnen
Programmer
Norchem Laboratories
http://www.norchemlab.com/