Building a session query from multiple queries of same name

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.

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?

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.

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

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

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?