Building a session query from multiple queries of same name

Report · Feb 22, 2012

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

Report · Feb 22, 2012

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?

Report · Feb 23, 2012

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

Report · Feb 23, 2012

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

Report · Feb 23, 2012

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.

Report · Feb 23, 2012

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

Report · Feb 26, 2012

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.

Report · Feb 26, 2012

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?

Report · Feb 26, 2012

It's on CF8 with MS SQL2008

Report · Feb 26, 2012

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.htm

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

Report · Mar 03, 2012

ACS LLC wrote:
It's on CF8 with MS SQL2008

Then one should expect no major issues.

Report · Feb 26, 2012

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"

...

Second query, name= "getCamp"

...

etc., etc.

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

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.

Adobe Community

Building a session query from multiple queries of same name