Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
It's on CF8 with MS SQL2008
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
ACS LLC wrote:
It's on CF8 with MS SQL2008
Then one should expect no major issues.
Copy link to clipboard
Copied
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.