RE: reducing a query result
Query the query using the same query variable name. Adrian -Original Message- From: Chad Gray [mailto:cg...@careyweb.com] Sent: 04 June 2009 16:07 To: cf-talk Subject: reducing a query result Say I have a query returned with 500 records. Is there a way in CF (not SQL) to reduce the query to only rows 1-50, 51-100 etc? I talking physically reducing the query object. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323167 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: reducing a query result
Oh ya... duh. I kept thinking of using functions to do it. Guess I need another cup of coffee. Thanks! -Original Message- From: Adrian Lynch [mailto:cont...@adrianlynch.co.uk] Sent: Thursday, June 04, 2009 11:11 AM To: cf-talk Subject: RE: reducing a query result Query the query using the same query variable name. Adrian -Original Message- From: Chad Gray [mailto:cg...@careyweb.com] Sent: 04 June 2009 16:07 To: cf-talk Subject: reducing a query result Say I have a query returned with 500 records. Is there a way in CF (not SQL) to reduce the query to only rows 1-50, 51-100 etc? I talking physically reducing the query object. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323168 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: reducing a query result
Hey Chad, It's kind of an odd questions. Can you tell us what you are trying to accomplish? So often on these lists people ask about their solution, not their problem... I'm guess you don't really want to reduce the query, you don't want it to dump out 500 records on a page. Instead you want some sort of pagination ability. Is that it? =] -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323169 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: reducing a query result
Ya it was a pagination thing, but I thought of a better solution using CSS and a better query. I posted before I thoroughly thought it out. Thanks though! -Original Message- From: Alan Rother [mailto:alan.rot...@gmail.com] Sent: Thursday, June 04, 2009 11:17 AM To: cf-talk Subject: Re: reducing a query result Hey Chad, It's kind of an odd questions. Can you tell us what you are trying to accomplish? So often on these lists people ask about their solution, not their problem... I'm guess you don't really want to reduce the query, you don't want it to dump out 500 records on a page. Instead you want some sort of pagination ability. Is that it? =] -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323170 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: reducing a query result
Coolio... If you ever need my good friend Nathan Strutz wrote a great tool for pagination http://www.dopefly.com/projects/pagination/ Sure saves a lot of time... http://www.dopefly.com/projects/pagination/=] -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323171 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: reducing a query result
All I can say is wow! -- Ryan On Thu, Jun 4, 2009 at 10:24 AM, Alan Rother alan.rot...@gmail.com wrote: Coolio... If you ever need my good friend Nathan Strutz wrote a great tool for pagination http://www.dopefly.com/projects/pagination/ Sure saves a lot of time... http://www.dopefly.com/projects/pagination/=] -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323172 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: reducing a query result
If you are looping over the query with a cfoutput tag, you can specify the
startRow and maxRows attribute to make pagination easy. That actually
modify the query object though.
The easiest way to pare down a result set is with a query of queries which
lets you run SQL commands against a query object in memory. The trick is
you need to have a column that represents the row numbers for you. So,
assuming one of the columns in your resultset is rowNumber which contains
1 - 500, you could do this:
cfquery dbtype=query name=youOriginalQueryName
SELECT *
FROM youOriginalQueryName
WHERE rowNumber 50 and rowNumber = 100
/cfquery
or alternatively:
cfquery dbtype=query name=youOriginalQueryName maxrows=50
SELECT *
FROM youOriginalQueryName
WHERE rowNumber 50
/cfquery
Note, those cfquerys do NOT run anything against the database.
Alternatively, if you REALLY want, you can loop over the result set manually
and build a new result set with CF's query functions.
cfscript
qryNewQuery = queryNew(#qryOrigQuery.columnList#);
i = 0;
startRow = 1;
endRow = 50;
while (++i = qryOrigQuery.recordcount)
{
if(i = startRow i endRow)
{
queryAddRow(qryNewQuery,1);
c = 0;
while (++c = listLen(qryOrigQuery.columnList))
{
QuerySetCell(qryNewQuery,
listGetAt(qryOrigQuery.columnList,c),
qryOrigQuery[listGetAt(qryOrigQuery.columnList,c)][i],
qryNewQuery.recordCount);
}
}
}
/cfscript
- Original Message -
From: Chad Gray cg...@careyweb.com
To: cf-talk cf-talk@houseoffusion.com
Sent: Thursday, June 04, 2009 10:07 AM
Subject: reducing a query result
Say I have a query returned with 500 records.
Is there a way in CF (not SQL) to reduce the query to only rows 1-50,
51-100 etc?
I talking physically reducing the query object.
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323173
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: reducing a query result
+ 1 billion for pagination.cfc It just works. But for the record blockfactor maxrows can be used to limit the number of rows returned. On Thu, Jun 4, 2009 at 11:24 AM, Alan Rother alan.rot...@gmail.com wrote: Coolio... If you ever need my good friend Nathan Strutz wrote a great tool for pagination http://www.dopefly.com/projects/pagination/ Sure saves a lot of time... http://www.dopefly.com/projects/pagination/=] -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323174 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: reducing a query result
If you're using mySQL, then look into the LIMIT keyword: SELECT [columns] FROM [tableName] LIMIT [totalRecords], [startingRow] This way you ONLY get the data you want. You could easily tie this into a data grid via Ext (or jQuery) so that new pages call only the data for that page. Here's one of the preeminent jQuery versions: http://www.flexigrid.info/ And a tutorial written by a coworker of mine on Ext data grids: http://blog.cutterscrossing.com/index.cfm/2007/4/24/My-First-ExtJS-DataGrid- Pt-1 andy -Original Message- From: Chad Gray [mailto:cg...@careyweb.com] Sent: Thursday, June 04, 2009 10:07 AM To: cf-talk Subject: reducing a query result Say I have a query returned with 500 records. Is there a way in CF (not SQL) to reduce the query to only rows 1-50, 51-100 etc? I talking physically reducing the query object. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323175 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: reducing a query result
You can do something similar with SQL Server 2005 http://www.stillnetstudios.com/2008/11/12/pagination-mssql-2005/ -Ryan Andy Matthews wrote: If you're using mySQL, then look into the LIMIT keyword: SELECT [columns] FROM [tableName] LIMIT [totalRecords], [startingRow] This way you ONLY get the data you want. You could easily tie this into a data grid via Ext (or jQuery) so that new pages call only the data for that page. Here's one of the preeminent jQuery versions: http://www.flexigrid.info/ And a tutorial written by a coworker of mine on Ext data grids: http://blog.cutterscrossing.com/index.cfm/2007/4/24/My-First-ExtJS-DataGrid- Pt-1 andy -Original Message- From: Chad Gray [mailto:cg...@careyweb.com] Sent: Thursday, June 04, 2009 10:07 AM To: cf-talk Subject: reducing a query result Say I have a query returned with 500 records. Is there a way in CF (not SQL) to reduce the query to only rows 1-50, 51-100 etc? I talking physically reducing the query object. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323176 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
