Skip navigation
Currently Being Moderated

Building a session query from multiple queries of same name

Feb 22, 2012 11:38 PM

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

 
Replies
  • Currently Being Moderated
    Feb 22, 2012 11:43 PM   in reply to ACS LLC

    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?

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 23, 2012 12:41 AM   in reply to ACS LLC

    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

     
    |
    Mark as:
  • Dave Watts
    747 posts
    Mar 11, 2003
    Currently Being Moderated
    Feb 23, 2012 12:54 AM   in reply to ACS LLC

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 23, 2012 7:42 AM   in reply to ACS LLC

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 26, 2012 1:21 AM   in reply to ACS LLC

    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?

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 26, 2012 1:28 AM   in reply to ACS LLC

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 26, 2012 2:06 AM   in reply to ACS LLC

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 3, 2012 2:22 AM   in reply to ACS LLC

    ACS LLC wrote:

     

    It's on CF8 with MS SQL2008

    Then one should expect no major issues.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points