Oscommerce modeling question

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

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 (http://api.rubyonrails.com/classes/
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
(http://api.rubyonrails.com/classes/ActiveRecord/Associations/
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

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

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

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

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

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]

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs