On 3/9/07, Jud <[EMAIL PROTECTED]> wrote:
> my $result = My::Project::Manager->get_projects
> (
> query => [
> 'employees.name' => 'jud',
> ],
> with_objects => [ 'employees' ],
> );
>
> I get the expected results (1 item in the test case). However, when I look at
> the list of employees for projects in my result set, they only contain the
> employee that was in my search( me ), instead of the full list of employees
> that are associated with the project.
The Manager's returning "all projects with employees where the
employee name is 'jud'". IOW, the query => ... Part is filtering out
all employees whose names are not "jud".
What you seem to want is "all projects with all their employees, where
at least one of the employees' names is 'jud'". Or, in more SQL-like
parlance, "all projects with all their employees where there exists at
least one employee whose name is 'jud'". Unfortunately, "where exists
..." translates to a subselect, which the Manager has no support for
short of providing a literal SQL subselect using the "clauses"
parameter.
> I could force the employee list to be reloaded by doing something like
>
> $_->employees( undef ) for @$result;
>
> but I'd hate to have to remember to do that for every query set,
> and I suspect that I'd need to do it for every relationship that I
> mention in my query terms.
If you don't mind having to query for the employee list for each
project separately, but just want to avoid having to refresh the
employees list manually as shown above, you can simply choose not to
fetch the employees at all in the Manager query:
my $projects =
My::Project::Manager->get_projects(
query => [ 'employees.name' => 'jud' ],
require_objects => [ 'employees' ],
fetch_only => [ 't1' ]);
(Note the use of require_objects instead of with_objects, which is
what I think you meant to type earlier.)
The full employee list for each project will be fetched on demand after that:
@employees = $projects->[0]->employees; # full list
A second option, if you know the number of matching projects is small,
is to fetch the list of project ids in one Manager query, then use
that list to form a second Manager query to get the rest of the
project info:
my @project_ids = map { $_->id }
@{ My::Project::Manager->get_projects(
select => 'id',
query => [ 'employees.name' => 'jud' ],
require_objects => [ 'employees' ]) };
my $projects =
My::Project::Manager->get_projects(
query => [ id => [EMAIL PROTECTED] ],
with_objects => [ 'employees' ]);
Again, that only works if the number of project ids is within the
limit of your database's "WHERE id IN (...)" support. (Most databases
seem to support somewhere between 50 and 500 items in an IN (...)
list.)
> FWIW, I don't see an obvious way to fix the SQL to get the required
> information in one query.
Speaking strictly in SQL, you could use a subselect to get everything
in one query. If you really want to do that in a Manager call, you
have to use literal SQL like this:
my $sql=<<"EOF";
EXISTS
(
SELECT * FROM project_employee_maps m, employees e
WHERE m.project_id = t1.id AND m.employee_id = e.id AND
e.name = 'jud'
)
EOF
my $projects =
My::Project::Manager->get_projects(
with_objects => [ 'employees' ],
clauses => [ $sql ]);
This is quite ugly, so it's probably wise to hide it behind a new
Manager method or a custom parameter to an existing Manager method
that you pre-process and convert into the appropriate literal clause
before calling through to the superclass Manager method.
-John
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
