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
_______________________________________________
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]
