Hey John-
Thanks for the quick and detailed reply. See my comments inline:
On 09/03/07 10:30 -0500, John Siracusa <[EMAIL PROTECTED]> wrote:
> 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.
Actually, what I want is projects that match specific criteria
defined in the query clause, and I want efficient access to related
objects. :)
I would expect that regardless of how the query was constructed, any
results (projects) returned would be accurate, meaning that they would
reflect the the state of things in the database at the time that the
query was executed.
It is definitely surprising behavior that $project->employees would not
reflect the state of the database, and it would be great if it was (at
least) clearly documented. I'm thinking about how to word it, but an
example seems best.
It seems like my use case is the common case. However, I'm willing to
accept that I'm just supremely biased :)
So if I understand correctly, adding a tags table (project_tag_maps)
my ($project) = My::Project::Manager->get_projects(
query => [ 'employees.name' => 'jud' ],
require_objects => [ 'employees', 'tags' ]
);
$project->employees( ) - doesn't match DB
$project->tags( ) - matches DB
query( ) really means limit not only the projects returned, but also
filter any related objects.
> 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' ]);
Yes, I'd like to avoid doing all these individual lookups
> (Note the use of require_objects instead of with_objects, which is
> what I think you meant to type earlier.)
yes :)
> 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:
...
> 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.)
Score one victory for mysql. 100,000 items in the IN clause with no problems
and very fast.
> 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 ]);
Thanks for this. I'll give it a try.
-------------------------------------------------------------------------
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
