Forum: Ruby on Rails I need superman!

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Charlie B. (Guest)
on 2006-01-19 01:52
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');
Jan P. (Guest)
on 2006-01-19 14:58
(Received via mailing list)
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"}
  Parent Count (0.000000)   SELECT COUNT(*) FROM
parent 
  Parent Load (0.000000)   SELECT * FROM parent LIMIT
0, 10
Rendering layoutfalseactionlist within layouts/parent
Rendering parent/list
  Parent Columns (0.000000)   SHOW FIELDS FROM
parent
  Child Load (0.016000)   select child.* from
child,parent where CONCAT(SUBSTRING(child.item_number,1,14),'00') =
parent.order_number
  Child Columns (0.000000)   SHOW 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 B. (Guest)
on 2006-01-26 00:35
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!
This topic is locked and can not be replied to.