Beginner many-to-many problems

[prev] [thread] [next] [Date index for 2004/07/11]

I've just started playing around with many-to-many
relationships, in the hopes of converting an existing app to
Class::DBI. I'm glad to say that in the course of typing
the test cases for this e-mail, I figured out a few things
I was doing wrong and have eliminated the stupid questions
and the need to give tons of class-setup information.
However, there are a few basic things I'm still having
trouble with, and the docs and the wiki haven't given me
the answers.

Reducing things to their simplest form, I have three tables:
"citation", which contains words; "subject", which contains
subjects, and "citation_subject", which contains the id's of
entries in the other two tables. Each citation can have
multiple subjects; each subject has many citations. My setup
for the Citations class is:

package Citations;
use base 'Citations::DBI';
__PACKAGE__->set_up_table("citation");
__PACKAGE__->has_many(subject => [ CitationSubject => 'subject_id' ]);

Two things that I'd like to be able to do are:

First, retrieve all citations for a given subject (by $subject->name;
the equivalent SQL, given $subject as the subject name, is:

SELECT citation.* FROM citation, subject, citation_subject
WHERE subject.name='$subject'
AND citation_subject.subject_id=subject.id
AND citation_subject.citation_id=citation.id

).

In practice I'd almost certainly be wanting to use additional
ORDER BY clauses and perhaps other things like LIMITs or
things to grab citations by their initial letter, to limit
the result set; I don't know if I'd have to use some more
complicated thing like add_constructor with more raw SQL to
accomplish this.

Second, I'd like to add a citation with accompanying subjects,
preferably in one shot. Suppose, that is, I'd like to do something
like:

my $new_cite = Citations->create({ 
                word => $word,
		part_of_speech => $pos,
        });

What do I do so that a @subjects can be passed, and the appropriate
rows in the citation_subject table are created too? (Let's assume
that each element of @subjects does exist in the subject table.)

Thanks very much.

Jesse Sheidlower