Associations question

[Pierre Christen]

Hi,

I've been trying to get my head around the model design and just can't
seem to figure it out...

I've taken an example in context to explain. (BTW, TitleCasing in this
example is for readability)
I have a Transactions table that's linked to a TransactionLines table.
Both are linked with hasMany/belongsTo, and that works just fine.

Now where I have a problem is in my Transaction table, I have
CreatedBy, UpdatedBy, SignedBy, ProcessedBy, etc...
All these fields link back to the users.id that did the corresponding
action.

How should I map these in the model and how would I call them from the
controller ?
Could someone give me an example of the best way to deal with these
cases ?

Cheers.

[Andy Bellenie]

Hi Pierre,

One way is to use beforeValidation() callbacks in the model that needs
the user Id's. That callback should be something like this:

<cffunction name="setCreatedBy" returnType="boolean" output="false">
<cfif StructKeyExists(session, "userId")>
<cfset this.createdBy = session.userId>
<cfreturn true>
</cfif>
<cfreturn false>
</cffunction>

I've got a plugin that already handles this for createdBy, updatedBy
and deletedBy, and you should be able to extend it easily enough.

http://cfwheels.org/plugins/listing/12

Cheers,
Andy

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

[Pierre Christen]

Hi Andy,

Thanks for your reply, you actually sent me that plugin for CFW v1.1
just recently and I replied to you because it seems to bomb out and I
can't figure out the problem. PS: I replied to your gmail account...
but that's off topic.
Although your plugin will simplify my process when I get it to work,
I've managed to get it to work fine with a similar method you
explained above.

My question is related to retrieving information, say the FullName of
that specific user, that might not be the one currently logged in,
rather, the one that did the action.

Typically I would issue the SQL as so:

SELECT transactions.id, ... users_created.firstname,
users_created.lastname, users_submitted.firstname, etc...
FROM transactions
LEFT JOIN users AS users_created ON transactions.createdBy =
users_created.id
LEFT JOIN users AS users_submitted ON ... etc...

In the case of a Transactions / TransactionLines example, I understand
the link to the key, etc.. but I can't figure out how I should declare
the model for the example above.

On Oct 26, 3:03 am, Andy Bellenie <andybelle...@gmail.com> wrote:
> Hi Pierre,
>
> One way is to use beforeValidation() callbacks in the model that needs
> the user Id's. That callback should be something like this:
>
> <cffunction name="setCreatedBy" returnType="boolean" output="false">
> <cfif StructKeyExists(session, "userId")>
> <cfset this.createdBy = session.userId>
> <cfreturn true>
> </cfif>
> <cfreturn false>
> </cffunction>
>
> I've got a plugin that already handles this for createdBy, updatedBy
> and deletedBy, and you should be able to extend it easily enough.
>
> http://cfwheels.org/plugins/listing/12
>
> Cheers,
> Andy
>

[Andy Bellenie]

Ok.

What you want to do here is include the users in your query, so you
need to setup the model associations accordingly. Because you've got
multiple fields all referencing the same users table, you need to
setup seperate named associations, something like this:

<cfset belongsTo(name="creator", class="user", foreignKey="createdBy")>
<cfset belongsTo(name="updater", class="user", foreignKey="updatedBy")>
<cfset belongsTo(name="signee", class="user", foreignKey="signedBy")>

Then you can use the include argument on finder methods when you need
the user information.

[Pierre Christen]

It seems so easy when you write it... wow !

Thanks again for your help Andy

On Oct 26, 3:50 am, Andy Bellenie <andybelle...@gmail.com> wrote:
> Ok.
>
> What you want to do here is include the users in your query, so you
> need to setup the model associations accordingly. Because you've got
> multiple fields all referencing the same users table, you need to
> setup seperate named associations, something like this:
>
> <cfset belongsTo(name="creator", class="user", foreignKey="createdBy")>
> <cfset belongsTo(name="updater", class="user", foreignKey="updatedBy")>
> <cfset belongsTo(name="signee", class="user", foreignKey="signedBy")>
>
> Then you can use the include argument on finder methods when you need
> the user information.
>

[jjallen]

When you use named associations like in the example below, would you
also need to specify multiple hasMany associations on the User model?

I am guessing not, but I can't get my head around how a single call
like say hasMany("transactions") would work - will wheels know to look
at the named associations on the other end/model, and work it out from
there?

Basically I am looking at this as an alternative to using look-up
tables and two-way hasOne() associations. If I can get by with named
associations (vs. the additional look-up tables), this seems like it
would help keep the DB cleaner overall.

In my project, I have an entity, Major, that has two "types" of
contacts - admincontact and programcontact. Both types of contact
have the same "profile" basically, so I would like to use one contacts
table to store the contact details, but still support a major having
two contacts...if that makes sense?

The goal I have in mind is that contacts (regardless of type) would
all be managed through a single interface and stored in the one
table... then assigned as either the admincontact or programcontact,
respectively, on the major. Each contact could be the programcontact
for a number of majors, and the admincontact for a separate group of
majors.

Would you use named associations, or separate look-up tables for each
type of contact instead?

On Oct 25, 12:50 pm, Andy Bellenie <andybelle...@gmail.com> wrote:
> Ok.
>
> What you want to do here is include the users in your query, so you
> need to setup the model associations accordingly. Because you've got
> multiple fields all referencing the same users table, you need to
> setup seperate named associations, something like this:
>
> <cfset belongsTo(name="creator", class="user", foreignKey="createdBy")>
> <cfset belongsTo(name="updater", class="user", foreignKey="updatedBy")>
> <cfset belongsTo(name="signee", class="user", foreignKey="signedBy")>
>
> Then you can use the include argument on finder methods when you need
> the user information.
>

[Pierre Christen]

@jjallen

I'll try to answer in sections as I'm not sure I understand the full
scope of your question.

Q: When you use named associations like in the example below, would

you also need to specify multiple hasMany associations on the User
model?

A: You don't need to declare the hasMany on the user model in my
example above unless you need it !
In my example, I only read the linked user's details, the user is
modified elsewhere and typically uses the hasMany for example if I
would need multiple addresses for that same user.
By the way, I'm using Andy's example above with the joinType added,
typically:
belongsTo(name="signee", class="user", foreignKey="signedBy",
joinType="outer");
This allows me to get the users even if the field is Null. (produces a
LEFT OUTER JOIN)

For the rest, I will let Andy or someone else reply, but I will try to
add some food for thought anyway....

Providing I understand your senario, I would use one table, and add
some smarts to your controller/view. Using the same table makes sense
from a RDBMS standpoint.
In your particular scenario, wouldn't you be using a many-to-many
relationship ?

Slightly off topic but still an idea, In some extreme cases I've used
different models pointing to the same table, I use the model as I
would a View in a database. Typically, I use this solution in a
particular case where I have a Parameters table that stores "read-
only" parameters, such as genders, country codes, colours, etc.. ,
it's not pretty but it avoids me to have 30 extra tables with 2 or 3
records in each of them. I use this in conjunction with a modified/
forked version of the defaultScope plugin that I recently posted on
github.

Although it's not a strait answer to your problem, I hope to have
added some food for thoughts.

[Andy Bellenie]

@jjallen

Pierre did a good job of explaining all that. I agree that for your
example having two belongsTo() associations for your Major would make
the most sense.

<cfset belongsTo(name="AdminContact", class="contact", jointype="outer")>
<cfset belongsTo(name="ProgramContact", class="contact", jointype="outer)>

All your contacts would be stored in a seperate table, and you simply
have two foreign key columns in your Major's table.

@Pierre

I think what you are referring to is called Single Table Inheritance,
and while it's not yet natively supported by Wheels, Per had a great
blog post on this here:
http://per.djurner.net/single-table-inheritance-in-wheels and since
then I've built a plugin for it, which will be officially released
shortly, although as always I'm happy to send anyone a BETA off-list.

- Andy

[jjallen]

Ok after I reread my earlier post, that was pretty confusing - you all
seemed to follow it well for the most part. To help clarify:

I am developing a management app. for a majors database basically.
Users will update details for majors, contacts, colleges, etc. through
this app.

I have a contacts table with the contact's name/office address/phone/
etc. Those fields will be the same, regardless of the type of contact
(Admin or Program). Further, a specific contact could be the
AdminContact for say 3 majors, and simultaneously that same person can
be the ProgramContact for say 4 other/different majors.

In the majors table I have two foreign keys (currently) -
admincontactid and programcontactid.

My basic questions are:

- Should I use lookup/join tables between majors and contacts to
represent the admincontacts and programcontacts separately? Or,
should I just use the id from the contacts table for both foreign keys
in majors, and use named associations per your examples above?

Keeping in mind - in the app., I need to be able to read the objects
from both directions - so, I'll have a "manage contacts" view, where
the user can select a specific contact from a grid, and it will
display a list of the majors that select contact is associated with.
I will also have views for major that will display/update the two
contacts (programcontact, admincontact) assigned to the selected
major.

So that's why I was asking - if I used named associations on the Major
model like belongsTo(name="...), would also need to somehow represent
those over on the Contact model with hasMany(name="...) or similar for
each of the two? I'm not sure how I could read from either direction
otherwise...?

The other way I came up with to do this, is basically I made two
tables like:

tbl: admincontacts
majorid
contactid

tbl: programcontacts
majorid
contactid

then in the majors table, add admincontactid and programcontactid as
fkeys to those two join tables respectively.

Then in models/ I created models for ProgramContact and AdminContact
with hasMany(majors) and hasOne(contact) ... hopefully you can see
where I'm going with that.

I'm not real happy with that approach though...so hoping I can use
named associations somehow to get away from using multiple join
tables.

Thanks!

[Andy Bellenie]

Based on your description, one approach I'd suggest would be to add a
type column to the join table.

So you have:

Majors
- id
- name etc

init block:
<cfset hasMany(name="MajorContacts")>

Contacts
- id
- name
- email etc

init block:
<cfset hasMany(name="MajorContacts")>

MajorContacts
- id
- majorId
- contactId
- type

init block:
<cfset belongsTo(name="Majors")>
<cfset belongsTo(name="Contact")>

- - -

With this setup, you could do stuff like this:

<cfset contact = model("Contact").findByKey(key)>
<cfset majorsForContact = contact.majorContacts(where="type='admin'",
include="Major")>

... to return all of a contact's majors for a particular type. It's
also the most expandable method, as creating a new type of contact
would be as simple as a new entry in the type column.

Andy

[jjallen]

Bwuh!

And here all this time, I was trying to avoid using a type column on
the join table, because I didn't think the framework would handle
that. Thinking was based on some info. from a rails developer here
who had quite a bout with a similar issue around polymorphic stuff in
models under rails.

If you couldn't tell... I'm new to using ORM's/frameworks... while I
have been developing in CF and other languages for some time, I'm
still getting up to speed on the abstraction layer.

Thanks for the help!

[Andy Bellenie]

No worries :)

[jjallen]

Ok, I'm kind of stuck with this again heh. With the bridge table/type
column setup... how do I setup a form/controller to create a new
major? What I mean is - how do I setup the fields for the admin
contact and program contact, then distinguish those in the controller
to set the type properly?

Basically a new major will always have a program contact, and will
have an admin contact in some cases. I can get everything else
handled...but how, in the controller do I set the contacts and more
importantly the type...will I have to loop over a list of form inputs,
add the type to parameters scope manually, and then call setContact()
or something?

Otherwise I'm not sure how to distinguish contact types on a new
incoming major submission....

Thanks.

[Per Djurner]

Yes, I think you would have to take the manual approach here unless
you can make use of "Nested Properties" perhaps?
http://cfwheels.org/docs/1-1/chapter/nested-properties

[Chris Peters]

Related discussion with answer:

http://groups.google.com/group/cfwheels/browse_thread/thread/449337b38b2573f1/3d9b6e921f149fa8