Parent-Child Tables Help


#1

can someone help me with this

i have a table
cars
(
id int
model_id int
make_id int
color_id int
description text
constraint fk_cars_carmake foreign key(make_id) references
carmakes(id),
constraint fk_cars_carmodel foreign key(model_id) references
carmodels(id),
constraint fk_cars_color foreign key(color_id) references colors(id)
)

carmakes
(
id int
carmake varchar
)

carmodels
(
id int
carmake_id int
carmodel varchar
)

colors
(
id int
color varchar
)

Carmakes and Carmodels

  • A carmake may be Toyota, Honda etc.
  • A carmodel may be Corrola, Civic.
  • Each carmodel will have a id of its parent.
  • So for instance
    if in the carmake table i have
    1 Toyota
    2 Honda

then in the carmodel table ill have
1 1 Corolla
1 2 Civic
(hope this helps you understand)

the color table is simply
id = 1, color = red
id = 2, color = blue …etc.

Now i want to be able to Create a form to Add Cars
The form would have all the info pertaing to cars, along with dropdown
lists for the makes, models, colors…

i get this error when i do it
Mysql::Error: #23000Cannot add or update a child row: a foreign key
constraint fails: INSERT INTO cars (created_on, number_plate,
price, title, updated_on, color_id, make_id, cc_rating,
description, negotiable, views, year, transmission, user_id,
car_type, mileage, model_id) VALUES(’’, ‘PBO-5678’, 36000.0, ‘2001
Corolla’, ‘2006-03-22 02:23:35’, 0, 0, 1800, ‘Nice\n’, 0, 0, 2001,
‘Manual’, 1, ‘’’, 1600, 0)

also
Request

Parameters: {“commit”=>“Create”, “carmake”=>{“id”=>“1”},
“color”=>{“id”=>“4”}, “car”=>{“number_plate”=>“PBO-5678”, “title”=>“2001
Corolla”, “price”=>“36000”, “cc_rating”=>“1800”,
“description”=>“Nice\n”, “negotiable”=>“0”, “year”=>“2001”,
“views”=>“0”, “transmission”=>“Manual”, “mileage”=>“1600”},
“carmodel”=>{“id”=>“1”}}

now i think for some reason it is trying to save the other tables as
well…not just the cars…
anyhow i’ve been trying to get this to work for the past few nights with
no luck…can someone help me please
here are the controller/views.

#Controller

class AdminController < ApplicationController

def index

end

def return_to_main
# If you have multiple scaffolds on the same view then you will want
to change this to
# to whatever controller/action shows all the views
# (ex: redirect_to :controller => ‘AdminConsole’, :action =>
‘index’)
redirect_to :action => ‘index’
end

def list
@cars = Car.find :all
render :layout => false
end

def new
@car = Car.new

if request.xhr?
  @temp_id = Time.new.to_i
  @headers['car-id'] = @temp_id
  @headers['Content-Type'] = 'text/html; charset=utf-8'

  render :layout => false

  # If you want to send an error message:
  # render :inline => "Error text goes here", :layout => false, 

:status => 500
end
end

def create
@car = Car.new(params[:car])
@car.user_id = session[‘user’].id
@car.make_id = session[‘carmake’].id
@car.model_id = session[‘carmodel’].id
@car.color_id = session[‘color’].id

if @car.save
  if request.xhr?
    @headers['car-id'] = @car.id
    @headers['Content-Type'] = 'text/html; charset=utf-8'
    render :partial => 'car', :layout => false, :locals => { :hidden 

=> true }
else
return_to_main
end
else
render :partial => ‘form_errors’, :layout => false, :status => 500
if request.xhr?
render :action => ‘new’ if not request.xhr?
end
end

def edit
@car = Car.find(params[:id])
@car.user_id = session[‘user’].id

render :layout => false if request.xhr?

end

def update
@car = Car.find(params[:id])
@car.user_id = session[‘user’].id

if @car.update_attributes(params[:car])
  render :partial => 'car', :layout => false, :locals => { :hidden 

=> true } if request.xhr?
return_to_main if not request.xhr?
else
render :partial => ‘form_errors’, :layout => false, :status => 500
if request.xhr?
render :action => ‘edit’ if not request.xhr?
end
end

def destroy
Car.find(params[:id]).destroy
render :nothing => true if request.xhr?
return_to_main if not request.xhr?
end
end

#View

Title <%= text_field 'car', 'title' %>
Make <%= @carmakes = Carmake.find(:all, :order => "carmake") collection_select(:carmake, :id, @carmakes, :id, :carmake) %>
Model <%= @carmodels = Carmodel.find(:all, :order => "carmodel") collection_select(:carmodel, :id, @carmodels, :id, :carmodel) %>
Year <%= text_field 'car', 'year' %>
Number plate <%= text_field 'car', 'number_plate' %>
Color <%= @colors = Color.find(:all, :order => "color") collection_select(:color, :id, @colors, :id, :color) %>
Cc rating <%= text_field 'car', 'cc_rating' %>
Transmission <%= text_field 'car', 'transmission' %>
Mileage <%= text_field 'car', 'mileage' %>
Price <%= text_field 'car', 'price' %>
Negotiable <%= text_field 'car', 'negotiable' %>
Description <%= text_area 'car', 'description' %>
Created on
Updated on
Views <%= text_field 'car', 'views' %>
</div>
#--------------------------------------------------------------------------

by the way, you may ignore the user_id, i’m using SHGL and it stores the
user_id fine when i tested creations before the car table had all the
other references.

Thanks to whomsover can help me.


#2

Ray Dookie wrote:

constraint fk_cars_carmake foreign key(make_id) references
)
id int
2 Honda
Now i want to be able to Create a form to Add Cars
‘Manual’, 1, ‘’’, 1600, 0)

This ‘insert’ doesn’t match the above table definition!

You need to find out what ?_id field isn’t in your database.
Do you have :-
colors.id = 0
makes.id = 0
models.id = 0


#3

Ray Dookie wrote:

by the way, you may ignore the user_id, i’m using SHGL and it stores the
user_id fine when i tested creations before the car table had all the
other references.

Thanks to whomsover can help me.

Can you pop your models in here too?

When you assign the car make to the new car instance you should let
rails take care of the relationships so in your model you should have;

class carmake < ActiveRecord::Base
has_many :cars
end

and in;

class car < ActiveRecord::Base
belongs_to: carmake
end

then when you add the make to the car;

@make = Carmake.find(session[:carmake])
@make.cars << @car
@car.save

obviously a little cleaner that that & the :id needs passing in
:carmake, but that should do it. When you save the car rails will take
care of the foreign keys for you.


#4

Hrm… tried and still didnt not work.

ok lets try something simpler

if i have tables


car (
id int
name varchar
color_id int
constraint fk_cars_color foreign key (color_id) references colors(id)
)

and I have a table

color(
id int
color_name varchar
)


I want a form to create cars…so on the form i would have atext-field
for “name”
and a dropdown list for “color”

how would the controller (create method), model and view look
so i could just select the color from the dropdown list enter the name
and create the car??

Thanks