Cache the query?

Report · May 11, 2009

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.

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

>

Report · 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:

select *

from data.Masterview

where itema = '#itema#'

order by #orderby#

</cfquery>

select *

from data.Masterview

where itemb = '#itemb#'

order by #orderby#

</cfquery>

</cfif>

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!

Report · May 11, 2009

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

Report · 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?

Report · 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?

Report · May 11, 2009

Yes Sir, I am using if/else logic and our system does allow session variables...I also made sure my code was right in my Application file. Please look at the code I have posted, maybe it's something else that I'm doing wrong - this really doesn't seem like a hard task, but I've apparently messed something up. BTW, thank you for your time in trying to help me.

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

Report · 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?

Report · 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:

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

</cfquery>

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

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:

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):

col = 'itemA'

val = #itemA#

</cfscript>

col = 'itemB'

val = #itemB#

</cfscript>

col = 'itemC'

val = #itemC#

</cfscript>

col = 'itemA'

val = #itemA#

</cfscript>

</cfif>

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

select *

from data.WF_Masterview2

where #col# = '#val#'

order by = #orderby#

</cfquery>

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

Report · May 12, 2009

Happy to help! Do post back if some of the changes you implement next don't work and we (all) can go from there.

Good luck!!

Craig

Report · May 12, 2009

Doing it your way worked great! I was able to pass the #col# and #filter# through the URL when sorting and still keep the filter options. Thank you so much Craig, I was completely frustrated messing with this and I really appreciate your time in actually trying to help me understand how to do it and providing an example.

Report · May 12, 2009

You are welcome and I'm very glad to hear it's working for you!!

Adobe Community

Cache the query?

1 Correct answer