13 Replies Latest reply on May 12, 2009 11:54 AM by craigkaminsky

    Cache the query?

    creelove

      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.

        • 1. Re: Cache the query?
          Michael Borbor Level 4

          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.

          >

          • 2. Re: Cache the query?
            creelove Level 1

            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!

            • 3. Re: Cache the query?
              Dan Bracuk Level 5

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

              • 4. Re: Cache the query?
                creelove Level 1

                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?

                • 5. Re: Cache the query?
                  Dan Bracuk Level 5

                  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?

                  • 6. Re: Cache the query?
                    creelove Level 1

                    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.

                    • 7. Re: Cache the query?
                      creelove Level 1

                      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.

                      • 8. Re: Cache the query?
                        creelove Level 1

                        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?

                        • 9. Re: Cache the query?
                          craigkaminsky Level 3

                          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>

                          • 10. Re: Cache the query?
                            creelove Level 1

                            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.

                            • 11. Re: Cache the query?
                              craigkaminsky Level 3

                              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

                              • 12. Re: Cache the query?
                                creelove Level 1

                                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.

                                • 13. Re: Cache the query?
                                  craigkaminsky Level 3

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