Oscommerce modeling question

Rails
1

All,

Many thanks for the previous help I have received from this list … I
am hoping someone can point me in the right direction here. I am
attempting to bolt on a ROR admin system for an existing oscommerce
store, and so far have been fairly successful.

My setup is a basic oscommerce install, which is apache/php/mysql. I
have abbreviated the tables to include only information relevant to
my issue.

Database Tables in question
customers:
customers_id int(11) NOT NULL auto_increment,
customers_firstname varchar(32) NOT NULL,
customers_default_address_id int(11) default NULL,
PRIMARY KEY (customers_id)

Example row in customers:
customers_id = 50
customers_firstname = bob
customers_default_address_id = 1

address_book:
address_book_id int(11) NOT NULL auto_increment,
customers_id int(11) NOT NULL,
entry_firstname varchar(32) NOT NULL,
entry_postcode varchar(10) NOT NULL,
entry_state varchar(32) default NULL,
entry_country_id int(11) NOT NULL default ‘0’,
entry_zone_id int(11) NOT NULL default ‘0’,
PRIMARY KEY (address_book_id),

Example rows in the address_book table:
address_book_id = 1
customers_id = 50
entry_firstname = bob
entry_postcode = 45619
entry_state = Alaska
entry_country_id = 223
entry_zone_id = 2

address_book_id = 11
customers_id = 50
entry_firstname = bob
entry_postcode = 40000
entry_state = Alaska
entry_country_id = 223
entry_zone_id = 2

zones:
zone_id int(11) NOT NULL auto_increment,
zone_country_id int(11) NOT NULL,
zone_code varchar(32) NOT NULL,
zone_name varchar(32) NOT NULL,
PRIMARY KEY (zone_id),
KEY idx_zones_country_id (zone_country_id)

Example row in the zones table:
zone_id = 2
zone_country_id = 223 (this is the US)
zone_code = AK
zone_name = Alaska

The customers table has the name/email/basic information for a
customer. The address_book table (note the non plural table name)
contains multiple mailing/shipping addresses for a customer. The
customers_default_address_id in the customers table denotes which is
the default address to ship to. In my example, bob has two addresses
in Alaska.

My issue is, how can I represent this information in a collection of
models such that I can use the address information properly.

My existing customer model works just fine.
I attempted to add an “address_book” model, but ran into some trouble
with the non-plural table name plus the fact that it had an
underscore.
The fact that the state two letter abbreviation is actually stored in
the zones table, left me a little stuck.

My goal was to be able to do something like this:

c = Customer.find 50 - this would find the customer record for bob -
this works great.
c.customers_state would return the current state of the current
default address (the two letter, requiring a hit of the address_book
and zones table).
c.addressbooks would return a list of all the addresses for a
customer.

My existing solution to this problem was to create a view in mysql for
my customer information and have it only use the current default
address. This was going to be an OK solution, but I discovered the
hosting service for my site is using a downlevel version of mysql, and
views are not possible.

I would be glad to share my completed mapping of rails to oscommerce
when I am done if anyone is interested …

Note: I am still a newbie on rails - please tell me if this is the not
the right place for this question.
Thanks for any help anyone can provide.

Dustin

2

This might not be exactly what you wanted, but after thinking about
your situation for a while, I decided that it would be best to point
you to some applicable documentation - primarily because I don’t know
if I could come up with a good solution for you! For your models, you
can try to use table_name (Peak Obsession
ActiveRecord/Base.html#M001396) if you have singular/plural problems.
You can also customize the inflector in config/environment.rb, I
believe. For associations, you’ll need to define your classes/foreign
keys since they don’t happily marry to the rails standards. Look at
the :class_name and :foreign_key options to different associations
(Peak Obsession
ClassMethods.html).

Sorry. I feel like I’m taking the easy way out, but others might be
able to provide you with some specifics.

-Kyle

3

No problem … any help at all is appreciated.
I have been pondering it a bit as well, and this is a far as I have
gotten.

I can create a model to match the address book like this:
./script/generate model addressbook --skip-migration
Then modify the model and force it to use my strange table name using
the table_name option you mentioned. Changing the inflector is
something I need to read a little more about, but that sounds pretty
close.

That gets me a list of address book entries, but I am not sure how to
query my model for the default one.
My guess is something like:
c = Customer.find 50
@default_address_book_id = c.customers_default_address_id
@default_address = c.addressbooks.find(:all, :conditions {:id =
@default_address_book_id} )

This still leaves me with the “zones” table, and I am still unsure how
I should handle this. Let me just take another shot at this whole
problem, maybe I haven’t applied enough brute force yet.

Thanks for your help !!

Dustin

4

Maybe create a view that ‘looks right to rails’ and maps back to your
real db?

5

OK … I have made some progress, it was somewhat easier than I
thought.

Customer Model:
class Customer < ActiveRecord::Base
set_table_name ‘rscustomers’
set_primary_key :customers_id
has_many :addressbooks, :foreign_key => “customers_id”
end

Addressbook Model:
class Addressbook < ActiveRecord::Base
set_table_name ‘address_book’
set_primary_key ‘address_book_id’
belongs_to :customer
## The following line is WRONG, not sure just how to fix it yet.
has_one :zone, :foreign_key => “zone_id”
end

Zone Model:
class Zone < ActiveRecord::Base

end

So, here is what works so far.
c = Customer.find 50

Works great, get back the correct customer.

a = c.addressbooks.find(:first) – returns the first address book
entry for a customer

Works great, returns the correct address books for the customer

a.zone
=> nil

This is my current failure point, and I see the issue but haven’t

found how to fix it yet.

There is a record that matches

From my development.log:
Addressbook Columns (0.002975) SHOW FIELDS FROM address_book
Zone Load (0.000500) SELECT * FROM zones WHERE (zones.zone_id =
50) LIMIT 1

When attempting to find the zone information for my address book
entry, the model is querying from the zones table where the zone_id =
customers id.

Note again the table structure:

customers:
address_book_id = 1
customers_id = 50
entry_firstname = bob
entry_postcode = 45619
entry_state = Alaska
entry_country_id = 223
entry_zone_id = 2

zones:
zone_id int(11) NOT NULL auto_increment,
zone_country_id int(11) NOT NULL,
zone_code varchar(32) NOT NULL,
zone_name varchar(32) NOT NULL,
PRIMARY KEY (zone_id),
KEY idx_zones_country_id (zone_country_id)

The query should be trying to find the zone in the zone table where
the entry_zone_id = zone_id.

I am reading through the active record docs to see if I can figure it
out …

Dustin

6

Got it … here are my completed model definitions:

class Addressbook < ActiveRecord::Base
set_table_name ‘address_book’
set_primary_key ‘address_book_id’
belongs_to :customer
belongs_to :zone, :foreign_key => “entry_zone_id”
end

class Customer < ActiveRecord::Base
set_table_name ‘rscustomers’
set_primary_key :customers_id
has_many :addressbooks, :foreign_key => “customers_id”
end

class Zone < ActiveRecord::Base
set_primary_key “zone_id”
has_many :addressbooks, :foreign_key => “entry_zone_id”
end

Thanks for the help, I will try to repay in kind as I get a little
more experience.

Dustin

7

That was my first thought, but I am stuck with mysql 4.x, which
doesn’t have views as a feature. That comes in mysql5. I had
considered doing a very wasteful cron script to build me a corrected
table to query on, but it seemed better to stick with an actual fix or
a view.

Dustin

On Apr 15, 3:02 am, Roger P. [email protected]