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/
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
