Many-to-Many help

[Dave]

Hi Guys,

I'm having some trouble figuring out the many to many side of
CFWheels. I have been successful in getting my application to create
and delete entries from my many to many tables. I am having trouble
displaying the output of the tables in the way I want on the index.
I'm also having trouble with checkbox values on the update page. On
the index I'm looking to display the name of the press release and the
types it is associated with all on one line.

Press Release A - CHA Press Release, Hospital Press Release

Right now, with the output looping through the tables, if a press
release has two types, it is displayed twice.

Press Release A - CHA Press Release
Press Release A - Hospital Press Release

Here's what my Model CFC's looks like:

PRItem.cfc

<cfcomponent extends="model">
<cffunction name="init">
<!--- Associations --->
<cfset hasMany(name="PRSubs", shortcut="prsubs") />
</cffunction>
</cfcomponent>

PRType.cfc

<cfcomponent extends="model">
<cffunction name="init">
<!--- Associations --->
<cfset hasMany(name="PRSubs", shortcut="prsubs") />
</cffunction>
</cfcomponent>

PRSub.cfc

<cfcomponent extends="model">
<cffunction name="init">
<!--- Associations --->
<cfset belongsTo("PRItem") />
<cfset belongsTo("PRType") />
</cffunction>
</cfcomponent>

Here's my controller CFC

PR.cfc

<!--- Display information for index --->
<cffunction name="index">
<cfset qPRItems=model("prsub").findAll(include="pritem, prtype",
order="pubdate desc") />
</cffunction>

<!--- Edit press release --->
<cffunction name="edit">
<cfset pritem=model("pritem").findByKey(key=params.key,
include="prsubs") />
<cfset prtypes=model("prtype").findAll(include="prsubs",
order="type") />
</cffunction>

Views

Index.cfm

<cfparam name="qPRItems">
<cfoutput>
<h2>Current Press Releases</h2>
<cfif flashKeyExists("success")>
<p class="success">#flash("success")#</p>
</cfif>
<p>#linkTo(text="Create a Press Release", action="new")#</p>
<table border="1" cellpadding="3" cellspacing="3">
<thead>
<tr>
<th align="left">Title</th>
<th align="left">Type</th>
<th colspan="2">
</tr>
</thead>
<tbody>
<cfloop query="qPRItems">
<tr>
<td>#qPRItems.title#</td>
<td>#qPRItems.type#</td>
<td>#linkTo(text="Edit", action="edit", key=qPRItems.pritemid)#</
td>
<td>#linkTo(text="Delete", action="delete", key=qPRItems.pritemid,
confirm="Are you sure that you want to delete #qPRItems.title#?")#</
td>
</tr>
</cfloop>
</tbody>
</table>
<br />
PRItems
<cfdump var="#qPRItems#">
</cfoutput>

Edit.cfm

<cfparam name="pritem">
<cfparam name="prtypes" type="query">
<cfoutput>
<h2>Edit #pritem.title#</h2>
<cfif flashKeyExists("success")>
<p class="success">#flash("success")#</p>
</cfif>
#startFormTag(action="update")#
#hiddenField(objectName="pritem", property="id")#
<fieldset style="width:800px;">
<legend>Press Release Type</legend>
<cfloop query="prtypes">
<!--- Display all possible types of PR's --->
#checkbox(objectName="pritem", property="prtypeid",
checkedValue="#prtypeid#", label="#type#",
labelPlacement="after")#<br />
</cfloop>
</fieldset>
<br />
<fieldset style="width:800px;">
<legend>Press Release Info</legend>
<div>#textField(objectName="pritem", property="title",
label="Title: ")#</div>
<div>#textField(objectName="pritem", property="url", label="URL:
")#</div>
<div>#fileField(objectName="pritem", property="file", label="File:
")#</div>
<div>Publish Date: #dateSelect(objectName="pritem",
property="pubdate")#</div>
</fieldset>
<br />
<div>#submitTag()#</div>
#endFormTag#
<br />
<div>#linkTo(text="Return to the Index", action="index")#</div>
<br />
<cfdump var="#pritem#">
</cfoutput>

Thanks for any help you can provide. I know I'm close. I've been
struggling with this for the last two days now and having the group
look at it might help me get over this hump.

[tpet...@gmail.com]

about your index question.

what you're asking is how to display the title and the item types so
that if the title is the same for both types it will only display the
title once and then the types in a command delimited list. Is that
correct? this part of your question is confusing because you're
index.cfm view displays the title and types in a table and not on a
single line. however i give you a solution for both of these, which
involves using cfoutput and the group attribute:

single line

<cfoutput query="qPRItems" group="title">
<cfset _types = "">
<cfoutput><cfset _types = ListAppend(_types, type)</cfoutput>
#title# - #_types#
</cfoutput>

now for the table with links and such:

<table border="1" cellpadding="3" cellspacing="3">

<cfoutput query="qPRItems" group="title">
<thead>
<tr>
<th align="left" colspan="3">#title#</th>
</tr>
</thead>
<tbody>
<cfoutput>
<tr>
<td>#type#</td>
<td>#linkTo(text="Edit", action="edit", key=pritemid)#</td>
<td>#linkTo(text="Delete", action="delete", key=pritemid,
confirm="Are you sure that you want to delete #title#?")#</td>
</tr>
</cfoutput>
</tbody>
</cfoutput>
</table>

could you please elaborate on what the issue your having with
checkboxes on your edit page and i'll try to help

[Dave]

@tpetruzzi Thanks for your help on the index issue I was having. Using
the single line example you created, I was able to create the index
page I needed.

I apologize for not elaborating further on my edit page checkbox
issue. My mind has been a bit burnt out.

On my edit page I have all of the Press Release category types appear
as a list of checkboxes.

CHA Press Release
Hospital Issues - Access & Coverage
Hospital Issues - Patient Care Regulation
Hospital Issues - Public Health
Hospital Issues - Quality & Patient Safety
Hospital Issues - Workforce
Hospital Press Releases

What I am trying to do is have the boxes checked that are an
associated type to a press release. So if Press Release A has a type
of CHA Press Release & Hospital Press Release I would like those boxes
to be checked, but the remaining other types be unchecked.

I've tried using checkBox and checkboxTag, but I am unsure if the code
I have setup for the Edit function within my Controller listed in the
OP is correct.

[tpet...@gmail.com]

view this thread for more information:
http://groups.google.com/group/cfwheels/browse_thread/thread/ab1ccea597a2aa34/4f2ffe350462f283

if you could post your database schema for these table, it would help
me give you a precise example, but in any case, i'll wing it :)

in your controller, you're going to want to query your prsub model and
convert those results to a list like so

<cfset prsub = model("prsub").findAll(pritem=pritemid)>
<!--- chr(7) is safer to use then a comma when dealing with values ---
>
<cfset prsubList = ValueList(prsub.typeid, chr(7))>

then in your view, you want to change your form to use the the
checkBoxTag() method and check if the type exists in the list:

<fieldset style="width:800px;">
<legend>Press Release Type</legend>
<cfloop query="prtypes">

#checkboxTag(name="pritem[prtypeid]", value="#prtypeid#",
label="#type#", labelPlacement="after",
checked="#IIF(ListFindNoCase(prsubList, prtypeid, chr(7)),
DE('checked'), DE(''))#<br />
</cfloop>
</fieldset>

[Dave]

The database schema would probably help.

pritems

column datatype
--------------------------------------------------
id int(11)
title varchar(200)
file varchar(200)
url varchar(200)
pubdate datetime


prtypes

column datatype
--------------------------------
id int(11)
type varchar(200)


prsubs

column datatype
--------------------------------
id int(11)
pritemid int(11)
prtypeid int(11)

Thanks for all of your help.

On Aug 26, 4:28 pm, "tpetru...@gmail.com" <tpetru...@gmail.com> wrote:
> view this thread for more information:http://groups.google.com/group/cfwheels/browse_thread/thread/ab1ccea5...

> ...
>
> read more »

[tpet...@gmail.com]

i was pretty good at guessing your column names ;)

let me know what the outcome of the code i wrote in my previous
comment.

> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -

[Dave]

@tpetruzzi I tried out the code you provided and have the following
error.

Variable PRITEMID is undefined.

The error occurred in C:\inetpub\wwwroot\cha\press_room\pubman
\controllers\PR.cfc: line 55
Called from C:\inetpub\wwwroot\cha\press_room\pubman\wheels\global
\cfml.cfm: line 130
Called from C:\inetpub\wwwroot\cha\press_room\pubman\wheels\dispatch
\request.cfm: line 385
Called from C:\inetpub\wwwroot\cha\press_room\pubman\wheels\dispatch
\request.cfm: line 352
Called from C:\inetpub\wwwroot\cha\press_room\pubman\wheels\index.cfm:
line 1
Called from C:\inetpub\wwwroot\cha\press_room\pubman\index.cfm: line 1
Called from C:\inetpub\wwwroot\cha\press_room\pubman\wheels\events
\onrequest.cfm: line 1

53 : <cfset pritem=model("pritem").findByKey(key=params.key,
include="prsubs") />
54 : <!--- <cfset
prtypes=model("prtype").findAll(include="prsubs", order="type") /> ---
>
55 : <cfset prsub = model("prsub").findAll(pritem=pritemid)>
56 : <!--- chr(7) is safer to use then a comma when dealing with
values --->
57 : <cfset prsubList = ValueList(prsub.typeid, chr(7))>

Any thoughts?

> ...
>
> read more »

[tpet...@gmail.com]

change line 55 from:

<cfset prsub = model("prsub").findAll(pritem=pritemid)>

to:

<cfset prsub = model("prsub").findAll(where="pritemid=#params.key#")>

sorry about that.

> ...
>
> read more »

[Dave]

@tpetruzzi - Thanks for the code update. I've gone ahead and applied
it to my model. Now when I access the edit page I am getting the
following error related to the checkBoxTag.

Invalid CFML construct found on line 32 at column 34.
ColdFusion was looking at the following text:

width

The CFML compiler was processing:

* An expression beginning with checkboxTag, on line 25, column
34.This message is usually caused by a problem in the expressions
structure.
* The body of a cfoutput tag beginning on line 3, column 2.
* The body of a cfoutput tag beginning on line 3, column 2.


The error occurred in C:\inetpub\wwwroot\cha\press_room\pubman\views\pr
\edit.cfm: line 32

Called from C:\inetpub\wwwroot\cha\press_room\pubman\wheels\global

\cfml.cfm: line 101
Called from C:\inetpub\wwwroot\cha\press_room\pubman\wheels\controller
\rendering.cfm: line 315
Called from C:\inetpub\wwwroot\cha\press_room\pubman\wheels\controller
\rendering.cfm: line 119
Called from C:\inetpub\wwwroot\cha\press_room\pubman\wheels\controller
\rendering.cfm: line 40

Called from C:\inetpub\wwwroot\cha\press_room\pubman\wheels\dispatch

\request.cfm: line 390

Called from C:\inetpub\wwwroot\cha\press_room\pubman\wheels\dispatch
\request.cfm: line 352
Called from C:\inetpub\wwwroot\cha\press_room\pubman\wheels\index.cfm:
line 1
Called from C:\inetpub\wwwroot\cha\press_room\pubman\index.cfm: line 1
Called from C:\inetpub\wwwroot\cha\press_room\pubman\wheels\events
\onrequest.cfm: line 1

30 : </fieldset>
31 : <br />
32 : <fieldset style="width:800px;">
33 : <legend>Press Release Info</legend>
34 : <div>#textField(objectName="pritem", property="title",
label="Title: ")#</div>

Here's the checkBoxTag code

<cfloop query="prtypes">
#checkboxTag(name="pritem[prtypeid]", value="#prtypeid#",
label="#type#", labelPlacement="after",
checked="#IIF(ListFindNoCase(prsubList, prtypeid, chr(7)),
DE('checked'), DE(''))#<br />
</cfloop>

I'm not seeing any reason why this would not work.

> ...
>
> read more »

[Mike Henke]

Not to discount the many to many in wheels but you can always jump out
of the orm for complicated sql. Just do a normal <cfquery>. Seems
people are having issues with it and you can always come back to using
the feature later.

mymodel.cfc

<cffunction name="manytomany">
<cfarguments> /// anything I might want to pass in
<cfquery> /// many to many sql
<cfreturn> /// return the query
</cffucntion>


then in the controller, you just call the function like an normal orm
class.

<cfset many = model('mymodel').manytomany(any arguments) />

> ...
>
> read more »

[Chris Peters]

Looks like you're missing a parenthesis or two and closing pound sign/quotation marks. That's a ColdFusion syntax error and not a Wheels error.

When the lines start getting really long and I start nesting function calls within function calls, I usually start indenting a little for clarity.

<cfloop query="prtypes">
    #checkboxTag(
        name="pritem[prtypeid]",

        value=prtypeid,
        label=type,
        labelPlacement="after",
        checked=IIF(ListFindNoCase(prsubList, prtypeid, chr(7)), DE('checked'), DE(''))
    )#<br />
</cfloop>

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