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'');
--
Posted via http://www.ruby-forum.com/.
I think you should be able to make it work manually, although you miss
out on some rails ''magic'' because of your schema requirements.
For
example [psuedo rails code which doesn''t take into account reserved
words, mysql quirks etc]
class Child < ActiveRecord::Base
def find_parent
Parent.find_all :conditions => [''number = ?'',
item_number[0..-3] +
''00'' ]
end
end
class Parent < ActiveRecord::Base
def find_children
Child.find_all :conditions => [''item_number like ?'',
number[0..-3] +
''%'' ]
end
end
HTH
-----Original Message-----
From: rails-bounces@lists.rubyonrails.org
[mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of charlie bowman
Sent: Thursday, 19 January 2006 10:53 AM
To: rails@lists.rubyonrails.org
Subject: [Rails] I need superman!
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'');
--
Posted via http://www.ruby-forum.com/.
_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails
Hi, charlie,
what a unique occasion for clark kenting ;-)
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"}
[4;36;1mParent Count (0.000000) [0;1mSELECT COUNT(*) FROM
parent
[4;35;1mParent Load (0.000000) SELECT * FROM parent LIMIT
0, 10
Rendering layoutfalseactionlist within layouts/parent
Rendering parent/list
[4;36;1mParent Columns (0.000000) [0;1mSHOW FIELDS FROM parent
[4;35;1mChild Load (0.016000) select child.* from
child,parent where
CONCAT(SUBSTRING(child.item_number,1,14),''00'') =
parent.order_number
[4;36;1mChild Columns (0.000000) [0;1mSHOW FIELDS FROM child
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:
<h1>Listing parents</h1>
<% for parent in @parents -%>
<%= parent.order_number %><br />
<%= parent.client_id %><br />
<%= parent.submitted_by %><br />
<strong>Orders in detail</strong><br />
<% for child in parent.child -%>
<%= child.item_number %><br />
<%= child.first_name %> <%= child.last_name %><br />
<% 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 %>
<br />
<%= 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
charlie bowman wrote:> 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'');
>
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!
--
Posted via http://www.ruby-forum.com/.