How to fit an ER model into Rails? (help an old procedural programmer)

rubyonrails-talk
1

Hi guys!

  I am an old procedural programmer learning Rails and object orientation. :slight_smile:

  I wish to create a little system to help my wife who is a teacher.

  The attached PDF file has the preliminary Entity-Relationship model.

  I am having a rough time trying to put this model into Rails... :frowning:

  Looking at the model I can think about the SQL code necessary. But I think Ruby and Rails is here to help and speed the development process. :wink:

  For some examples:

    Who teaches at school "x"?

SELECT people.name FROM people WHERE people.id IN   (SELECT team_has_teachers.person_id FROM team_has_teachers WHERE team_has_teachers.team_id IN     (SELECT teams.id AS team_id FROM teams WHERE teams.school_id =       (SELECT schools.id AS school_id FROM schools WHERE schools.name = 'x')))

    Who learns from teacher "x"?

SELECT people.name FROM people WHERE people.id IN   (SELECT team_has_students.person_id FROM team_has_students WHERE team_has_students.team_id IN     (SELECT team_has_teachers.team_id FROM team_has_teachers WHERE team_has_teachers.person_id =       (SELECT people.id FROM people WHERE people.name = 'x')))

    Who is in team "x"?

SELECT people.name FROM people WHERE people.id IN   (SELECT team_has_students.person_id FROM team_has_students WHERE team_has_students.team_id IN     (SELECT teams.id FROM teams WHERE teams.name = 'x'))

  Any help is welcome!

Best regards,   Mosoleu

Attachments: http://www.ruby-forum.com/attachment/8356/ER_model.pdf

2

Hi guys!

  I am an old procedural programmer learning Rails and object orientation. :slight_smile:

  I wish to create a little system to help my wife who is a teacher.

  The attached PDF file has the preliminary Entity-Relationship model.

  I am having a rough time trying to put this model into Rails... :frowning:

  Looking at the model I can think about the SQL code necessary. But I think Ruby and Rails is here to help and speed the development process.

It is rare to have to use SQL directly in Rails. If you have to then you are probably doing something wrong.

:wink:

  For some examples:

    Who teaches at school "x"?

@school.teachers

SELECT people.name FROM people WHERE people.id IN   (SELECT team_has_teachers.person_id FROM team_has_teachers WHERE team_has_teachers.team_id IN     (SELECT teams.id AS team_id FROM teams WHERE teams.school_id =       (SELECT schools.id AS school_id FROM schools WHERE schools.name = 'x')))

    Who learns from teacher "x"?

@teacher.pupils though in pra

SELECT people.name FROM people WHERE people.id IN   (SELECT team_has_students.person_id FROM team_has_students WHERE team_has_students.team_id IN     (SELECT team_has_teachers.team_id FROM team_has_teachers WHERE team_has_teachers.person_id =       (SELECT people.id FROM people WHERE people.name = 'x')))

    Who is in team "x"?

@team.members

SELECT people.name FROM people WHERE people.id IN   (SELECT team_has_students.person_id FROM team_has_students WHERE team_has_students.team_id IN     (SELECT teams.id FROM teams WHERE teams.name = 'x'))

  Any help is welcome!

The details are not correct as I have not looked at the ER diagram, and it is arguable whether you should start with such a diagram in the first place, but in order to understand the basics of what rails can do for you I suggest you work right through a good tutorial such as railstutorial.org, which is free to use online. Also look at the Rails Guides, particularly ActiveRecord Associations.

Colin

3

Hi Colin!

  Thanks for your tips. :slight_smile:

  With help of Rails Guides on ActiveRecord Associations I reach this point:

= Migrations =

class CreateSchools < ActiveRecord::Migration   def change     create_table :schools do |t|       t.string :name

      t.timestamps     end   end end

class CreateTeams < ActiveRecord::Migration   def change     create_table :teams do |t|       t.integer :school_id       t.string :name

      t.timestamps     end   end end

class CreatePeople < ActiveRecord::Migration   def change     create_table :people do |t|       t.string :name

      t.timestamps     end   end end

class CreateTeachers < ActiveRecord::Migration   def change     create_table :teachers, :id => false do |t|       t.integer :team_id       t.integer :person_id

    end     add_index :teachers, [:team_id, :person_id]   end end

class CreateStudents < ActiveRecord::Migration   def change     create_table :students, :id => false do |t|       t.integer :team_id       t.integer :person_id

    end     add_index :students, [:team_id, :person_id]   end end

= Models =

class School < ActiveRecord::Base   has_many :teams end

class Team < ActiveRecord::Base   belongs_to :school

  has_many :teachers   has_many :people, :through => :teachers

  has_many :students   has_many :people, :through => :students end

class Person < ActiveRecord::Base end

class Teacher < ActiveRecord::Base   belongs_to :team   belongs_to :person end

class Student < ActiveRecord::Base   belongs_to :team   belongs_to :person end

4

I would not do it like that. I would just have a people table that contains all the students and teachers, without the separate classes. Look up self referential associations to see how you can then have @person.students (which will be people) and @person.teacher. If you want the classes then you could use STI.

Colin

5

class Team < ActiveRecord::Base belongs_to :school has_many :teachers has_many :people, :through => :teachers has_many :students has_many :people, :through => :students end

I’m pretty sure your has_many :people won’t work, because you have two of them with different :through clauses. You would have to do something like:

has_many :teacher_people, :class_name => ‘People’, :through => :teachers

has_many :student_people, :class_name => ‘People’, :through => :students

But as you can see, the naming gets awkward pretty quickly, as well as your database. It would be better to do as Colin suggested, and simply have a People table that contains all the teachers and students. If you really want separate classes for the teachers and students, you could use single table inheritance, but it may not be necessary. Then you could simple have a team_members table that linked your teams to people.

class Team < ActiveRecord::Base belongs_to :school has_many :team_members

has_many :members, :through => :team_members

end

class Person < ActiveRecord::Base

scope :teachers, where(:person_type => ‘Teacher’)

scope :students, where(:person_type => ‘Student’)

end

Then, you could get members, teachers, and students for teams like this:

team.members

team.members.teachers

team.members.students

Jim