The very model of confusion with models and adding a record to joins

Hi (this is an update to a previous problem)

I've decided my fundamental problem here is a lack of understanding
about models. I have enough to get my started by getting lost quickly.

So, following previous advice I have updated my models as such:

class Appointment < ActiveRecord::Base
has_many :line_items
belongs_to :customer
end

class Customer < ActiveRecord::Base
has_many :line_items
has_many :appointment
end

class LineItem < ActiveRecord::Base
belongs_to :appointment
belongs_to :customer
belongs_to :therapist
has_many :treatlines
end

class Therapist < ActiveRecord::Base
has_many :line_items
end

class Treatment < ActiveRecord::Base
has_many :treatlines
end

class Treatline < ActiveRecord::Base
belongs_to :line_item
belongs_to :treatment
end

I have updated my code which is trying to save lots of information in
a line_items (and associated) table using the following code in my
controller:

def book_appointment
   # Need to write this to take in the date from the date and time
boxes and
   # then put it with all the rest of the information to make the
appointment
   # Create new appointment
   @cart = find_treat
   # Setup data column for the appointment
   l = LineItem.new

   c = Customer.find(session[:currentcustID])
   t = Therapist.find(params[:appt_therapist])
   # Add a new appointment
   a = Appointment.new
   a.appdate = params[:appt_date]
   a.apptime = params[:appt_time]
   a.applength = @cart.total_time
   a.customer = c

   l.appointment = a
   l.customer = c
   l.therapist = t
   # Need to work out how to add treatments to this massive list!
Refer back to book!
   @cart.items.each do |item|
      if Treatment.find(item.id)
        tr = Treatline.new
        tr.treatment = Treatment.find(item.id)
        logger.warn("Treatment desclong is:
#{tr.treatment.desclong}")
        l.treatlines << tr
      end
   end
   l.save

end

But I'm now getting the following error message:

ActiveRecord::StatementInvalid (Mysql::Error: Column 'treatline_id'
cannot be null: INSERT INTO `line_items` (`updated_at`,
`therapist_id`, `treatline_id`, `customer_id`, `appointment_id`,
`created_at`) VALUES('2008-09-03 15:00:06', 2, NULL, 2, 8, '2008-09-03
15:00:06')):

I felt it would be better for each line item, to keep the treatments
that each customer has in a seperate table. As each customer can have
more than one treatment per visist and it'll make life easier later.
Hence I've now created a table called treatline. This table would, in
turn, reference the master treatments table which holds all the
treatments on offer. But I have no idea how to get the line_items
table to hold the id for all the treatlines I am associating with an
order. If that makes sense (and it sure doesn't seem to be making much
sense to me at the
moment!).

It might also help to show you the LineItems model set up as well?

class CreateLineItems < ActiveRecord::Migration
def self.up
   create_table :line_items do |t|
     t.column :treatline_id, :integer, :null => false
     t.column :customer_id, :integer, :null => false
     t.column :therapist_id, :integer, :null => false
     t.column :appointment_id, :integer, :null => false
     t.timestamps
   end

   execute "alter table line_items add constraint
fk_line_item_treatlines foreign key (treatline_id) references
treatlines(id)"
   execute "alter table line_items add constraint
fk_line_item_customers foreign key (customer_id) references
customers(id)"
   execute "alter table line_items add constraint
fk_line_item_therapists foreign key (therapist_id) references
therapists(id)"
   execute "alter table line_items add constraint
fk_line_item_appointments foreign key (appointment_id) references
appointments(id)"
   execute "alter table treatlines add constraint
fk_treatline_line_items foreign key (line_item_id) references
line_items(id)"
end

def self.down
   drop_table :line_items
end
end

And treatlines?

class CreateTreatlines < ActiveRecord::Migration
def self.up
   create_table :treatlines do |t|
     t.column :treatment_id, :integer, :null => false
     t.column :line_item_id, :integer, :null => false
     t.timestamps
   end
   execute "alter table treatlines add constraint
fk_treatline_treatments foreign key (treatment_id) references
treatments(id)"

end

def self.down
   drop_table :treatlines
end
end

It's like being sat with a present. I think I have the right tools to
get inside the wrapping, but I just can't seem to get my fingers to
know they are fingers and not toes. I know my misunderstanding of
models is at fault here so any help in clearing the fog and getting
this to save an appointment would be great. I hate only having half
the knowledge! It's driving me to drink!

Thanks in advance

Darren

I feel that you need to clean and simplify your model schema first.

For example i see many unnecessary complications as this:

class Appointment < ActiveRecord::Base
has_many :line_items
belongs_to :customer
end

class Customer < ActiveRecord::Base
has_many :line_items
has_many :appointment
end

A Customer object can have many Appointment objects associated to it
and each Appointment can have many LineItems. You can access LineItems
for a Customer as:

cust.appt.line_items

I don´t get why you associate directly a Customer to a LineItem. It
just adds unnecessary complexity.

Next:

class LineItem < ActiveRecord::Base
belongs_to :appointment
belongs_to :customer
belongs_to :therapist
has_many :treatlines
end

You also "belongs_to "associate the customer directly. This is
unnecesary. The customer is linked to LineItems through Appointments.

class Therapist < ActiveRecord::Base
has_many :line_items
end

class Treatment < ActiveRecord::Base
has_many :treatlines
end

class Treatline < ActiveRecord::Base
belongs_to :line_item
belongs_to :treatment
end

Here you have a many to many association. Each LineItem can have many
Treatments and each Treatment can have many LineItems. Treatline is
just a join model. I could rewrite your complete model as this:

class Customer < ActiveRecord::Base
  has_many :appointment
end

class Appointment < ActiveRecord::Base
  has_many :line_items
  belongs_to :customer
end

class LineItem < ActiveRecord::Base
  belongs_to :appointment
  belongs_to :therapist
  has_many :treatment, :through => :treatline
end

class Therapist < ActiveRecord::Base
  has_many :line_items
end

class Treatment < ActiveRecord::Base
has_many :line_items, :through => :treatline
end

class Treatline < ActiveRecord::Base
belongs_to :line_item
belongs_to :treatment
end

Just add line_item_id and treatment_id to your treatlines table, so
the associations can be handled correctly, and delete the unnecessary
_id columns from your other tables.

And voilá!

You can do this:

cust = Customer.find(*** customer id ***)
app = Appointment.create(*** whatever extra appoinment attributes you
have ***)

li = LineItem.create(:therapist_id => therapist_id, *** more
attributes ***)

or you can do it in two lines, but more readable:

li = LineItem.create(*** line item atributes)
li.therapist << Therapist.find(*** Theraphist ID ***)

li.treatments << Treatment.find(*** treatment _id***)
li.treatments << Treatment.find(*** another_treatment_id ***)
app.line_item << li

cust.appointment << app

You just created a complete appointment for your customer
This is not tested code, so it may not work at all, but it will give
you a rough idea of what can be accomplished with proper associations.

Hope it helps.

Regards,
Jorge Corona.

I forgot to associate the treatlines table. The models will be like
this:

class Customer < ActiveRecord::Base
  has_many :appointment
end

class Appointment < ActiveRecord::Base
  has_many :line_items
  belongs_to :customer
end

class LineItem < ActiveRecord::Base
  has_many :treatline
  belongs_to :appointment
  belongs_to :therapist
  has_many :treatment, :through => :treatline
end

class Therapist < ActiveRecord::Base
  has_many :line_items
end

class Treatment < ActiveRecord::Base
has_many :treatline
has_many :line_items, :through => :treatline
end

class Treatline < ActiveRecord::Base
belongs_to :line_item
belongs_to :treatment
end

Regards,
Jorge Corona.