11 Replies Latest reply: Mar 3, 2012 2:22 AM by BKBK RSS

    Building a session query from multiple queries of same name

    ACS LLC Community Member

      I have a SQL query that runs with the name="getcamp" multiple times within a page, then uses a template to display results.

       

      The criteria is different on each run, but the data retrieved is exactly the same.

       

      On another page I need all of the data, so I can loop over it as if it was one query. The idea is to pass the query to the other page in a name="session.getcamp" , so that the data from the query is stored in a session.

       

      The issue I have is that there are the 5 queries with the same name in the first page. Could anybody tell me how I would merge each one into a session query that would have the one name session.getcamp but contain the results of all 5 queries

       

      Thanks

       

      Mark

        • 1. Re: Building a session query from multiple queries of same name
          Owain North Community Member

          Okay well what you're trying to achieve sounds so convoluted you're almost certainly doing something wrong.

           

          Even if you used something like Query of Queries to union them all together you can't, as every time you run a query with that name, is overwrites the old resultset. It no longer exists.

           

          Use different names for your queries then using Query of Queries, but I'd imagine you're heading in the wrong direction down a dark, dark path, but without knowing exactly why it's impossible to say.

           

          Why not just run a new query on the new page? What if someone's session has ended and they hit the second page directly?

          • 2. Re: Building a session query from multiple queries of same name
            Adam Cameron. Community Member

            Is the filter criteria session-specific?  IE if you hit your site would the filter criteria be different than if I did?  as opposed to being generic site-wide information.  I'm just trying to assess whether you should be carting all this stuff around in the session scope, or whether it's more appropriate for the application scope.

             

            Also you don't really make it clear whether you want to maintain the data as multiple record sets, or roll it all into one recordset.

             

            If it's multiple ones, then just stick 'em individually into the session scope, eg: session.campData.getCamp1, session.campData.getCamp2, etc (or more descriptive names).  "getCamp" is a really crap name for a recordset variable, btw.  "getCamp" sounds like a function or method, not a variable.

             

            If you want to aggregate the recordsets into one, then QoQ them together.

             

            More important than all of that: I agree with Owain that is sounds like you're possibly going about things the wrong way here.

             

            --

            Adam

            • 3. Re: Building a session query from multiple queries of same name
              Dave Watts CommunityMVP

              I agree with everyone else who says ur doin it rong. Almost anytime you find yourself looping over a query, there's usually a better way.

               

              Seriously, why not incorporate all of the criteria in the query in the first place, then just show the appropriate records when you want to? That kind of thing is easy to do with UNION statements, at the very least. You can even add a flag to let you differentiate between the records at display time:

               

              SELECT category = 1, field1, field2, ...

              FROM table1

              WHERE somecondition = true

               

              UNION

               

              SELECT category = 2, field1, field2, ...

              FROM table1

              WHERE someothercondition = true

               

              ...

               

              Dave Watts, CTO, Fig Leaf Software

              • 4. Re: Building a session query from multiple queries of same name
                Dan Bracuk Community Member

                If you really need to run 5 queries, give them different names.  If they are run inside a loop, you can give them names like static_part#variable_part#.  If you do that, when you want to reference them you will have to use array notation with the variabes scope.

                 

                Also, be careful about what you store in the session scope.  These days the liklihood of session variables having their values changed inadvertently is much greater than it was when IE6 was the latest version.

                • 5. Re: Building a session query from multiple queries of same name
                  ACS LLC Community Member

                  It's a little difficult to explain, but basically this is powering an ad network on a mobile phone network, and there is no direct access to the content through a regular web browser as the content is served to a proprietary SDK.

                   

                  The query that I run GETCAMP selects the TOP x records based on the initial criteria, and then it draws in a template which creates the visual content, looping over the query to produce the necessary results... display the ads with their text, hyperlinks etc.

                   

                  It then lowers the counter by 'x' amount from above, so if we need 6 on the page and it only found 2, then the counter is 4, at that point it runs a query with a different criteria, and again repeats the process, using the displaytemplate to output the results.

                   

                  The displaytemplate always uses getcamp so by using the same query name it does not know, or care that it was a different query that call the template.

                   

                  As I am working some trickery on the phones system to bring in the Android market, I have to recreate a page that initially compiled and displayed all of the offers on the next page when they click, due to some restrictions in the phone system. So the idea was if I could get all of the results into one session query it would save me going back to the dbase to get the data I already had

                   

                  Hope that makes it a tad clearer

                   

                  Thanks

                   

                  Mark

                  • 6. Re: Building a session query from multiple queries of same name
                    BKBK CommunityMVP

                    ACS LLC wrote:

                     

                    I have a SQL query that runs with the name="getcamp" multiple times within a page, then uses a template to display results.

                     

                    The criteria is different on each run, but the data retrieved is exactly the same.

                     

                    On another page I need all of the data, so I can loop over it as if it was one query. The idea is to pass the query to the other page in a name="session.getcamp" , so that the data from the query is stored in a session.

                     

                    The issue I have is that there are the 5 queries with the same name in the first page. Could anybody tell me how I would merge each one into a session query that would have the one name session.getcamp but contain the results of all 5 queries

                    Your ColdFusion version and database engine?

                    • 7. Re: Building a session query from multiple queries of same name
                      BKBK CommunityMVP

                      ACS LLC wrote:

                       

                      It's a little difficult to explain, but basically this is powering an ad network on a mobile phone network, and there is no direct access to the content through a regular web browser as the content is served to a proprietary SDK.

                       

                      The query that I run GETCAMP selects the TOP x records based on the initial criteria, and then it draws in a template which creates the visual content, looping over the query to produce the necessary results... display the ads with their text, hyperlinks etc.

                       

                      It then lowers the counter by 'x' amount from above, so if we need 6 on the page and it only found 2, then the counter is 4, at that point it runs a query with a different criteria, and again repeats the process, using the displaytemplate to output the results.

                       

                      The displaytemplate always uses getcamp so by using the same query name it does not know, or care that it was a different query that call the template.

                       

                      As I am working some trickery on the phones system to bring in the Android market, I have to recreate a page that initially compiled and displayed all of the offers on the next page when they click, due to some restrictions in the phone system. So the idea was if I could get all of the results into one session query it would save me going back to the dbase to get the data I already had

                       

                      Hope that makes it a tad clearer

                      I hope you will heed all the advice above. However, nothing is set in stone. The whole basis of our business is, you should be able to justify the design choices you make.

                      • 8. Re: Building a session query from multiple queries of same name
                        BKBK CommunityMVP

                        ACS LLC wrote:

                         

                        I have a SQL query that runs with the name="getcamp" multiple times within a page, then uses a template to display results.

                         

                        The criteria is different on each run, but the data retrieved is exactly the same.

                         

                        On another page I need all of the data, so I can loop over it as if it was one query. The idea is to pass the query to the other page in a name="session.getcamp" , so that the data from the query is stored in a session.

                         

                        The issue I have is that there are the 5 queries with the same name in the first page. Could anybody tell me how I would merge each one into a session query that would have the one name session.getcamp but contain the results of all 5 queries

                        I would suggest the following design:

                         

                        1) Indeed use the session scope to store the part-queries. However, I would store each of them in the session scope, and union them only where a union is required. You thereby achieve more power and flexibility, as each of the queries will continue to be available to requests throughout the session.

                         

                        2) I would use the duplicate function to avoid any further reference to the original query. Something like:

                         

                        First query, name= "getCamp"

                        <cfset session.getCamp1 = duplicate(getCamp)>

                        ...

                        ...

                        Second query, name= "getCamp"

                        <cfset session.getCamp2 = duplicate(getCamp)>

                        ...

                        ...

                        etc., etc.

                         

                        3) On the page where you need to merge the 5 queries:

                         

                        <cfquery name="getCamp_merged" dbtype="query">

                        select * from session.getCamp1

                        union

                        select * from session.getCamp2

                        union

                        select * from session.getCamp3

                        union

                        select * from session.getCamp4

                        union

                        select * from session.getCamp5

                        </cfquery>

                         

                        It's Sunday today, at least where I am. As far as CPU consumption is concerned, whether or not what I've suggested turns out to be the devil's work is up to you to find out.

                        • 9. Re: Building a session query from multiple queries of same name
                          ACS LLC Community Member

                          It's on CF8 with MS SQL2008

                          • 10. Re: Building a session query from multiple queries of same name
                            ACS LLC Community Member

                            I have not had time to try this yet, but I did a little searching and found this link by Ben Nadel talking about the speed of the duplicate command, vs query over query, and duplicate appears to do a very good, fast job

                             

                            http://www.bennadel.com/blog/127-Duplicate-Much-Faster-Than-ColdFusion-Query-of-Queries.ht m

                             

                            I'll be on this later or tomorrow and will report back, looks like a good solution to me

                             

                            I have to admit I was not aware of the duplicater function

                             

                            Thanks

                            • 11. Re: Building a session query from multiple queries of same name
                              BKBK CommunityMVP

                              ACS LLC wrote:

                               

                              It's on CF8 with MS SQL2008

                              Then one should expect no major issues.