John,
Thanks for the quick reply!
I think I now have a better understanding of the usage
of mapping tables. I made the changes you suggested,
so my test program now reads as follows:
#!/usr/bin/perl
use strict;
use lib "/home/scott/projects/da-db/lib";
use School::CourseSection;
use School::Course;
use School::Person;
use School::Room;
use School::ClassInstructor;
local $Rose::DB::Object::Debug = 1;
local $Rose::DB::Object::Manager::Debug = 1;
my $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) {
print $class->instructors->fname, " ",
$class->instructors->lname,
" teaches ", $class->course->short_cname,
", section ", $class->sect_num,
" in ", $class->room->short_rmname,
".\n";
}
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
SELECT
t1.cid AS t1_cid,
t1.clid AS t1_clid,
t1.rmid AS t1_rmid,
t1.sect_num AS t1_sect_num,
t3.dob AS t3_dob,
t3.fname AS t3_fname,
t3.lname AS t3_lname,
t3.miname AS t3_miname,
t3.pid AS t3_pid,
t3.sex AS t3_sex,
t4.cid AS t4_cid,
t4.cnum AS t4_cnum,
t4.long_cname AS t4_long_cname,
t4.short_cname AS t4_short_cname,
t5.long_rmname AS t5_long_rmname,
t5.rmid AS t5_rmid,
t5.short_rmname AS t5_short_rmname
FROM
course_section t1,
class_instructor t2,
person t3,
course t4,
room t5
WHERE
t2.clid = t1.clid AND
t2.pid = t3.pid AND
t1.cid = t4.cid AND
t1.rmid = t5.rmid
ORDER BY t1.clid, t3.lname, t3.fname ()
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?
Also, from where did the first ORDER BY argument sneak
into the generated query?
My table creation sql:
CREATE TABLE "course" (
"cid" SERIAL NOT NULL,
"cnum" smallint NOT NULL,
"short_cname" character varying(10) NOT
NULL,
"long_cname" character varying(128),
PRIMARY KEY ("cid")
);
CREATE TABLE "room" (
"rmid" SERIAL NOT NULL,
"short_rmname" character varying(8) NOT NULL,
"long_rmname" character varying(32),
PRIMARY KEY ("rmid")
);
CREATE TABLE "person" (
"pid" SERIAL NOT NULL,
"lname" character varying(32) NOT
NULL,
"fname" character varying(32) NOT
NULL,
"miname" character varying(32),
"sex" character(1)
CHECK (sex = 'F' OR sex =
'M'),
"dob" date,
"picture" OID,
PRIMARY KEY ("pid")
);
CREATE TABLE "course_section" (
"clid" SERIAL NOT NULL,
"cid" integer
REFERENCES course
ON DELETE RESTRICT
ON UPDATE CASCADE,
"sect_num" smallint NOT NULL,
"rmid" integer
REFERENCES room
ON DELETE RESTRICT
ON UPDATE CASCADE,
PRIMARY KEY ("clid")
);
CREATE TABLE "class_instructor" (
"clid" integer NOT NULL
REFERENCES course_section
ON DELETE CASCADE
ON UPDATE CASCADE,
"pid" integer NOT NULL
REFERENCES person
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY ("clid", "pid")
);
That brings up a topic for another thread -- the
"picture" column in the person table.
Thanks,
Scott Karns
--- John Siracusa <[EMAIL PROTECTED]> wrote:
>...
> That should work. If it doesn't, please post the
> CREATE TABLE
> statements for all of the tables involved and I'll
> see if I can debug
> it. The solution above is just off the top of my
> head.
>
> Also, I don't think you need the "multi_many_ok"
> parameter since I
> only see one "... to many" relationship in the
> require_objects list:
> "instructors." The other two, "course" and "room",
> are foreign keys
> which are always "... to one". The multi_many_ok
> parameter won't hurt
> anything, of course, but it's not strictly needed
> here.
>
> Finally, you will need to qualify the sort_by
> values. Unqualified
> values are assumed to belong to the primary table
> ("course_section" in
> this case). You can qualify sort_by column names by
> prepending table
> names, tN table aliases, or relationship names.
> Relationship names
> are probably the most clean. I also recommend an
> arrayref rather than
> a comma-separated string. Some examples (also
> untested, sorry)
>
> # table names
> sort_by => [ 'person.lname', 'person.fname' ],
>
> # tN table aliases
> sort_by => [ 't3.lname', 't3.fname' ],
>
> # Relationship name
> sort_by => [ 'instructors.lname',
> 'instructors.fname' ],
>
> So, the final call:
>
> $class_iter =
>
>
School::CourseSection::Manager->get_classes_iterator(
> require_objects => [ qw(instructors course
> room) ],
> sort_by => [ 'instructors.lname',
> 'instructors.fname' ]);
>
> Let me know how close I came to producing working
> code in my examples :)
>
> -John
>
__________________________________________
Yahoo! DSL Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com
-------------------------------------------------------
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_id=7637&alloc_id=16865&op=click
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
