Topic: Getting info from one table, saving to another

This is probably a simple problem, but I have not found the answer after lots of trying.  Of course, new as I am, I suspect I saw the answer without recognizing it.

I have two tables, one of which has info on a group of people, and another one that is empty for now.  I would like to make a form that would fill in several values from the first table (to save users the typing and the errors) and then take that info, plus whatever new info they input, and save it to the second table.  That second table would then be searchable for records by several methods.

I am assuming I need to use a "collection_select" to get this started, but how do I go about filling the other boxes with data from the first table?  Then, is there a trick to getting the whole thing to save to the second table?

Sorry for the simple problem, but pointers to an answer would be much appreciated.

Re: Getting info from one table, saving to another

This will result in duplicate data in the database which usually isn't a good thing. Normally, you would make an association between the two tables so one record in one table links to a record in the other table through a foreign key.

If you can provide more information about the situation I may be able to provide some code.

Railscasts - Free Ruby on Rails Screencasts

Re: Getting info from one table, saving to another

ryanb wrote:

This will result in duplicate data in the database which usually isn't a good thing. Normally, you would make an association between the two tables so one record in one table links to a record in the other table through a foreign key.

This may be a special case.  We'll see, I guess (and hope).

ryanb wrote:

If you can provide more information about the situation I may be able to provide some code.

Certainly, Sir!  And thanks a bunch!

This is an app for tracking interventions made in the case of students in a school w/ population ~1200.  The students already are in one table along with data such as ID number, grade, counselor's name, and administrator's name.

The other table is meant to store this info plus the additional info from teachers on students' behavior, the interventions they have done, as well as the need for further interventions as necessary.  I would like this table to be searchable by student name, teacher name, counselor name, and administrator name, so that all of these parties (except students) can keep track of what they have done and what they still need to do.

If I just let teachers input data. I anticipate a lot of variation and error (nicknames, wrong ID numbers, etc.)  They are also very stressed and time-deprived, so I have to make this dead easy for them. 

I have a has-many relationship between students and interventions (students have many interventions) with a "student_id" field for the relationship.  Do I need other kinds of relationships? 

Also, I anticipate using a "collection_select" of the student table, and I figure I need to make an array, but how do I get those known values to display in the input boxes of the form?

Under the circumstances, is what I have in mind too non-standard?  (Sorry for being a bo-bo, but here we are.)

Thanks!

MT Byers

Last edited by mtbyers (2006-11-09 11:54:33)

Re: Getting info from one table, saving to another

mtbyers wrote:

Do I need other kinds of relationships?

Yeah, you will need more relationships. However, it depends how extravagant you want to make it. You mentioned searching by teacher, administrator, and counselor name. I'm not certain how these all relate to one another, so I'm just going to stick to teacher.

I suggest creating a Teacher model where Teacher has_many :students - if a teacher has all students you may not want to bother with this association. Next you need to create a table/model to hold the user's behavior reports. I'm just going to call this report, but you should probably rename it as you have a better understanding of the domain. This report can belong to both teacher and student (it has a teacher_id and student_id). Here is the end result:

class Teacher < ActiveRecord::Base
  has_many :students
  has_many :reports
end

class Report < ActiveRecord::Base
  belongs_to :teacher
  belongs_to :student
end

class Student
  belongs_to :teacher
  has_many :reports
end


Now you need to make a form for creating the reports. I'm assuming you let the teachers login to the system somehow, so you already have the teacher_id value. You just need to provide a way to select the student value:

# in reports_controller.rb or wherever
def new
  @teacher = Teacher.find(session[:teacher_id]) # you probably want to handle this elsewhere/differently
  @report = Report.new
end

# in new.rhtml

<%= start_form_tag :action => 'create' %>
  ...other report fields...
  <p>Student: <%= collection_select :report, :student_id, @teacher.students, :id, :name %></p>
<%= end_form_tag %>

# back in controller
def create
  @report = Report.new(params[:reports])
  @report.teacher = Teacher.find(session[:teacher_id]) # again, the teacher will probably be fetched some other way
  if @report.save
    # ...
  else
    #...
  end
end


This will only show the teacher's students in the select menu. If you wanted to show all students in that menu you would do:

collection_select :report, :student_id, Student.find(:all), :id, :name

You would probably want to set that to a @students instance variable in the controller to keep the finds out of view.

ryanb wrote:

Under the circumstances, is what I have in mind too non-standard?

Nope, seems pretty normal to me. Unless of course I'm way off base. smile

The searching might be a little tricky, but I suggest getting this working first.

Railscasts - Free Ruby on Rails Screencasts

Re: Getting info from one table, saving to another

ryanb wrote:

I suggest creating a Teacher model...

What a thorough answer!  This is way better than I had any reason to expect.  Thanks for the time and effort you obviously put into this. 

So, the problem is not so simple after all.  I'm wondering, once all this is done, should I expect to be able to use "collection_select" and have the first part of the form (the part with student data) fill with the data on any particular student (from the "students" table) in the form?  (I think I see what you mean by keeping it "out of view.")

That seems too magical, somehow.  I'm thinking I have to do something with hashes or arrays to get that going.  Is all of that really done for me?  (Yep.  Total n-word, here.  That's "n00b," by BTW.)

MT

Last edited by mtbyers (2006-11-09 20:54:18)

Re: Getting info from one table, saving to another

mtbyers wrote:

I'm wondering, once all this is done, should I expect to be able to use "collection_select" and have the first part of the form (the part with student data) fill with the data on any particular student (from the "students" table) in the form?  (I think I see what you mean by keeping it "out of view.")

You shouldn't need to fill in any part of the form with the data in the students table because you can fetch it anytime you want once you have the student selected. Unless of course you want to give the teacher the option to override a certain part of the student's data the same time they make a report - this is much more complicated.

mtbyers wrote:

That seems too magical, somehow.  I'm thinking I have to do something with hashes or arrays to get that going.  Is all of that really done for me?  (Yep.  Total n-word, here.  That's "n00b," by BTW.)

What I posted will get you about 90% there. The Rails form helpers and such really helps make this simple - you don't have to worry about juggling each of the attributes and saving them one-by-one. Rails handles this all for you. Magical is a good word for it.

Railscasts - Free Ruby on Rails Screencasts

Re: Getting info from one table, saving to another

ryanb wrote:

What I posted will get you about 90% there....

Thanks again.  I can't say I understand all you've said, but that's not your fault.  Your answer gives me direction, and that's what I needed.  Very much appreciated!

To clarify one point, it seems that if I want to be able to search by some known data point, that data point needs a table to be able to do it.  'Bout right?

(Thinking out loud here:)
My students table does not include data matching the students to their teachers.  We are on semesters here, so that data would change too often (and the info is hard to get, anyway).  Each student's principal and counselor is set for the year by grade level, so that's why I was able to add that. 

The way I conceive it, the teachers would make the reports, and the counselors and principals would check them, but only those reports that fall into their area of responsibility.  Other teachers would want to search by student name to see if their "problem child" is having similar problems in his/her other classes. 

Sounds like a lot of tables and relationships, huh?  But please, don't worry about me anymore (unless the problem intrigues you).  I have a lot of ground to cover, it seems.  I also need to acknowledge that this may be beyond me right now, too.

Thanks!

Re: Getting info from one table, saving to another

mtbyers wrote:

To clarify one point, it seems that if I want to be able to search by some known data point, that data point needs a table to be able to do it.  'Bout right?

Yeah, the data needs to be somewhere for it to be searched. However, it doesn't all need to be in one table, you can search multiple tables through their relationships.

mtbyers wrote:

My students table does not include data matching the students to their teachers.  We are on semesters here, so that data would change too often (and the info is hard to get, anyway).

That's fine if there's no direct relationship between student and teacher. What you would change in the code I gave above is list all students in the collection_select instead of @teacher.students - that's all I was using that relationship for.

mtbyers wrote:

Other teachers would want to search by student name to see if their "problem child" is having similar problems in his/her other classes.

The beauty of relationships in Rails is that they are easily accessible. For example, let's say you want to have a details page for a given student. You can easily loop through that student's reports and display them on  the same page along with the teacher's information who submitted the report. For example:

# in students controller
def show
  @student = Student.find(params[:id])
end

# in show.rhtml
<h1>Student <%= @student.name %></h1>
<h2>Reports</h2>
<% for report in @student.reporst %>
  Content: <%= report.content %>
  Submitted by Teacher: <%= report.teacher.name %>
<% end %>


mtbyers wrote:

I have a lot of ground to cover, it seems.  I also need to acknowledge that this may be beyond me right now, too.

If this is your first Rails project, then it may be a bit of a leap. It takes a while to "get" model relationships, but once you do you can make as many as you want without too much trouble. Rails makes it really easy to define and fetch relationships, but it just takes some practice.

Searching across relationships can be a bit trickier. I recommend implementing the search after you are done with the other areas.

Railscasts - Free Ruby on Rails Screencasts