I need superman!


#1

I have a legacy database that I can’t figure out. I’ve tried hundres of
times and spent way tooooo much time trying to figure this out. I don’t
think that rails can do it. Maybe there is a superman here who can get
this too work. I’m going to post my sql dump to produce a very simple
database. If anyone can get rails to work with this THEY WILL BE MY
HERO!!.

This is the statment I would normally use to create the join.
“select * from parent,child where parent.number = ‘2005121212121200’ and
CONCAT(SUBSTRING(child.item_number,1,14),‘00’) = parent.number”

As you can see child.item_number is links to the parent with
CONCAT(SUBSTRING(child.item_number,1,14),‘00’) = parent.number. The
last two digits of parent.number are incremented for each
child.item_number. To top it off, child has no primary because
child.item_number can have duplicates. This is a simplistic example of
an actuall database I am trying to use rails with. Are you superman? I
really hope that rails can handle this database. I would much rather use
rails than write out thousands of lines of perl and sql to handle this.
Someone please tell me this is possible!

Here is the sql to create the database with some simple information

CREATE TABLE child (
item_number varchar(16) NOT NULL default ‘’,
first_name varchar(8) NOT NULL default ‘’,
last_name varchar(8) NOT NULL default ‘’
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


– Dumping data for table child

INSERT INTO child VALUES (‘2005121212121201’, ‘charlie’, ‘bowman’);



– Table structure for table parent

CREATE TABLE parent (
order_number varchar(16) NOT NULL default ‘’,
client_id varchar(16) NOT NULL default ‘’,
submitted_by varchar(8) NOT NULL default ‘’,
PRIMARY KEY (order_number)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


– Dumping data for table parent

INSERT INTO parent VALUES (‘2005121212121200’, ‘45645465’, ‘smith’);


#2

Hi, charlie,

what a unique occasion for clark kenting :wink:

What YOU already did works for me, you really ought to check what is
wrong with your rails and/or mysql configuration, try another machine
and save yourself some hair…

My development.log:

Processing ParentController#index (for 127.0.0.1 at 2006-01-19 13:12:06)
[GET]
Parameters: {“action”=>“index”, “controller”=>“parent”}
e[4;36;1mParent Count (0.000000)e[0m e[0;1mSELECT COUNT() FROM
parent e[0m
e[4;35;1mParent Load (0.000000)e[0m e[0mSELECT * FROM parent LIMIT
0, 10e[0m
Rendering layoutfalseactionlist within layouts/parent
Rendering parent/list
e[4;36;1mParent Columns (0.000000)e[0m e[0;1mSHOW FIELDS FROM
parente[0m
e[4;35;1mChild Load (0.016000)e[0m e[0mselect child.
from
child,parent where CONCAT(SUBSTRING(child.item_number,1,14),‘00’) =
parent.order_numbere[0m
e[4;36;1mChild Columns (0.000000)e[0m e[0;1mSHOW FIELDS FROM
childe[0m
Completed in 0.06300 (15 reqs/sec) | Rendering: 0.03100 (49%) | DB:
0.03200 (50%) | 200 OK [http://localhost/parent]

the database.yml:

development:
adapter: mysql
database: testit_development
username: root
password: xxx
#socket: /path/to/your/mysql.sock

Connect on a TCP socket. If omitted, the adapter will connect on

the

domain socket given by socket instead.

host: localhost
port: 3306
encoding: utf8

environment.rb:

Include your application configuration below

ActiveRecord::Base.pluralize_table_names = false

The parent:

class Parent < ActiveRecord::Base

    set_primary_key :order_number
    has_many        :child,
                    :finder_sql     => "select child.* from

child,parent " +
“where CONCAT(SUBSTRING(child.item_number,1,14),‘00’) =
parent.order_number”

end

the child:

class Child < ActiveRecord::Base

set_primary_key :item_number
belongs_to :parent,
:foreign_key => ‘order_number’

end

the view:

Listing parents

<% for parent in @parents -%>
<%= parent.order_number %>

<%= parent.client_id %>

<%= parent.submitted_by %>

Orders in detail

<% for child in parent.child -%>
<%= child.item_number %>

<%= child.first_name %> <%= child.last_name %>

<% end -%>
<% end -%>

<%= link_to ‘Previous page’, { :page => @parent_pages.current.previous }
if @parent_pages.current.previous %>
<%= link_to ‘Next page’, { :page => @parent_pages.current.next } if
@parent_pages.current.next %>


<%= link_to ‘New parent’, :action => ‘new’ %>

the output:

Listing parents

2005121212121200
45645465
smith
Orders in detail
2005121212121201
charlie bowman

New parent http://localhost:3002/parent/new

Best regards
Jan


#3

I guess I can be my own superman! I finall got this stuff working. It
turns out it was a combination of bad code and bad c mysql binding on
fedora core 4. I uninstalled mysql (gem uninstall mysql) and my
segmentation faults ended. Here is the parent and child models. I hope
this helps someone else!

class Parent < ActiveRecord::Base
set_primary_key :order_number
has_many :child,
:finder_sql => ‘select * from child where
CONCAT(SUBSTRING(child.item_number,1,14),‘00’) = #{order_number}’
end

class Child < ActiveRecord::Base
belongs_to :parent,
end

In my enviroment.rb
ActiveRecord::Base::pluralize_table_names = false

Thanks to everyone who helped!