Skip to main content
C
creelove
Inspiring
May 11, 2009
Answered

Cache the query?

  • May 11, 2009
  • 4 replies
  • 2443 views

Here is what I'm attempting to do:

I have a database, where I display all of the information in a table - on each column there is the option to put in asc or desc order. This works using a switch statement to change the order. Now, I've just recently added filter options, and this works fine too using a cfif to change the WHERE clause in the query.

My problem is that once the page reloads with the new filtered information, my asc, desc function does not work with the filtered options - instead it reloads the page grabs ALL of the records and then ascends or descends. Should I be caching the query? Or is there another way to go about doing the asc and desc? Thank you for any help - please let me know if I need to add any more information.

This topic has been closed for replies.
Correct answer craigkaminsky

creelove,

Which query are you trying to cache? There are several queries in the PDF code sample and I know you mentioned you changed the code a bit but I'm guessing that the queries are similarly structured in your conditionals (suggestion on that below).

If it's the cfquery named 'qry_record' that you are looking to cache, that's going to be an issue. To cache a query (unless this has changed in CF 8, which I haven't checked), the query must be *exactly* the same.

This query:

<cfquery name="qry_record" datasource="#dsn#" cachedwithin="#blah#">

     select * from my table where itemA = '#itemA#'

</cfquery>

Is not the same (in terms of caching) as this query:

<cfquery name="qry_record" datasource="#dsn#" cachedwithin="#blah#">

     select * from my table where itemB = '#itemB#'

</cfquery>

Without the 'matching' queries, caching will not work as you expect.

One other thing I noticed is that your hidden field (<input type="hidden" name="filter" and value="yes" />) isn't using a dynamic value, it's always yes. Don't know if that might lead to an issue for you but thought I'd call it out (since you pass in a key/value in the URL for 'filter').

Given the code, which variable were you trying to store in the session scope and how were you going about that (it wasn't in the code sample, unless I missed it!)?

The qry_record query:

This is just a suggestion and what you have is not wrong, so please disregard if you wish .

You're repeating the qry_record several times in your code and you could just do it in one shot (thus making the code a bit easier for you to read and debug). The qry_record query, in the respective conditional block, is the same in all instances, except for the where clause:

<cfquery name="qry_record">

select *

from data.WF_Masterview2

^^ where x = y ^^

order by = #orderby#

</cfquery>

You could setup the WHERE part of your query in the conditional block and then type the query once at the end (much like what you do when you setup the orderby variable):

<cfif itemA is not "">

<cfscript>

     col = 'itemA'

     val = #itemA#

</cfscript>

<cfelseif itemB is not "">

<cfscript>

     col = 'itemB'

     val = #itemB#

</cfscript>

<cfelseif itemC is not "">

<cfscript>

     col = 'itemC'

     val = #itemC#

</cfscript>

<cfelse>

<cfscript>

     col = 'itemA'

     val = #itemA#

</cfscript>

</cfif>

After this conditional block runs, you can run the query:

<cfquery name="qry_record>

select *

from data.WF_Masterview2

where #col# = '#val#'

order by = #orderby#

</cfquery>

4 replies

C
creeloveAuthor
Inspiring
May 12, 2009

Can anyone help me with this? I understand "what" I need to do, which plenty of people has graciously told me, but I don't understand "how" to do it. I've changed the code quite a bit since yesterday, to incorporate the "cachedwithin" attribute in my query.  I also used <cfparam> to maintain the filtering variables at the session level and I used <cfset session> to attempt to keep the filter during the session, but this isn't working. The filtering works fine - but I think the issue is when I go to sort the queried results. My href is: <a href="index.cfm?col=1&filter=yes"> Is the issue that I'm coming back to the same page, do I need to go to another page to get this to work correctly? Does anyone have any examples of how to sort from filtered query results?

C
craigkaminskyCorrect answer
Inspiring
May 12, 2009

creelove,

Which query are you trying to cache? There are several queries in the PDF code sample and I know you mentioned you changed the code a bit but I'm guessing that the queries are similarly structured in your conditionals (suggestion on that below).

If it's the cfquery named 'qry_record' that you are looking to cache, that's going to be an issue. To cache a query (unless this has changed in CF 8, which I haven't checked), the query must be *exactly* the same.

This query:

<cfquery name="qry_record" datasource="#dsn#" cachedwithin="#blah#">

     select * from my table where itemA = '#itemA#'

</cfquery>

Is not the same (in terms of caching) as this query:

<cfquery name="qry_record" datasource="#dsn#" cachedwithin="#blah#">

     select * from my table where itemB = '#itemB#'

</cfquery>

Without the 'matching' queries, caching will not work as you expect.

One other thing I noticed is that your hidden field (<input type="hidden" name="filter" and value="yes" />) isn't using a dynamic value, it's always yes. Don't know if that might lead to an issue for you but thought I'd call it out (since you pass in a key/value in the URL for 'filter').

Given the code, which variable were you trying to store in the session scope and how were you going about that (it wasn't in the code sample, unless I missed it!)?

The qry_record query:

This is just a suggestion and what you have is not wrong, so please disregard if you wish .

You're repeating the qry_record several times in your code and you could just do it in one shot (thus making the code a bit easier for you to read and debug). The qry_record query, in the respective conditional block, is the same in all instances, except for the where clause:

<cfquery name="qry_record">

select *

from data.WF_Masterview2

^^ where x = y ^^

order by = #orderby#

</cfquery>

You could setup the WHERE part of your query in the conditional block and then type the query once at the end (much like what you do when you setup the orderby variable):

<cfif itemA is not "">

<cfscript>

     col = 'itemA'

     val = #itemA#

</cfscript>

<cfelseif itemB is not "">

<cfscript>

     col = 'itemB'

     val = #itemB#

</cfscript>

<cfelseif itemC is not "">

<cfscript>

     col = 'itemC'

     val = #itemC#

</cfscript>

<cfelse>

<cfscript>

     col = 'itemA'

     val = #itemA#

</cfscript>

</cfif>

After this conditional block runs, you can run the query:

<cfquery name="qry_record>

select *

from data.WF_Masterview2

where #col# = '#val#'

order by = #orderby#

</cfquery>

C
creeloveAuthor
Inspiring
May 12, 2009

Thank you so much for helping me, It didn't even cross my mind to do it the way you suggested. But I think that will fix my problem, because my issue was once I had the filtered results and I went to sort them it would go back to the original query, but with the way you have it - I'm thinking I should be able to pass the variables that I need (not just "filtered=yes") to the page. I'm going to get started on this and hopefully get it to work. Again, thank you SO very much for taking the time to help me, I appreciate it.

C
creeloveAuthor
Inspiring
May 11, 2009

I have attached a .PDF of the code - can anyone please help me to either cache the query or create session variables? Right now, once the page reloads with the new filtered information, my asc, desc function does not work with the filtered options - instead it reloads the page grabs ALL of the records and then ascends or descends. I have tried both query caching (cachedwithin) and session variables (through hidden variable and URL) and it isn't working, so I stripped down my code to the basics to see if someone could help. Thank you.

D
Dan_Bracuk
Inspiring
May 11, 2009

caching the query will work.  Making it a session variable will also work.

C
creeloveAuthor
Inspiring
May 11, 2009

I tried setting it to a session variable, and now it just says that it isn't defined in the session. Is this because I'm using the same page? When should I be setting it? I think I'm confused on the logical order of when I should be doing this. Meaning, when you first open the page none of the filters are chosen, but after you hit the submit button, then the new query is created and displayed on the scren. Should I create the session variable then? So that when I go to order the new list, it keeps the filtered query?

D
Dan_Bracuk
Inspiring
May 11, 2009

Did you do any if/else logic to run the query the first time if the session variable was not there?

Are you doing this in an application that allows session variables to be set?

Michael Borbor
Michael Borbor
Inspiring
May 11, 2009

You could pass URL vars in order to

Make your app "remember" the filters.

Sincerely,

Michael

El 11/05/2009, a las 10:23, creelove <forums@adobe.com> escribió:

>

Here is what I'm attempting to do:

>

I have a database, where I display all of the information in a table

- on each column there is the option to put in asc or desc order.

This works using a switch statement to change the order. Now, I've

just recently added filter options, and this works fine too using a

cfif to change the WHERE clause in the query.

>

My problem is that once the page reloads with the new filtered

information, my asc, desc function does not work with the filtered

options - instead it reloads the page grabs ALL of the records and

then ascends or descends. Should I be caching the query? Or is there

another way to go about doing the asc and desc? Thank you for any

help - please let me know if I need to add any more information.

>

C
creeloveAuthor
Inspiring
May 11, 2009

I tried that - I put it as a URL variable - when the order is changed, but then it still doesn't remember what filter was chosen or the value of the filter.  To do the filters, my code is:

<cfif IsDefined ("form.submit") OR IsDefined ("URL.filter")>

     <cfif itema NEQ "">

          <cfquery name="qry_record" datasource="#dsn#">

          select *

          from data.Masterview

          where itema = '#itema#'

          order by #orderby#

          </cfquery>

<cfelseif itemb NEQ"">

      <cfquery name="qry_record" datasource="#dsn#">

          select *

          from data.Masterview

          where itemb = '#itemb#'

          order by #orderby#

          </cfquery>

     </cfif>

<cfelse>

     <cfquery name="qry_record" datasource="#dsn#">

          select *

          from data.Masterview

          order by #orderby#

          </cfquery>

</cfif>

-------------------------------------------------------------------

When I pass the variable through the URL (filter=yes) when sorting, then it doesn't remember that the original filter was for itema, nor what the value was for itema. Right now I get an error saying that itema is undefined.

Hopefully that makes sense!

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices