On 1/9/06, Scott Karns <[EMAIL PROTECTED]> wrote:
> Unfortunately, I now encounter the following warning and error:
>
> WARNING: Fetching sub-objects via more than one "one to many" relationship in
> a single query may produce many redundant rows, and the query may be slow. If
> you're sure you want to do this, you can silence this warning by using the
> "multi_many_ok" parameter at test-rdbo.pl line 15
I still don't think you should be getting that warning, so that looks
like a bug. I'll investigate further when I get a chance to run your
code. In the meantime, you can put back the "multi_many_ok => 1"
argument to silence it.
> Can't call method "fname" on unblessed reference at test-rdbo.pl line 21.
>
> I still seem to be missing something. As I'm iterating through all the
> classes, how can I get at the School::Person object referenced by the
> "instructors" relation?
Remember that each School::CourseSection has many School::Person
objects related through the "instructors" relationship. So for each
School::CourseSection, you need to iterate over the list of
"instructors" (School::Person objects), then ask each one for its
fname, lname, etc.
Note that this iteration over the list of "instructors" does not cause
any more database queries. The info has already been fetched and is
stored in the Perl objects. We're just iterating over them, not
running more SQL.
Example:
$class_iter =
School::CourseSection::Manager->get_classes_iterator(
require_objects => [ qw(instructors course room) ],
sort_by => [ qw(instructors.lname instructors.fname) ]);
while(my $class = $class_iter->next)
{
foreach my $person ($class->instructors)
{
print $person->fname, " ", $person->lname,
" teaches ", $class->course->short_cname,
", section ", $class->sect_num,
" in ", $class->room->short_rmname,
".\n";
}
}
> Also, from where did the first ORDER BY argument sneak into the generated
> query?
It's required in order to correctly pare down the redundant data
returned by the SQL query. (Run the query manually to see what the
data returned actually looks like.) The behavior is documented here:
http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Manager.pm#get_objects
Scroll down to the description of the sort_by parameter and you'll see
this explanation:
"If selecting sub-objects (via require_objects or with_objects) that are
related through "one to many" or "many to many" relationships, the first
condition in the sort order clause must be a column in the primary table
(t1). If this condition is not met, the list of primary key columns will be
added to the beginning of the sort order clause automatically."
If you think about it, it makes little sense to sort by a column in a
table related by a "... to many" relationship. For example, imagine
that the database has two people, Alice and Bob. Alice has 3 dogs
named Toby, Rex, and Bobo. Bob has 2 dogs named Spot and Fido. Now
you ask for a list of "all people in the database, plus their dogs,
sorted by dog name." Who comes first, Alice or Bob? How do you
decide? It's an arbitrary decision. More info is needed to make the
order deterministic.
Worse, think about *iterating* over such a collection. An iterator is
meant to only fetch a little at a time, rather than fetching all rows
at once (which can cost a lot of memory). But if you literally sorted
by dog name in the SQL query, Alice rows and Bob rows would be
inter-mixed. So you'd potentially have to fetch every single row in
order to do the first iteration (to be sure you got all rows related
to the first person)! That's bad :) Thus, the requirement to sort on
*some* value from the primary table, in order to keep all the rows for
each person bunched together.
It's somewhat surprising, perhaps, but at least it's documented :)
And I hope you now understand why it's necessary.
-John
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://ads.osdn.com/?ad_idv37&alloc_id865&op=click
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
