I'm a newbie to ORM and DBIx::Class and I think my question will be bread and butter for you guys but I can't figure it out so don't flame me!!<br> <br> I went through the examples provided in the docs and I found them no problem but then I tried one of my own examples .....<br> <br> The examples given in the docs have the relationships of <br> <br> one artist can have many cds<br> one cd belongs to one artist<br> one cd can have many tracks<br> one track belongs to one cd<br> <br> My own example has the relationships of, say, <br> one student can attend many schools (attendee)<br> one school can have many students (attendee)<br> <br> I have an intermediate table (school_attendees) that stores the student.id against the school.id<br> <br> Schema<br> <br> CREATE TABLE students (<br> id INTEGER NOT NULL PRIMARY KEY,<br> first_name TEXT NOT NULL,<br> last_name TEXT NOT NULL,<br> age INTEGER NOT NULL<br> );<br> <br> CREATE TABLE school_attendees
(<br> id INTEGER NOT NULL PRIMARY KEY,<br> attendee_id INTEGER NOT NULL REFERENCES students(id),<br> school_id INTEGER NOT NULL REFERENCES schools(id)<br> );<br> <br> <br> CREATE TABLE schools (<br> id INTEGER NOT NULL PRIMARY KEY,<br> name TEXT NOT NULL,<br> address_1 TEXT NOT NULL,<br> address_2 TEXT,<br> phone_number TEXT NOT NULL<br> );<br> <br> With the following classes ....<br> <br> package DB::MyTestSchema::Student;<br> <br> use strict;<br> use base qw|DBIx::Class|;<br> my $pkg = __PACKAGE__; <br> <br> $pkg->load_components( qw| PK::Auto Core | );<br> $pkg->table('students');<br> $pkg->add_columns(qw|id first_name last_name age|);<br> $pkg->set_primary_key( qw| id | );<br> $pkg->has_many( school_attendees => 'DB::MyTestSchema::SchoolAttendee', 'id' );<br> 1;<br> <br> package DB::MyTestSchema::School;<br> <br> use strict;<br> use base qw| DBIx::Class |;<br> <br> my $pgk = __PACKAGE__;<br> <br>
$pgk->load_components( qw|Core PK::Auto| );<br> $pgk->table('schools');<br> $pgk->add_columns( qw|id name address_1 address_2 phone_number| );<br> $pgk->set_primary_key( 'id' );<br> $pgk->has_many( school_attendees => 'DB::MyTestSchema::SchoolAttendee', 'id' );<br> 1;<br> <br> <br> package DB::MyTestSchema::SchoolAttendee;<br> <br> use strict;<br> use base qw| DBIx::Class |;<br> <br> my $pgk = __PACKAGE__;<br> <br> $pgk->load_components( qw|PK::Auto Core| );<br> $pgk->table('school_attendees');<br> $pgk->add_columns( qw|id attendee_id school_id| );<br> $pgk->set_primary_key( 'id' );<br> $pgk->belongs_to( schools => 'DB::MyTestSchema::School', 'id' );<br> $pgk->belongs_to( student => 'DB::MyTestSchema::Person', 'id' );<br> 1;<br> <br> Now, I want to retrieve the names of the school attendees when the name of the school is supplied.<br> <br> Where ever I put my code, I always end up with a 'DBIx::Class::ResultSet::next(): No
relationship blah blah'<br> <br> The SQL I'm trying to make is ...<br> <br> SELECT students.* <br> FROM students, school_attendees, schools<br> WHERE schools.name = 'My High school'<br> AND schools.id = school_attendees.school_id<br> AND student.id = school_attendees.attendee_id<br> <br> Am I right with my gut feeling that I should be calling the package that deals with SchoolAttendee? for example DB::AccessSchema::SchoolAttendee->get_school_attendees( $schema, 'My High School' );<br> <br> or should it go in DB::AccessSchema::Student?<br> <br> I think DB::AccessSchema::SchoolAttendee is the right place as this is where the common link is, I just don't know how to provide the args to create the required SQL.<br> <br> I'm loathed to provide the code for these two classes as obviously the things I'm doing are wrong and I've tried lots of things :(<br> <br> I hope my set-up is correct.<br> <br> Any advice
would be great as I need this to unleash the shackles :D<br> <br> Please don't flame, I'm made out of paper.<br> <br> -NOA<br> <br> <br> <p> 
<hr size=1>
Try the <a href="http://us.rd.yahoo.com/mail/uk/taglines/default/nowyoucan/wall_st_2/*http://us.rd.yahoo.com/evt=40565/*http://uk.docs.yahoo.com/nowyoucan.html">all-new Yahoo! Mail</a> . "The New Version is radically easier to use" � The Wall Street Journal