How to properly save form-data to multiple DB tables and still sticking to Wheels conventions

[ComboFusion]

Before I start I would like to point out that this topic relates to
this one:
http://groups.google.com/group/cfwheels/browse_thread/thread/b11a5f7965c395ea/f5deb1c3df059515?lnk=gst&q=save+into+two+tables#f5deb1c3df059515.

Recently I've been working on a system (in PHP) that saves data from a
quite complicated (many input fields) web-form into 4 different MySQL
tables. When you pressed the save button below the form, I created a
MD5 unique ID, so each record would have one.

Now I want to apply a similiar problem into CF, using the CFWheels
Framework, but as a Wheels newbie I would first like to know if there
is an easier and quicker way to do it in Wheels. I know that Wheels
makes things easier and I would like to know if I there is a
conventional way in Wheels to do that.

Let's go back to my PHP solution, to explain my system... Since in
the form I have 4 different sections ("Choir" section, "Choir Master"
section, "Choir President" section and "Competition details" section)
that should be stored each in its own DB table I connected all 4
section-records with one Unique ID. I obviously stored the data into 4
different session structs / arrays (like... session.myArray1,
session.myArray2...) and each of them had the same MD5 unique ID
stored as well.

By using the same unique ID, stored in all 4 tables I could pull one
record with the same unique ID from those 4 tables at any time.

So what I did was, not to have normal foreign keys at we usually do.
Instead I could get data of one record from all 4 tables simply by
referencing each table with a unique ID that was stored in each table.

Now I am using Wheels (testing it the past few days) and I was
thinking to apply this system to my CF project, but I am not sure if
there are any Wheels conventions that help me to do this in an easier
way.

Here is a screencast I just made to show you what I want to do.
http://screenr.com/860. Here I am trying to savi data from a form into
two different DB tables.

Now with Wheels I have the idea to first save the data of the Choir
form into a session, where I also save a unique ID. After that the
user could maybe fill up the Address form which would also be saved in
the session, having the same unique ID that I just made when saving
the choir data first.

OK, I know my explanation may not be the best, so if anyone wants to
help me here, I will gladly explain if you don't understand something
that I wrote or said in the Screencast.

Thank you in advance.

[raulriera]

What version of Wheels are you using? the bleeding edge (and very
stable code) has Nested Properties, which will solve your problem in
one line :D

http://code.google.com/p/cfwheels/wiki/NestedProperties

I am pointing you to bleeding edge documention and code, maybe Chris
won't like that :P

On Aug 22, 5:23 pm, ComboFusion <areb...@gmail.com> wrote:
> Before I start I would like to point out that this topic relates to

> this one:http://groups.google.com/group/cfwheels/browse_thread/thread/b11a5f79....

> Here is a screencast I just made to show you what I want to do.http://screenr.com/860. Here I am trying to savi data from a form into

[raulriera]

If you want to go my hand, like I used to be something like this will
work

<cfif choir.save()>
<!--- here you have choir.id available so you can continue to save
more models --->
<cfset otherStuff.choirId = choir.id>
<cfif otherStuff.save()>
<cfset moreStuff />
</cfif>
</cfif>

This all should be transaction friendly of course, otherwise you will
run into a lot of orphan data.

On Aug 22, 5:23 pm, ComboFusion <areb...@gmail.com> wrote:

> Before I start I would like to point out that this topic relates to

> this one:http://groups.google.com/group/cfwheels/browse_thread/thread/b11a5f79....

> Here is a screencast I just made to show you what I want to do.http://screenr.com/860. Here I am trying to savi data from a form into

[ComboFusion]

> What version of Wheels are you using?

I am using Wheels 1.0 Final on ColdFusion 8.

the bleeding edge (and very
> stable code) has Nested Properties, which will solve your problem in
> one line :D
>
> http://code.google.com/p/cfwheels/wiki/NestedProperties

Checking it out. Thank you.

As for my form, my idea now is to make the form in more sections. The
user will fill the form in steps. I guess this sequencing should help
me solve my problem. Braking the whole problem to more smaller
problems is allways easier and also friendlier for the user.

When finished I will let you know and will try to share what I learned
on my blog: http://combofusion.posterous.com so also other's can share
it.

[ComboFusion]

Taken from the "NestedProperties" documentation:

"When you're starting out as a Wheels developer, you are probably
amazed at the simplicity of a model's CRUD methods. But then it all
gets quite a bit more complex when you need to update records in
multiple database tables in a single transaction."

I DRINK ON THAT! :) It's like reading my mind. :)))

> on my blog:http://combofusion.posterous.comso also other's can share
> it.

[ComboFusion]

Darn it, I knew it! "Variable NESTEDPROPERTIES is undefined."

Is the 1.0 version of Wheels not enough? Do I have to upgrade Wheels
to a higher version?

If I understood correctly, upgrading Wheels to a higher version is
simple and involves only rewriting the "Wheels" folder with a newer
version, right?

> > on my blog:http://combofusion.posterous.comsoalso other's can share
> > it.

[ComboFusion]

Just upgraded to Wheels 1.0.5 and it still says

" Variable NESTEDPROPERTIES is undefined. "

Any ideas?

[halfFAST]

Nested properties are coming in 1.1... you'll need to be running code
right from the svn repo to try them out as there is no release that
supports nested properties out yet.

> --
> You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
> To post to this group, send email to cfwh...@googlegroups.com.
> To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.
>
>

[ComboFusion]

Thank you anyway.

I think I know now how to solve my original problem.

Looking forward to share my solution here when it'll be done.

On Aug 22, 9:49 pm, halfFAST <halffast.soluti...@gmail.com> wrote:
> Nested properties are coming in 1.1... you'll need to be running code
> right from the svn repo to try them out as there is no release that
> supports nested properties out yet.
>

> >> > > on my blog:http://combofusion.posterous.comsoalsoother'scan share

[raulriera]

halfFAST is right, it's 1.1 only... but everyone likes trunk
lunkers :D

[ComboFusion]

> Nested properties are coming in 1.1...

Thank you for clarifying that. :)

> you'll need to be running code
> right from the svn repo to try them out as there is no release that
> supports nested properties out yet.

Sorry my ignorance, but can you please explain what's a "svn repo"?

[ComboFusion]

Alright then... I was thinking to go along with my idea of solving the
problem of saving the data to multiple tables, but unfortunatelly I
came across another problem. So I will have to postpone the result for
some other time. I didn't think that this would get so complicated.

I figured out the downside of those wonderful validation functions
(such as validatesPresenceOf(), validatesConfirmationOf(),
validatesExclusionOf()...):

The downside is that when you want your data to be saved from the form
to the session scope only (not directly to the database table), you
can actually say "bye-bye" to those form validation functions...
unfortunatelly. I realized that those functions only work when you
want to save the data to the database table, so when calling the
Wheels' save() function. Please correct me if I am wrong.

So it really get's complicated from here on. I know that Wheels is not
here to do all the job for us, so I am just asking if that means that
until we will be able to use NestedProperties() with 1.1 version of
Wheels, we have to make our own form validation system in case of
saving data to multiple tables? Well, it wouldn't be the first time
that I do it, but still... isn't there a better solution?

Nevermind now the NestedProperties() function. We will use it when
time comes. Here's my main question now: is there any other way that
would allow me to save form data to multiple database tables
simultaniusly but at the same time let me keep the functionality of
the above mentioned validation functions and the errorMessagesFor()
helper function?

I was trying to find out how the errorMessagesFor() helper function is
made, so that I could somehow make my own version of that function,
that would actually check if the information is being saved to the
session scope instead to the database directly. Unfortunatelly I
realized that there is no way I can understand all that code today. Is
just to much for a Wheels newbie. :) Maybe another time.

[raulriera]

SVN is subversion control, where Wheels code lives :)

http://code.google.com/p/cfwheels/

You can go into the "source" section and everything under the folder
"trunk" is what is called the bleeding edge code of ColdFusion on
Wheels (in other words the upcoming 1.1 version)

[Chris Peters]

For the foreseeable future, validation functions are only available when saving to the database. You can create the tables though and instead of calling save(), you can call valid() to run the validations without saving.

What I suggest is running the validations before storing the new data in the session. That way you can validate that everything is OK before "committing" to the session scope. I believe that you can wrap everything in <cftransaction> tags in case you need to roll back during the transaction. This is where Raul's example above would come in handy, but with calling valid() instead of save().

As for displaying error messages, there are a lot of ways to skin that cat. I happen to like the simplicity of errorMessagesFor(), so I have an array called errorModelObjects that my layout file looks for. Whenever a model object fails validation, I just add it to the errorModelObejcts array from the controller and let the layout handle it from there.

Here's how I call a partial called form_error_message in views/layout.cfm:

<!--- At the top of the file --->
<cfparam name="errorModelObjects" type="array" default="#ArrayNew(1)#">



<!--- Wherever you would want form errors to display in your layout --->
#includePartial(partial="/form_error_message", errorModelObjects=errorModelObjects)#

And here is the partial at views/_form_error_message.cfm:

<cfparam name="arguments.errorModelObjects" type="array">



<!--- Count errors --->
<cfset modelErrorCount = countModelObjectErrors(arguments.errorModelObjects)>


<cfif modelErrorCount>
	<cfoutput>

		<div class="error-message">
			<h2>

				There
				<cfif modelErrorCount eq 1>

					was an error
				<cfelse>
					were errors
				</cfif>

				with your submission:
			</h2>
			<cfloop array="#arguments.errorModelObjects#" index="modelObject">

				#errorMessagesFor("modelObject")#


			</cfloop>
			<p>Please correct the highlighted fields below and try again.</p>


		</div>
	</cfoutput>
</cfif>

The countModelObjectErrors() helper is stored in views/helpers.cfm and looks like this. I need to loop through the array to count how many properties in each object have errors so my error message isn't awkward.

<cffunction name="countModelObjectErrors" returntype="numeric" hint="Counts number of errors in an array of model objects.">

	<cfargument name="errorModelObjects" type="array" hint="Array of model objects to examine.">


	<cfset var loc = {}>
	<cfset loc.errorCount = 0>

	<cfloop array="#arguments.errorModelObjects#" index="loc.errorModelObject">

		<cfset loc.errorCount += loc.errorModelObject.errorCount()>

	</cfloop>

	<cfreturn loc.errorCount>


</cffunction>

Looks like this could be a recipe for John's new site. ;)

[Chris Peters]

Please disregard what I said about <cftransaction> below. I started writing that you need to save to the database and then remembered the valid() function. Forgot to remove the transactions part.

But if you ever decide to save to the database, it's good to know that you can use <cftransaction> though. ;)

[ComboFusion]

> For the foreseeable future, validation functions are only available when
> saving to the database.

That's why I was thinking to solve my problem by adjusting myself to
that. Since I think that those validation functions are sent from
heaven :), I really want to keep the functionality. I really want to
use them. In the last project I made in PHP, I was busting my ass to
make sure the validation works as it should, so I really want to use
those functions! :) So my idea now is to save the choir form data in
sections, thus for each section having a DB table on it's own. However
this may not be the best solution, because in this case we we would
have more database tables than we maybe should. But on the other had,
does this really matters? I don't know... My idea was to have
temporary tables that would be used as temporary containers... instead
of using the session scope for that purpose, we could save the
temporary data into temporary tables which would enable to access the
data also after loosing the sessions!

That may sound funny, but I am sure it wouldn't be the first time that
someone makes it this way. However I know that once we will be able to
use NestedProperties(), things won't be so complicated anymore.

So let me explain a little bit more how I was thinking to use those
sections and the "temp" tables.

1. "Main info" section
This section would have only a couple of input fields in the form.
This form would be the first thing that a user would see when clicking
the "Add choir" button in the administration. It would have the
fields: "Choir title" (textfield), "Type of choir" (select), "Date of
the Choir Establishment" (calendar or select), "Number of
singers" (select). This form, or this section would be mainly used for
creating a new record, with a new main record ID in the database. Once
the user would fill up those 4 forms (4 sections) we would just save()
the data into a DB table named "tempchoirs" (I hope this name follows
the Wheels conventions), which would allow us to use both the form
validation functions and the errorMessagesFor() helper function. Now
we would have the ID of the newely inserted choir which we would use
in the following sections (steps of the form) as a foreign key, to
connect the whole choir record (all 4 temp tables).

2. "Address" Section
After saving the data in the first section of the form, we would have
the "tempchoirs" table filled with a new record and we would be
redirected to the second step of the form - to the "Formal info"
section. Actually we would be redirected to a new action, which would
use the same controller. This section would have around 4 or 5 form
fields for the address data (city, house number, post-office
number...) and would save the filled data into the second-section DB
table named "tempadresses".

The procedure would be the same also in the "Formal info" section
(data would be stored in the "tempchoirinformation" DB table) and in
the "Extra infro" section (data would be stored in the
"tempextrachoirinformation" DB table).

Now, here is the important part!

After filling up and checking the data of all 4 sections, the user
would click "Save record" and the information would be moved from the
temporary DB tables to the main tables: "choirs", "addresses",
"choirinformation" and "extrachoirinformation".

The information from the DB tables would move (copy and delete) in
this way:

tempchoirs -> choirs
tempaddresses -> addresses
tempchoirinformation -> choirinformation
tempextrachoirinformation - > extrachoirinformation

Probably you were asking yourself: "What happens after the user stops
on the second step of the form? Do we have only one temp table
filled?"

Yes! Why not? This can also have it's benefits and it certainly
doesn't affect the user at all. Let's say that the user comes back
after a few hours and wants to insert it's choir again from the
beginning (step 1). Once he or she would start filling the form, the
app could suggest names of the choirs that were not finished filling
up and! Maybe by using Ajax.

IMHO this scenario may be useful because in this way we would
certainly keep the functionality of the form validation functions and
the errorMessagesFor() helper function. Also this may be a good idea,
or a good method for some future Wheels versions. Or we could use this
idea to make a new plugin perhaps? Hm... :)

>You can create the tables though and instead of
> calling save(), you can call

> valid()<http://cfwheels.org/docs/function/valid>to run the validations
> without saving.

Or, instead of everything I wrote above, I could do it this way. :)
Maybe.

> What I suggest is running the validations before storing the new data in the
> session.

Well, this is what I was trying to do actually, but in the process I
lost the functionality of the validation functions. I hope what you
are suggesting really works! Have you been using this system in your
apps?

> That way you can validate that everything is OK before "committing"
> to the session scope.

Exactly! Validating before even saving to the session scope would be
even better!

> I believe that you can wrap everything in
> <cftransaction> tags in case you need to roll back during the transaction.

I need to check out what are the <cftransaction> for. Also I am not
sure if I understand the "roll back during the transaction"
expression.

> This is where Raul's example above would come in handy, but with calling
> valid() instead of save().

Which example were you referring to?

> As for displaying error messages, there are a lot of ways to skin that cat.
> I happen to like the simplicity of errorMessagesFor(),

Exactly! I would be glad if we could keep this function working in
every scenario. :)

> so I have an array
> called errorModelObjects that my layout file looks for. Whenever a model
> object fails validation, I just add it to the errorModelObejcts array from
> the controller and let the layout handle it from there.

Nice! I am going to study your code and maybe later come back with
questions. You might say: "OH NOOOO! MORE QUESTIONS?!" :)))))))


For now, lEt this be all for now. I hope I am not being too annoying
with my questions and problems, but I am really excited about the new
stuff that I am learning.

Ever since I started using ColdFusion a year ago I always said that
this community is the best. However we could freely add the Wheels
community to that statement. :) Thank you guys for helping. It is very
nice to know that there is someone to ask for help, especially after a
long night unsuccessfully to solve your problem.

P.S. I someone wants to check, I am putting some of my ideas in my
blog: http://combofusion.posterous.com. Feel free to comment and
spread the words. Maybe one day someone would find my blog useful
too. :)

[ComboFusion]

So, does anyone have any objections to my scenario with the temp DB
tables? Feel free to read the matter of the subject on my blog, since
Google groups does not allow HTML formatting, which makes it hard to
read texts.

My blog: http://combofusion.posterous.com/ideas-about-saving-form-data-into-multiple-ta

BTW, I would also like to as ask you a question on this topic:

"SVN is subversion control, where Wheels code lives :)
http://code.google.com/p/cfwheels/ You can go into the "source"
section and everything under the folder "trunk" is what is called the
bleeding edge code of ColdFusion on Wheels (in other words the

upcoming 1.1 ersion) "

My question is: which files and folders do I need to download to be
able to get the NestedProperties() function to work in my 1.0.5
Wheels?

Thank you.

[ComboFusion]

Anyone? Perhaps I am beeing dull with my long posts. Sorry for that.

On Aug 23, 10:24 pm, ComboFusion <areb...@gmail.com> wrote:
> So, does anyone have any objections to my scenario with the temp DB
> tables? Feel free to read the matter of the subject on my blog, since
> Google groups does not allow HTML formatting, which makes it hard to
> read texts.
>

> My blog:http://combofusion.posterous.com/ideas-about-saving-form-data-into-mu...

>
> BTW, I would also like to as ask you a question on this topic:
>

> "SVN is subversion control, where Wheels code lives :)http://code.google.com/p/cfwheels/ You can go into the "source"

[Chris Peters]

I don't think you'd have any issues with temp tables. Let us know if you do. Give it a try on one of the models and make sure it works...

Nested properties may help with some of the complex relationships, but you'll still need a solution similar to the one that I suggested for displaying form errors.

To try 1.1, you should only need to update your wheels folder with the new one. You'll want to refer to the wiki for how-to's because some things have changed. After I update the API docs, I will be creating an "Upgrading to Wheels 1.1" chapter because some of the conventions will change slightly. (Most of the changes have settings to support backwards compatibility though...)

[ComboFusion]

Here you can see that I succescfully made a model.

http://screenr.com/b90

Now I will create the other three models and and start working on
saving data to temp tables.

We will see what happens.

On Aug 24, 1:46 pm, Chris Peters <ch...@clearcrystalmedia.com> wrote:
> I don't think you'd have any issues with temp tables. Let us know if you do.
> Give it a try on one of the models and make sure it works...
>
> Nested properties may help with some of the complex relationships, but
> you'll still need a solution similar to the one that I suggested for
> displaying form errors.
>
> To try 1.1, you should only need to update your wheels folder with the new
> one. You'll want to refer to the wiki for how-to's because some things have
> changed. After I update the API docs, I will be creating an "Upgrading to
> Wheels 1.1" chapter because some of the conventions will change slightly.
> (Most of the changes have settings to support backwards compatibility
> though...)
>

> On Mon, Aug 23, 2010 at 4:24 PM, ComboFusion <areb...@gmail.com> wrote:
> > So, does anyone have any objections to my scenario with the temp DB
> > tables? Feel free to read the matter of the subject on my blog, since
> > Google groups does not allow HTML formatting, which makes it hard to
> > read texts.
>
> > My blog:

> >http://combofusion.posterous.com/ideas-about-saving-form-data-into-mu...

> > cfwheels+u...@googlegroups.com<cfwheels%2Bunsu...@googlegroups.com>
> > .

[ComboFusion]

As promised, here's my update on the progress with forms and multiple
tables in Wheels.

I wanted to write something short here, but unfortunately "short" is
not a word in MY vocabulary! :) I started writing here and soon I
realized that my post was becoming a "blog" post, so I hope the owners
of this group don't mind if I give you the link to the last post I
made on my blog.

It is there where I described my adventure with Wheels, forms,
databases, helper functions, validations...

So, here it is. I hope someone can find anything useful in what I
wrote.

Comments are more than welcome as always!

Here you go, my last post: http://combofusion.posterous.com/very-good-progress-with-my-form-application-i

> ...
>
> read more »

[ComboFusion]

Hello again.

I am not going to write a lot, cause everything is explained in the 5
min screencast, so it's maybe easier for everyone. Sorry for my
english... it's not the best, cause again I haven't slept all night.

So, I am having a problem with the validation functions. To be short,
in one form everything works properly, but in the other the same thing
doesn't. See the video, you'll hear the explanation and see the code.
If you have any questions, please, don't hesitate to ask. Thank you in
advance.

http://screenr.com/YCr

On Aug 26, 6:08 am, ComboFusion <areb...@gmail.com> wrote:
> As promised, here's my update on the progress with forms and multiple
> tables in Wheels.
>
> I wanted to write something short here, but unfortunately "short" is
> not a word in MY vocabulary! :) I started writing here and soon I
> realized that my post was becoming a "blog" post, so I hope the owners
> of this group don't mind if I give you the link to the last post I
> made on my blog.
>
> It is there where I described my adventure with Wheels, forms,
> databases, helper functions, validations...
>
> So, here it is. I hope someone can find anything useful in what I
> wrote.
>
> Comments are more than welcome as always!
>

> Here you go, my last post:http://combofusion.posterous.com/very-good-progress-with-my-form-appl...

> ...
>
> read more »

[ComboFusion]

Oh maaaan!!! Sorry people! I found out where the problem, was!!!

Since I am making a new model

<cfset tempaddress = model("Tempaddress").new()>

... I also need to make a file named Tempaddress.cfc in my /models
folder! :) That's the whole point of making a model in the code, isn't
it? :)))

How could I miss that?! :) I guess coding while being extremely tired
isn't good. Then you miss obvious things like that.

Again, sorry for the stupid question.

> ...
>
> read more »

[ComboFusion]

And I needed one hour or more to figure that out?! At least now
understand better the MVC model as I did before hehe. I certainly
won't make that mistake again. :)

> ...
>
> read more »

[joshua clingenpeel]

On Sat, Aug 28, 2010 at 1:15 AM, ComboFusion <are...@gmail.com> wrote:
[snip]
> ... cause again I haven't slept all night.
[snip]

Well, there you go. ;)

--
Josh

[Cathy Shapiro]

Hey ComboFusion,

I use Raul's scaffolding plugin all the time just to create the basic structure. That would prevent this from happening again for you.

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To post to this group, send email to cfwh...@googlegroups.com.

To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.

--
Flash Pro Design
4646 Poplar, Suite 517
Memphis, TN 38117

Phone: (901) 767-8767
Fax: (901) 685-9054

http://www.flashprodesign.com