Hi Dan,
To support what Scott says, I would try to get used to using calls to the DatabaseBlocks.cfc for your queries. That is
what it is there for ;)
I have a little snippet I keep that I can hotkey that sets up a blank for me. You just populate the clauses and rename
the qryResult if you want, and away you go.
<cfscript>
// Select record set
selectClause = "";
fromClause = "";
whereClause = "";
orderByClause = "";
qryResult = Application.DatabaseBlocks.SelectRecords(selectClause:selectClause, fromClause:fromClause,
whereClause:whereClause, orderByClause:orderByClause);
</cfscript>
Here is an example of a simple one I created today:
<cfscript>
// Select record set
selectClause = "Workshop.WSDate AS Workshop_WSDate, Workshop.WSTeams AS Workshop_WSTeams, Workshop.WSRooms AS
Workshop_WSRooms, Workshop.WSDescription AS Workshop_WSDescription";
fromClause = "PlumUser, Workshop, PlumUserWorkShop";
whereClause = "PlumUser.UserID = PlumUserWorkshop.UserId AND
Workshop.WorkshopID = PlumUserWorkshop.WorkshopID AND
(PlumUser.UserID = #GetAuthUser()#)";
orderByClause = "1";
qryResult = Application.DatabaseBlocks.SelectRecords(selectClause:selectClause, fromClause:fromClause,
whereClause:whereClause, orderByClause:orderByClause);
</cfscript>
Then you could run your QoQ against the qryResult.
Right now you have to use underscores between the tablenames and fields. Adam and David are planning for this behavior
to go away in the future, but it works well with the current component.
Go look at the methods in there. You can do inserts, updates, get record counts, etc. and you can call it with either
the dot notation above or by using a CFINVOKE.
HTH,
Jeff
Dan wrote:
Thank you Scott. !!!
A quick copy and paste to test AND IT WORKS. Now to refine it if I
can. I don’t need elegant right now, just a working app. (my wife says
I look elegant in a Tux, but I prefer a T-Shirt and Shorts)
Ironically I was headed in the direction of your reasoning—that my first
query was outside the scope of something. I was about to try caching
the query, but something tells me that wouldn’t have worked.
Dan
-----Original Message-----
*From:* [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] *On Behalf Of
*Scott Krebs
*Sent:* Thursday, May 05, 2005 5:13 PM
*To:* [email protected]
*Subject:* RE: [plum] Query of Query
Greetings Dan,
Since you run the first query in a template called by cfmodule and not
cfinclude, the result set is out of the scope of the rest of the page. A
quick fix would be to load the result set into the session scope by
doing something like the following in SiteNav.cfm after the cfquery:
<CFLOCK TIMEOUT="30" THROWONTIMEOUT="No" TYPE="EXCLUSIVE" SCOPE="SESSION">
<CFSET session.GetCategories = GetCategories>
</CFLOCK>
You can then do your QoQ on the query "session.GetCategories" and it
should work just fine.
That's nowhere near an elegant solution, but it'll work...
HTH!
Scott
------------------------------------------------------------------------
*From:* [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] *On Behalf Of *Dan
*Sent:* Thursday, May 05, 2005 4:52 PM
*To:* [email protected]
*Subject:* [plum] Query of Query
Hi there Plummers,
Struggling again. Hope there’s someone out there who can re-point me in
the right direction.
On my Header.cfm page I have near the top a CFmodule call for the
SiteNav bar.
In the SiteNav.cfm page for the bar I run a fairly fancy SQL query to
get the Nav Items from a couple of tables. No problem here—it works
very well.
A little further down the page I have a “PageBar” that will show the
“CategoryName” and the “PageName” ( A Category can/will have multiple
pages) Only the CategoryName(s) appear in the SiteNav bar at top. The
PageNames are used in the PageBar and below that in a “subNav menu”
I need the PageBar to change values displayed as a site visitor
clicks/selects CategoryName(s) from the SiteNav bar.
Since I’ve already run a query that has all the CategoryNames and
PageNames, my reasoning was that all I needed to do was run a Query of
Query on the “GetCategories” query that populates the SiteNav bar, by
passing to it (the QoQ) the CategoryID and have my WHERE clause simply
say WHERE CategoryID = #URL.Cat#
It seems to make logical sense to me.
But I am getting an error that says *my first query* “GetCategories”
*can’t be found in memory.*
Here’s some code snippets: (this is all in header.cfm)
<!-- #SiteNav -->
<cfmodule template="#request.layout#/SiteNav.cfm"
pageType="#Attributes.pageType#">
…then a bit further down the page…
<!--- #PageBar --->
<div id="PageBar">
<CFinclude template="../../qry/BCC_qry_*GetPageBarLabels.cfm*">
<cfoutput query="GetPageBarLabels">
<span class="PageTitle">#GetPageBarLabels.PageName#</span>
GetPageBarLabels.cfm is my Query of Query on GetCategories
and over on *SiteNav.cfm* I have (in part)
<CFcase value="Public">
<CFset RoleCode ="Public">
<CFinclude template="../../qry/BCC_qry_*GetCategories.cfm*">
<cfoutput query="GetCategories">
<li><a
href="#Application.absoluteUrlRoot#/PublicContent/index.cfm?Cat=#CategoryID#"
#GetCategories.CategoryName#</a></li>
</cfoutput>
If you can figure out what I’m describing here, can you tell me why not
the GetCategories query would not be in memory?
Thanks,
Dan Kaufman
//An Elephant Never Forgets//
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.5 - Release Date: 5/4/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.5 - Release Date: 5/4/2005
**********************************************************************
You can subscribe to and unsubscribe from lists, and you can change
your subscriptions between normal and digest modes here:
http://www.productivityenhancement.com/support/DiscussionListsForm.cfm
**********************************************************************
