oscommerce modeling question

rubyonrails-talk
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 (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

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

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

6

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

  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

7

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