Want to receive a weekly email containing
the scoop on our new titles along with the
occasional special offer? Just click the button.
(You can always unsubscribe later by editing your
account information).

Give us an email and a password (you can use the password later to log in and
change your preferences). We'll send you a newsletter roughly once a week.

Pt-G-4

Thom says:

Warning! Unlike earlier example solutions, there are some significant differences of opinion on how to best implement this ‘extra credit’ question.

You may find yourself with an edited schema that breaks when you try to migrate, and won’t roll back easily, either (I ended up just dropping the whole database a few times until I got it the way I wanted it.)

You may also end up trying to decide whether to still use a foreign key constraint or not (I did), whether to use the strings (cc, po, etc) or just the numeric id’s to store the payment types in their own table (I chose the latter).

And you may run into an infuriatingly easy-seeming problem where if you DO include a prompt for the user (eg, ‘pick a value below’), it is not displayed, or if you prompt => nil, it does show the prompt but always displays the prompt, instead of the value you chose (if one of your other fields fails validation.)

You may then find yourself on an odyssey through the very difficult to access Rails API documentation (where do I find form.select? With examples?) because while the authors kindly list the four parameters to form.select (:attribute, choices, options, html_options) on p485 of the 2nd printed edition… they do not provide any examples of the use of the latter two parameters.

It is Thom’s fervent wish that the “Daves” (authors) would please revisit this section and replace this entire page with a version that is in keeping with the otherwise very well thought out examples on the rest of this wiki. Or that SOMEONE would.

Having been warned(!!) read on… but plan to possibly spend a lot more time trying to make this bit work than many of the previous ones.

However, the :prompt option doesn’t seem to work, I’m not sure how to do that at this stage.

Dave:

Maybe because you’re giving the payment_type_id column a default value? :prompt only does its thing if the column has no value.

Nicolas:

You’re correct, I’ve edited my listings above, removing the default value and adding the :selected symbol, set to nil. It works great now.

Ok there is a problem, and I can’t find the answer. The validates_inclusion_of :payment_type_id never returns true. It seems that the value I have put for :in is incorrect, although it does return an array. Any hints?

Nicolas:

I found the problem. I’m giving the drop-down the old values (cc, po etc..) instead of the payment_type_id that is used as a reference. So in checkout.rhtml, I corrected with this:

I have MySQL? Ver 14.7 Distrib 4.1.20 and looking around in PhpMyAdmin?, I have an orders table, with a payment_type_id field and I have a payment_types table and both tables have “id” as the primary key.

I even tried running this statement manually: “alter table orders add constraint fk_order_payment_types foreign key (payment_type_id) references payment_types(id)” but it fails with the same error.

Does anyone have any clues as to why this is failing for me?

Thanks in advance.

Tiago White:

Hi Nicolas, I am having problems with its solution aproach, when i hit the “checkout”, i’m reciving this error:

BillyGray:

(re foreign key failure) It sounds like you already have existing data in the payment_type_id field that doesn’t match a payment_type in the payment_types table. When you can’t create an FK, it’s usually because existing data violates the proposed constraint. I hope that helps!

Another tip I’ve picked up: when doing numerous updates to your database in a migration, it can be very helpful to wrap it all in a transaction block:

This way, if one of your later statements fails, you aren’t left with a mess of a migration to fix and an incorrect schema version in the schema_info table.

However, some databases like MySQL? don’t allow DDL statements like ‘create table’ within a transaction. Which is why you should use PostgreSQL? =P

JimJamesAZ:

I finally got it to take after emptying the line_items and the orders tables.

NicolasConnault:

You may also find it useful to add the option :force => true to your migrations’ table creations, this will add the DROP IF EXISTS statement to the SQL. And of course, if you are using referential integrity, don’t forget to set the table to INNODB

NicolasConnault:

Good point, it’s important to consider existing data when applying migrations. However, it’s doubtful that you would have to go back that far on a production application, since this is all part of the initial setup. These kinds of considerations would become more important as the project becomes more mature, with lots of testing happening.

Jim, contact me at some stage when you’re doing the AJAX stuff, I’m having some difficulties, but also discovering some very interesting things.

Jim Alateras:

I ended up using the has_one decorator in Order to define the relationship between order and payment type. I also removed the associated validates_inclusion_of decorator from the class. Is there any disadvantage in doing this?

linoj says:

I dont understand the need for the added complexity of a foreign key here. It seems to make it more fragile (and using the id is an extra level of indirection). For example, if you change the table with more or less options, change their order, and the id’s change, existing data will break. I’d just continue to store the selection list value in the field.

This should get you going, and you are also able to rollback your changes. In the migration script, I could not get the PayType?.creates to work (left in commented). Anyone have a solution for that?

Dave Moore says:

Your create statement is using :id but your column name is :typeid.

Hi there, I just wondered what we should need two values for in the payment_types table… once I moved the payment types into the db I only set up a table with one “name” column (besides the id). I guess that output should always be human readable and internaly I only need an ID. My validation then looks like this:

Anthony says:

I like the consideration for existing data in a live database, hadn’t thought of that, and its good to know how to convert dynamically – also the point about transactions (postgres over mysql).

As for a payment_type_id, I believe its standard to have a numeric id. The short-hand version could be handy for comparison statements when you actually go to implement the payment processing. Which do you want to write?:

Your label may very well change due to marketing purposes, but the id and the name attributes would have a tendency to remain constant.

Bill says:
I also added the DB constraint “knitting” to order.rb:

belongs_to :payment_type

and in payment_type.rb

class PaymentType < ActiveRecord::Base
has_one :order
end

sarah says:

After trial and error, I agree with linoj who said “I dont understand the need for the added complexity of a foreign key here”. I originally got this working with with a numeric fk (id), where I was adding payment_type_id and deleting pay_type from orders in my migrations. It was working, but it was fragile on the front-end because pay_type was no longer a method, payment_type_id no longer existed, etc. So I re-did it by creating table pay_types with typeid (pk) and name [typeid is the string “cc” or “check”, etc]. But still when I rolled back migrations so there was no longer a pay_types table, this code in checkout.rhtml failed:

I’d also have to restore the PAYMENT_TYPES array and comment out the incorrect validates_inclusion_of lines in order.rb.

It was a lot of manual editing for a migration rollback. So I made a method in pay_type.rb that will return pay_types from the DB, or, if the DB doesn’t exist return pay_types from a pre-existing array (the same one we had hard-coded originally in order.rb). Is this overkill? – after all, how many times, realistically, is one going to be adding and deleting the database table pay_types, and do we really need to encapsulate this? BUT doing this allowed me to drop and add the pay_types table and see no change in the code functionality and no errors. That was enough of a motivation. So:

i.e.: p.display would refer to the display method of p, instead of the column named “display” of the table payment_types.

solution was to rename the column “name”.

nulbyte says:

A chink in an otherwise good framework is this nonsense over primary keys having to be integers. There has never been a popular consensus in db that said that primary key had to be like that (database ‘agnostic’ all the same). The OP’s solution that includes both the alphanumeric code and the id. This leaves a sour taste in the mouth. Personally I use the a unique index of a the of the :type_id and set :id=>nil (boo!). I can do the foreign key alright. Foreign keys are generally a good idea in terms of integrity, and to avoid late night debugging. Database ‘agnosticism’ shouldn’t mean database ignorance.

James West says:

The problem here seems to be a lot more about the headache of migrations rather than the actual changes needed to get the change in place and working.

There are some excellent ideas here on the steps required to implement the actual interface requirements which I will certainly have a go at but the main problem appears to be caused by the fact that existing data wil lconflict with the payment types table and there seems to be a lot of confuion over how to deal with this in a migration.

In a commercial environment using other development tools I have tackled this problem many times and the solution has been very simple.

The steps to the solution as I see it are as follows
1) Create and populate the payment type table.
2) Add a foreign key to the order table (this must allow blanks and have no referential integrity at this stage.)
3) Update existing records in the order table to set the foreign key to point to the payment type table primary key (SQL is VERY good at this sort of thing.)
4) Set the foreign key on the the order table to have referential integrity and to not allow blanks (This can be done now as all data will be populated.
5) Remove the original payment type field from the order table.

What I would have done in other development environments would be to write a stored procedure to do the work possibly with an update program depending on the complexity of the situation but can all this be done in a migration. I don’t see why not as SQL can be rn as part of a migration.

The same principle applies time and time again in the real world and the above is very definitely the way to approach the solution in my mind there are no issues with key types being numeric that seem to have confused people and is totally database independant.

The only thing that you need to be careful of with this approach is that when you create and populate the payment types table is that you can easily identify the records to update in the order table.
so set the value of the Payment Type Name field to one of the following for each of the 3 payment type records you create “check”, “cc”, “po” this will allow an update statement to find all recrods where name = “po” and update them with the key from the correspondingly matched record in the payment type table

You can then go and change the value of the payment type name field in the payment type table to anything you want after the update has been run.

Now all you need to do is apply the changes to the model etc…

Have I done this yet? No! But I’m about to and if I have time I’ll post back here the actual changes I made to get it to work.

JoeyA says:

I’ve updated the checkout view with the following line to display the drop-down based on the new database table:

Does anyone know why the prompt doesn’t appear? I.e., the check, credit card, and purchase order options appear but no “Select a payment method.”
h4. PEZ says:
My migration looks much like linoj’s. Then I have this model:

I.e. it’s much like the original code, just moving some of the responsibilities to the PaymentType model. And since most of the wiring is left unchanged the validation still works.

PEZ adds
But now when I’ve reached the section about integration testing I stumble across some problems. (I’m reading the 3rd edition beta, if that matters.) I think it has to do with that the test database doesn’t have the payment types. Adding them with a fixture doesn’t help since I’m initializing the constants before the table is populated (that’s what I think happens, I could be wrong). The fixture was just something I tried to see if it would work. It’s not a solution that appeals to me. Anyone who can suggest a strategy for keeping the test database’s lookup tables populated?

McWild says:

After reading all thread, I still can not understand why all of you want to create a new field(payment_type_id) in orders table?
Why not use the old(pay_type) field?
And why we should manage migration of existed data with zero records in database(I didn’t count that one test record we have)?