create record with many-to-many and a "type" qualifier

[jjallen]

I have a model setup, where objects major and contact are related via
a bridge table in a many-to-many setup - with a twist though, I have
an additional field on the bridge table, to indicate the type of a
given contact as it relates back to the major.

I am struggling to work out how I can setup a form and action to
create a new major, with the notion of "types" of contact that are
associated.

Basically, each major has an adminContact, and a programContact.
Every contact (regardless of type) has the same fields defined (name/
email/phone/etc.).

What I can't figure out, is how to setup the new() form (view and
controller), so that the user can specify the admin contact and
program contact distinctly, and have those saved properly in the DB.

Currently I have this modeled as follows. Note I'm omitting details
for other associations and writing pseudo-code here for brevity.

DB tables:
majors: id, title, description, etc.
contacts: id, name, email, phone, etc.
majorcontacts: id, majorid, contactid, type

model cfcs:

Major.cfc
<init>
hasMany(name="majorcontacts", shortcut="contacts")

Contact.cfc
<init>
hasMany(name="majorcontacts", shortcut="majors")

MajorContact.cfc
<init>
belongsTo(major)
belongsTo(contact)
---

Now, what I am trying to figure out, is how to setup my form (views/
major/new) to be able to save two contacts on the given major, where
one contact will have type=administrative, and the other will have
type=program (corresponding to the type field in table
majorcontacts).

I have been all over the place with this... while I know I can take
steps in the controller like:

adminContact = major.contacts(where="type = 'administrative'")

...that only seems to help if I want to write out the current values
in say a detail-view.

What I can't seem to get my head around though, is how to enforce the
two types of contact on an entry form when creating a new major. I
think I almost have it worked out for an update form, but I'm lost on
how to do this for creating new records.

Do I need to use property() somehow in my models, or otherwise define
adminContact and programContact explicitly somehow? or will I have to
handle setting a major's contacts separately and use a sub-form or
something to get the type to include for each one?

Or - should I model this relationship differently in the DB or the
models ?

I've thought about splitting contacts out to two separate objects in
the model like adminContacts and programContacts, and then associating
a contact-profile to those to tack on the contact info. to each that
way. Heck, I've also wondered about doing something with inheritance
where adminContact and programContact both extend Contact in the model
for that matter.

Basically I am just looking for advice on whether I need a different
approach with my tables, if there's some way to account for the two
types of contact with my existing setup that I'm missing.

Thanks in Advance!

[Chris Peters]

Are you using Wheels 1.0.5 or Wheels 1.1? My answer to your question would vary depending on your answer. :)

--
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.

[jjallen]

Oh, I should add - the reason I can't keep the type field on the
contacts table (Contact model), is that a given contact can be the
administrative contact for one major, and the program contact for a
different major, simultaneously. So like a department chair in the
Math dept. may be the admin contact for major=math, but could also be
the program contact for major=statistics.

Hopefully that makes sense... this is what has me partially thinking I
should model AdminContact and ProgramContact separately maybe.

[jjallen]

Oh sorry:

Wheels 1.1 rc 1
CF 8 (doubt that makes a diff. but...)

Thanks.

[jjallen]

Ok I'm still stuck. I tried using a single table inheritance pattern
with each type of contact extending base contact. That seems to fail
consistently on finding multiple joins to the same table as not-
unique. Basically when I try to read a major (and include
admincontact,programcontact), the query wheels writes includes two
inner joins to the same table basically, and that throws a sql error
"table or alias not unique".

So I am back to looking at the bridge setup with a type field again.
I still can not work out, how to setup and handle a form submission to
set both types of contact on a new major, as I can't see a way to
specify the type of each as they are coming from the form... i.e. if I
have form.programContact and form.adminContact coming in, how can
wheels parse that back into the bridge associations and figure out how
to set the type (program vs. admin) on the bridge table?

Any ideas?

Thanks,

[Per Djurner]

Have you had a look at Nested Properties yet?
http://cfwheels.org/docs/1-1/chapter/nested-properties

[Chris Peters]

Per is right that nested properties will probably do the trick here. I'm going to type a lot of code below. Be sure to <cfdump> the objects as you go along so you can see how this all works. It really starts to make sense once you have one or two of these under your belt!

Configure the contact model with nested properties:

{{{

    hasMany(name="majorContacts", shortcut="contacts");

    nestedProperties(association="majorContacts");

}}}

Do this in your new() controller action:

{{{

    // Parent object

    major = model("major").new();

    // Admin and program contacts

    major.majorContacts = {

        model("majorContact").new(type="administrative"),

        model("majorContact").new(type="program")

    }

// For filling majorContacts dropdowns... Should probably be factored out into a filter method

majorContacts = model("majorContact").findAll();

}}}

And in the view:

{{{

    <!--- Administrative Contact --->

    #select(

        label="Administrative Contact",

        objectName="major",

        association="majorContacts",

        position=1,

        property="contactId",

        options=majorContacts,

        includeBlank="-- Select one --"

    )#

    #hiddenField(

        objectName="major",

        association="majorContacts",

        position=1,

        property="type"

    )#

    

    <!--- Program contact --->

    #select(

        label="Program Contact",

        objectName="major",

        association="majorContacts",

        position=2,

        property="contactId",

        options=majorContacts,

        includeBlank="-- Select one --"

    )#

    #hiddenField(

        objectName="major",

        association="majorContacts",

        position=2,

        property="type"

    )#

}}}

In the create() action, all you need to do is this:

{{{

    major = model("major").new(params.major);

    if(major.save()) {

        redirectTo(

            controller=params.controller,

            success="The major was saved successfully."

        );

    }

    else {

        renderPage(action="new");

    }

}}}

Here's what the edit action would look like. Note that it's important that in this example the first slot is "administrative," and the second slot is "program." One way around this is to have a sort property saved in the DB table, but for this example, I'll just order by type because it's alphabetically in the order that we want. If you find that you're having issues with the order that the array is appearing on error, then you may want to do a beforeValidation callback in the major model that makes sure the majorContacts array is sorted properly. Business logic FTW!

{{{

    major = model("major").findByKey(key=params.key);

    major.majorContacts = major.majorContacts(order="type", returnAs="objects");

}}}

The edit() view is similar to new(), except you need to be sure to have a hidden field for major.id.

The update() action is similar to create():

{{{

    major = model("major").findByKey(params.major.id);

    if(major.update(params.major)) {

        redirectTo(

            action="edit",

            success="The major was saved successfully."

        );

    }

    else {

        renderPage(action="edit");

    }

[jjallen]

Wow, thanks so much for the detailed reply and the extra help.

I hadn't been back to this post for a few days, as I've been working
on this when I could and studying the wheels docs a lot. I arrived at
a very similar approach...although, I think I left a couple of things
out, that may or may not come back to haunt me later heh.

Basically I was already onto nested properties before I had posted.
My main stumbling block at that time though, was how to populate the
form for new majors. Basically I was missing the bit where you create
the majorContacts as an array of obj's on the new() controller.

Finally worked that out... although I did:

{{{
<cfset var newMajorContacts =
[ model("majorcontact").new(type="administrative"),
model("majorcontact").new(type="program") ] >
<cfset major = model("major").new(majorcontacts = newMajorContacts)>
}}}

Then for the view, in my _major partial I just did:

{{{
<cfloop from="1" to="#ArrayLen(major.majorcontacts)#" index="i">
#select(label="#major.majorcontacts[i].type# contact",
objectName="major", association="majorcontacts",
position=i, property="contactid", options=allContacts)#
</cfloop>
}}}

...kind of pieced that together from examples in the docs on the
select helper... at one point I had a keys attribute on there (from
the hasManyCheckBox example) but worked out that wasn't needed pretty
quickly.

...a couple of other differences from my approach:

- I didn't add hidden fields for type on the form. Seems to be
working as expected without that, as the obj's are getting the correct
type.

- for the update action, I found that with between the nested
properties setup and the form/select, I didn't need to instantiate the
majorcontacts separately, as long as I include majorcontacts on the
find like so:

{{{
<cfset major = model("major").findByKey(key=params.key,
include="designation,college,degree,majorcontacts")>
}}}

...after that, update is just like create basically. Oh and if anyone
wonders about my using key=params.key there... I am in the habit of
setting a key attribute on startformtag like key="#major.id#". That
way, I have the findByKey factored out to a filter function as well,
and filter anything through that which needs to get a specific major
(I love filters!)

- already had the contacts, designations, colleges, etc. drop-down
data already factored out to a filter method (did I mention that I
love filters...they are the bees knees).

Overall though, approach is the same, and your write-up has given me
some additional points to think about like the ordering, hidden fields
for type etc. So I will definitely work through those as well.

Thanks again for the great reply :)

[Chris Peters]

You're welcome. That's awesome that you were able to figure out an approach similar to what I proposed. Like I said, it all starts to make sense once you start playing around with it a little.

[jjallen]

Hey, sorry to necro an old thread.  I am using nested properties with a many-to-many, with a type field on the join table, as Chris describes above.

I have this working fine for the most part, but I have found one minor issue that I am struggling with.

When the user submits the form, if they leave either of two contact selects empty (i.e. if they do not select an option for 1 of the two), the save fails on my create action.

allErrors() and errorMessagesFor() are empty arrays at that point for some reason.  When I cfdump params in the controller action (when the save fails), the interesting/relevant section has two contact objects in major.majorContacts.  The one left blank on the form has contactId = <empty string>.

The problem is, since both majorContact objs have a type already...wheels can't seem to deal with the case where the ContactId for either of those is empty.

The project requirement is that both types of contact be optional, though.

I am thinking that I might need to loop over major.majorContacts in create() and test each for a contactId, then remove any that do not have a contactId, before I call major.save().

My questions are:

- anyone know of a cleaner way to allow the contacts to be optional?  Like am I just missing something with nestedProperties maybe?

- if there's not a better way, should I loop/remove majorContact obj's that are blank in the controller, before calling .save() - or would this be better done on the model as a beforeSave() or similar?

...if it's the later - any tips on how to setup the beforeSave?

Thanks!

[Chris Peters]

Try checking allErrors() on the nested objects as well. Does that uncover anything? Does save() return false?

--

You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.

To view this discussion on the web visit https://groups.google.com/d/msg/cfwheels/-/8NRRWKZL0BwJ.

[jjallen]

save() does return false.

allErrors() returns an "empty array" on the parent object.  If I call it on the associated/nested object, CF throws an error noting that allErrors() does not exist on major.majorcontacts .

I was able to dump the params struct in my create action when submitting the new major form though.  In cases where I leave either majorcontact assignment un-selected on the form, that part of params looks like:

major

  majorcontacts (struct)

        [694375291] contactId empty string

                               type administrative

        [2]                   contactId 59

                               type program

I think what is happening is that the empty string passed for contactId is causing validation to fail on the Majorcontact object.  Since that's the nestedProperty, I think it's saving against that when I call major.save() basically.

While that may be the expected behavior out of the box, what I think I am really getting at is - how do you make associated object assignment *optional*.

bus. requirement here is that a major *can* have zero, one, or two contacts assigned, each with a type.  Here are two examples that might help illustrate that:

major: Biology

  administrative contact: [none assigned]

  program contact [contactId=59, Tom Turkey]

major: Math

  administrative contact: [contactId=59, Tom Turkey]

  program contact: [contactId=123, Jane Doe]

major: Chemistry

   administrative contact: [contactId=123, Jane Doe]

   program contact: [none assigned]

major: Art History

  administrative contact: [none assigned]

  program contact: [none assigned]

---

...so each of those cases are valid use. The contact for each type on a given major can change each semester, or can be unassigned then etc.  

* Also a given Contact can be assigned as either type of contact across a number of majors... so Jane Doe can be the admin contact for 3 majors, and the program contact for 2 other/different majors, say.  That's why I have the bridge table (majorcontacts) and use the type field, as technically there is just one master "list" of "Contacts" = tbl contacts.

So I think what I am trying to get at is...how do I make associated objects/nested properties *optional* as opposed to required?

Thanks!

[Chris Peters]

I think there is a rejectIfBlank argument in nestedProperties() that's supposed to have a role in that. I've never really quite understood how it works though. (Maybe someone can help?)

If that doesn't work out, you can write a beforeValidation() callback that cleans out the unneeded object.

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.

To view this discussion on the web visit https://groups.google.com/d/msg/cfwheels/-/-arIA4K8kWUJ.

[jjallen]

From reading the docs, rejectIfBlank looks like it will just stop all crud on the associated object if say the contactId in my case is blank/empty-string.

That would be fine except, in the case of updating an existing major, if there is a need to un-assign one of the contacts (and not replace it with another contact)... on the update form, if the user changes the value on the program contact select say to the blank option (--choose one--) ...then with rejectIfBlank on, I *think* the result would be that the contact assignment would not change in that case...which is not what I need.

I just tried the method pasted below in my controller/create action... this seems like it *should* work, but it doesn't - the save still fails:

[code]

<cffunction name="create">

                <cfset major = model("major").new(params.major)>

                <cfloop from=1 to="#arrayLen(major.majorcontacts)#" index="i">

                        <cfif not isNumeric(major.majorcontacts[i].contactId)>

                                <cfset major.majorcontacts[i]._delete = true>

                        </cfif>

                </cfloop>

                <cfif major.save()>

                        <cfset redirectTo(action="index", success="Major Saved.")>

                <cfelse>

                        <cfset getDropDownData()>

                        <cfset flashInsert(error="Save Failed.")>

                        <cfset renderPage(action="new")>

                </cfif>

        </cffunction>

[code]

If I dump #major# right before the save, I can see that the _delete property is being set as expected there...but the objects in the majorcontacts array are still *there* and the save still fails.

I'm thinking I could just remove the majorcontact in question from the array before calling save... but even if I get that far, I am going to a new/related problem with update I think.

So this has me rethinking my model again now, but I am just spinning my wheels with that, as I keep running into the same problem no matter how I draw out the model/relationships.

I'm beginning to think this will require bigger changes the app. overall than what I can afford time/etc. for at this point... driving me nuts lol.

[Chris Peters]

I'll ask an obvious question because I always miss obvious things myself...

Do you have allowDelete=true in the model? Can you share some of that code with us?

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.

To view this discussion on the web visit https://groups.google.com/d/msg/cfwheels/-/31VC236zc6wJ.

[jjallen]

Yes, and code is below.  Models are very simplistic...no callbacks, <property>'s or custom validations yet.  I do have the wheels built-in validations turned on.

Also I am omitting some code around other associations where I can for brevity - those are working fine and I think I am including everything below that is involved with major => contact assignment.

-- models/Major.cfc

<cffunction name="init">

  <cfset hasMany(name="majorcontacts", shortcut="contacts", joinType="outer", dependent="deleteAll")>

  <cfset nestedProperties(associations="majorcontacts", allowDelete=true)>

</cffunction>

--

-- models/Contact.cfc

<cffunction name="init">

  <cfset hasMany(name="majorcontacts", shortcut="majors", joinType="outer")>

</cffunction>

--

-- models/Majorcontact.cfc

<cffunction name="init">

  <cfset belongsTo("major")>

  <cfset belongsTo("contact")>

</cffunction>

--

-- controllers/Major.cfc

 <cffunction name="init">

                <cfset provides("html,json,xml")>

                <cfset filters(type="before", through="authorize")>

                <cfset verifies(only="edit,delete", get=true, params="key", paramsTypes="integer", action="index", error="Invalid ID.")>

                <cfset verifies(only="update", post=true, params="key", paramsTypes="integer", action="edit", error="Invalid ID.")>

                <cfset filters(type="before", through="read", only="edit,update,delete")>

                <cfset filters(type="before", through="getDropDownData", only="new,edit")>

        </cffunction>

        <cffunction name="getDropDownData">

                <!--- data to populate select boxes on views new and edit --->

                <cfset allColleges = model("college").findAll(order="title")>

                <cfset allDesignations = model("designation").findAll(order="title")>

                <cfset allDegrees = model("degree").findAll(order="title")>

                <cfset allContacts = model("contact").findAll(order="name")>

                <cfset allDeliveryMethods = model("deliverymethod").findAll()>

        </cffunction>

        <cffunction name="read">

          <cfset major = model("major").findByKey(key=params.key, include="designation,college,degree,majordeliverymethods")>

          <cfset major.majorContacts = major.majorContacts(order="type", returnAs="objects")>

        </cffunction>

        <cffunction name="new">

                <cfset var myContacts = ArrayNew(1)>

                <cfset myContacts[1] = model("majorContact").new(type="administrative")>

                <cfset myContacts[2] = model("majorContact").new(type="program")>

                <cfset major = model("major").new()>

                <cfset major.majorContacts = myContacts>

        </cffunction>

        <cffunction name="update">

                <cfif major.update(params.major)>

                        <cfset redirectTo(action="index", success="Major Updated.")>

                <cfelse>

                        <cfset getDropDownData()>

                        <cfset flashInsert(error="Update Failed.")>

                        <cfset renderPage(action="edit")>

                </cfif>

        </cffunction>

        <cffunction name="create">

                <cfset major = model("major").new(params.major)>

                <cfif major.save()>

                        <cfset redirectTo(action="index", success="Major Saved.")>

                <cfelse>

                        <cfset getDropDownData()>

                        <cfset flashInsert(error="Save Failed.")>

                        <cfset renderPage(action="new")>

                </cfif>

        </cffunction>

--

-- views/major/new.cfm

<cfoutput>

<h1>Add a Major</h1>

#flashMessages()#

#errorMessagesFor("major")#

#startFormTag(action="create")#

#includePartial(major)#

<div class="formRow">

#submitTag()#

</div>

#endFormTag()#

</cfoutput>

--

-- views/major/_major.cfm

<!--- note I am only including the select boxes for the contacts from the form.  This view is huge otherwise as I have a lot of cflayoutarea related code to display the form sections in tabs/etc. --->

#select(label="Administrative Contact", objectName="major", association="majorContacts", position=1,

                property="contactId", options=allContacts, includeBlank="-- Select One --")#

#hiddenField(objectName="major", association="majorContacts", position=1, property="type")#

#select(label="Program Contact", objectName="major", association="majorContacts", position=2,

                property="contactId", options=allContacts, includeBlank="-- Select One --")#

#hiddenField(objectName="major", association="majorContacts", position=2, property="type")#

--

[jjallen]

also here is a brief view of the DB tables involved:

majors

------

id (int, not null, pkey, identity/auto-increment)

title

url

....

------

majorcontacts

------

id (int, not null, pkey, identity/auto-increment)

majorid (int, not null)

contactid (int, not null)

type (varchar, not null, default binding 'program')

------

contacts

------

id (int, not null, pkey, identity/auto-increment)

name (varchar, not null)

title (varchar, nullable)

org (varchar, nullable)

email  (varchar, not null)

phone (varchar, not null)

------

...one thing I am wondering about, is whether I must have a composite primary key on the majorcontacts table.  I had to do that when setting up a different assocaition for major => deliverymethods to be able to use the hasManyCheckBox() helper setup in views for that.

Docs for the select() form help do not include reference to the keys attribute that is used with hasManyCheckBox(), so I was assuming I wouldn't need the composite pkey on majorcontacts since I'm dealing with selects.

Also the docs for select() DO have this - which confuses the heck out of me heh:

"If you are building a form with deep nesting, simply pass in a list to the nested object, and Wheels will figure it out."

Hope this helps illustrate the setup....thanks very much for the help!

[jjallen]

Ok thinking about the db keys a bit more, I tried the following in test:

- tried to recreate table majorcontacts as follows.  I first made a copy of the old table using existing data for some test major/contact relationships...

majorid (int, not null, pkey)

contactid (int, not null, pkey)

type (varchar, not null)

** That won't work though, as there are cases where the pkey data are not unique.  Since each contact can serve as either/both admin. and program. contacts, those cause this setup to fail at the DB level.

So then I tried keys setup for that table like:

id (int, identity, not null, pkey)

majorid (int, not null, pkey)

contactid (int, not null, pkey)

...that causes the app. to fail to update the majorcontacts table when updating an existing major/contacts.  Wheels does not throw an error...instead it seems to indicate that save() was successful.  However when I return to the edit form for that major, or look at the table contents directly on the DB, it just isn't changing values in the DB as well.

this also failed with the case when creating a new major, and trying to leave one of the two contacts unassigned... that causes this error at the db: 

"Cannot insert the value NULL into column 'contactid', table 'wvuMajorPrograms.dbo.majorcontacts'; column does not allow nulls. INSERT fails."

...which tells me, that instead of working out that it should drop/ignore the majorcontacts obj. with the empty contactId, it's trying to insert it anyway.

It really seems like wheels is not treating nested properties the same when using select() on the form, or that hasManyCheckBox() is doing something additional to make that work the way it does.

So basically I think I'm sunk haha.  I have no clue now how to get the behavior I need.  So I guess I'm back to the drawing board for a way to handle this requirement, I think.

Thanks.

[jjallen]

I am mainly bumping the thread so that Chris can see my inline replies above with the requested info., as it's been a while since any new posts have landed here.

For now we are operating with both contacts (one of each type) as being required for every major...so that nudge in the reqs. gets me past the problem for now.

Longer term I am looking at a possible work around using either separate join-tables (majoradmincontacts, majorprogramcontacts) that each join majors to contacts.  That would let me drop the "type" column as used with "majorcontacts" now, and I think I can then set the composite primary key on each of the two join tables as (majorid + contactid).

I think that would get around most of my issues.  Downside is that will require a bit of additional code in the controller and re-factoring my views to treat the admincontact and programcontact as distinct/separate entities basically.  Also I would need to add additional join tables/models later if we need to add more contact types, but that is very unlikely realistically speaking.

I am also eyeing an option using single table inhertance (Per blogged about that, I think) where admincontact and programcontact models would inherit from contact.  Then I think I could relate those to majors like:

admincontact hasMany majors

programcontact hasMany majors

major belongsTo each of those the other way.

This would mean I would need to add a  foreign key for each of those two on the majors table.

Regardless of which approach I take, I think I am going to have to allow the contactid (or programcontactid, admincontactid, say) to be null in the table, to represent the case where a contact that was previously assigned to a specific major, can be un-assigned on that major.

That's because basically I can't work out how to get wheels to delete a record from the join table, when setting that contact to blank/empty on the view.  Keep in mind this is using select() controls on the form... I know it works with hasManyCheckBox() but I can't use check-boxes in this case, as there are over 300 possible contacts to select from for each contact type...

Thanks for the previous replies/so far.  I am getting around this for now as it's a minor issue (requiring both contacts to always be set *shrug*)...hopefully I can address this more comprehensively later.