[Dbix-class] Newbie to ORM and DBIx (pls help)

Anthony Gardner cyclewood_ltd at yahoo.co.uk
Tue Aug 1 18:14:21 CEST 2006


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!!
 
 I went through the examples provided in the docs and I found them no problem but then I tried one of my own examples .....
 
 The examples given in the docs have the relationships of 
 
 one artist can have many cds
 one cd belongs to one artist
 one cd can have many tracks
 one track belongs to one cd
 
 My own example has the relationships of, say, 
 one student can attend many schools (attendee)
 one school can have many students (attendee)
 
 I have an intermediate table (school_attendees) that stores the student.id against the school.id
 
 Schema
 
 CREATE TABLE students (
   id INTEGER NOT NULL PRIMARY KEY,
   first_name TEXT NOT NULL,
   last_name TEXT NOT NULL,
   age INTEGER NOT NULL
 );
 
 CREATE TABLE school_attendees (
   id INTEGER NOT NULL PRIMARY KEY,
   attendee_id INTEGER NOT NULL REFERENCES students(id),
   school_id INTEGER NOT NULL REFERENCES schools(id)
 );
 
 
 CREATE TABLE schools (
   id INTEGER NOT NULL PRIMARY KEY,
   name TEXT NOT NULL,
   address_1 TEXT NOT NULL,
   address_2 TEXT,
   phone_number TEXT NOT NULL
 );
 
 With the following classes ....
 
 package DB::MyTestSchema::Student;
 
 use strict;
 use base qw|DBIx::Class|;
 my $pkg = __PACKAGE__; 
 
 $pkg->load_components( qw| PK::Auto Core | );
 $pkg->table('students');
 $pkg->add_columns(qw|id first_name last_name age|);
 $pkg->set_primary_key( qw| id | );
 $pkg->has_many( school_attendees => 'DB::MyTestSchema::SchoolAttendee', 'id' );
 1;
 
 package DB::MyTestSchema::School;
 
 use strict;
 use base qw| DBIx::Class |;
 
 my $pgk = __PACKAGE__;
 
 $pgk->load_components( qw|Core PK::Auto| );
 $pgk->table('schools');
 $pgk->add_columns( qw|id name address_1 address_2 phone_number| );
 $pgk->set_primary_key( 'id' );
 $pgk->has_many( school_attendees => 'DB::MyTestSchema::SchoolAttendee', 'id' );
 1;
 
 
 package DB::MyTestSchema::SchoolAttendee;
 
 use strict;
 use base qw| DBIx::Class |;
 
 my $pgk = __PACKAGE__;
 
 $pgk->load_components( qw|PK::Auto Core| );
 $pgk->table('school_attendees');
 $pgk->add_columns( qw|id attendee_id school_id| );
 $pgk->set_primary_key( 'id' );
 $pgk->belongs_to( schools => 'DB::MyTestSchema::School', 'id' );
 $pgk->belongs_to( student  => 'DB::MyTestSchema::Person', 'id' );
 1;
 
 Now, I want to retrieve the names of the school attendees when the name of the school is supplied.
 
 Where ever I put my code, I always end up with a 'DBIx::Class::ResultSet::next(): No relationship blah blah'
 
 The SQL I'm trying to make is ...
 
 SELECT students.* 
   FROM students, school_attendees, schools
  WHERE schools.name = 'My High school'
    AND schools.id   = school_attendees.school_id
    AND student.id    = school_attendees.attendee_id
 
 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' );
 
 or should it go in DB::AccessSchema::Student?
 
 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.
 
 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 :(
 
 I hope my set-up is correct.
 
 Any advice would be great as I need this to unleash the shackles :D
 
 Please don't flame, I'm made out of paper.
 
 -NOA
 
 
 
 		
---------------------------------
 Try the all-new Yahoo! Mail . "The New Version is radically easier to use" – The Wall Street Journal
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060801/d485a5a7/attachment.htm 


More information about the Dbix-class mailing list