Re: [plum] Query of Query

Thu, 05 May 2005 22:15:05 -0400 

Hi Dan,

Ever wonder how all that data magically appears in your forms and lists when there 
are no <cfquery> tags to be found?
The DatabaseBlocks.cfc component (in the components directory) is used by all of the custom tags that access data in Plum apps. Open the DisplayList.cfm or InsertRecord.cfm custom tags for example, and you will see references to the DatabaseBlocks component. You won't see <cfquery> tags being used (there may be a stray one in there somewhere). This is a persistent component loaded in the Application scope (look at the Global.cfm template in your root directory), that has properties and methods that can be reused thoughout your application. They are similar to 'classes' in OOP languages and promote code reuse and ease of maintenance. 

Of course you still have to deal with exception handling and locking if 
appropriate for your scenario.
As CF moves more toward Java and OOP, it is a good idea to start looking into using components and their benefits. Adam and Dave have a great section on this topic in the excellent ColdFusion MX Bible, if you don't already have it. 

v/r,

Jeff





Thanks Jeff.

Scott's help and advice definitely helped me out.  I was headed in that
direction, but mis-stepping my way there.

Re your suggestion to use the DatabaseBlock.cfc.  My honest answer is I
don't yet understand what and why with this one.

What is this doing "better" than writing a SQL statement for? (I'll pull out
my Plum docs and read 'em)

Dan




-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jeff
Fleitz
Sent: Thursday, May 05, 2005 6:22 PM
To: [email protected]
Subject: Re: [plum] Query of Query

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




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




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

Reply via email to