Hope everyone's looking forward to the weekend. I certainly am!
I asked a few weeks ago if anyone had seen a coldfusion database query tool. I found a plugin for ExtJS which basically creates the WHERE statement from the choices you make (CherryExt). I've ported the backend code to Coldfusion but am having a few problems with the SQL. Some the SQL has subqueries for counting rows etc. If I pass in a SQL filter of "materialsent = 1" into the first query it obviously fails, so I got around it by then querying a query. It means I have to load the whole recordset in the first query (very slow with large recordsets) and also qoq do not allow me to use the limit command. Does anyone have any suggestions of how I could do this better or point me in the right direction, SQL isn't my strongest skill!
<cfquery name="returnData" datasource="#application.settings.datasource#"> SELECT tbl_organisations.id, tbl_organisations.name, tbl_organisations.datecreated, (SELECT COUNT(*) FROM lnk_materials WHERE tbl_organisations.id = lnk_materials.orgId) AS materialsent FROM tbl_organisations </cfquery>
<cfquery name="returnData" dbtype="query"> SELECT * FROM returnData WHERE #getSQL(filters)# </cfquery>
This e-mail is confidential and should not be used by anyone who is not the original intended recipient. If you have received this e-mail in error please inform the sender and delete it from your mailbox or any other storage mechanism. Wiredmonkey cannot accept liability for any statements made which may be the sender's own and not expressly made on behalf of Wiredmonkey. Electronic mail is not a secure form of communication and can be subject to interference.
You might want to try re-writing the first query using GROUP BY to get the
aggregate information. Some of the filters would go in the WHERE statement,
and some in a HAVING statement.
Been a while since I've used HAVING, so I'm not 100% sure this will work
--------- Original Message --------
From: scottishcfug@googlegroups.com
To: scottishcfug@googlegroups.com <scottishcfug@googlegroups.com>
Subject: [SCFUG] Pure SQL or dtype="query" anyone?
Date: Oct 16, 2009 02:12 PM
> Hi > all
>
> Hope everyone's > looking forward to the weekend. I certainly am! >
> I asked a few weeks > ago if anyone had seen a coldfusion database query tool. I found
a > plugin for ExtJS which basically creates the WHERE statement from the
choices > you make (CherryExt). I've ported the backend code to Coldfusion but am
having a > few problems with the SQL. Some the SQL has subqueries for counting rows
etc. If > I pass in a SQL filter of "materialsent = 1" into the first query it > obviously fails, so I got around it by then querying a query. It means I
have to > load the whole recordset in the first query (very slow with large
recordsets) > and also qoq do not allow me to use the limit command. Does anyone have
any > suggestions of how I could do this better or point me in the right
direction, > SQL isn't my strongest skill!
>
> <cfquery name="returnData" > datasource="#application.settings.datasource#"> SELECT > tbl_organisations.id, tbl_organisations.name, > tbl_organisations.datecreated, (SELECT > COUNT(*) FROM > lnk_materials WHERE > tbl_organisations.id = lnk_materials.orgId) AS materialsent FROM > tbl_organisations</cfquery>
>
> <cfquery name="returnData" > dbtype="query"> SELECT * FROM returnData WHERE > #getSQL(filters)#</cfquery>
>
> Kind > regards
> Shaun Perry
> Wiredmonkey +44 (0)131 2080811+44 (0)7921
> This e-mail is confidential and should not be used by > anyone who is not the original intended recipient. If you have received
this > e-mail in error please inform the sender and delete it from your mailbox
or any > other storage mechanism. Wiredmonkey cannot accept liability for any
statements > made which may be the sender's own and not expressly made on behalf of > Wiredmonkey. Electronic mail is not a secure form of communication and can
be > subject to interference.
>
I'm changing the backend code so I can split some of the filters into the
where statement and some into the having statement, thanks for pointing me
in the right direction!
On Behalf Of Gareth.cole
Sent: 16 October 2009 13:21
To: scottishcfug@googlegroups.com
Subject: [SCFUG] Re: Pure SQL or dtype="query" anyone?
Hi Shaun,
You might want to try re-writing the first query using GROUP BY to get the
aggregate information. Some of the filters would go in the WHERE statement,
and some in a HAVING statement.
Been a while since I've used HAVING, so I'm not 100% sure this will work
--------- Original Message --------
From: scottishcfug@googlegroups.com
To: scottishcfug@googlegroups.com <scottishcfug@googlegroups.com>
Subject: [SCFUG] Pure SQL or dtype="query" anyone?
Date: Oct 16, 2009 02:12 PM
> Hi
> all
>
> Hope everyone's
> looking forward to the weekend. I certainly am! >
> I asked a few weeks
> ago if anyone had seen a coldfusion database query tool. I > found
a > plugin for ExtJS which basically creates the WHERE statement from the
choices > you make (CherryExt). I've ported the backend code to Coldfusion but > am
having a > few problems with the SQL. Some the SQL has subqueries for counting > rows
etc. If > I pass in a SQL filter of "materialsent = 1" into the first query > it obviously fails, so I got around it by then querying a query. It > means I
have to > load the whole recordset in the first query (very slow with large
recordsets) > and also qoq do not allow me to use the limit command. Does anyone > have
any > suggestions of how I could do this better or point me in the right
direction, > SQL isn't my strongest skill!
>
> <cfquery name="returnData" > datasource="#application.settings.datasource#"> SELECT
> tbl_organisations.id, tbl_organisations.name, > tbl_organisations.datecreated, (SELECT
> COUNT(*) FROM > lnk_materials WHERE > tbl_organisations.id = lnk_materials.orgId) AS materialsent FROM > tbl_organisations</cfquery> <cfquery name="returnData"
> dbtype="query"> SELECT * FROM returnData WHERE > #getSQL(filters)#</cfquery> Kind regards Shaun Perry > Wiredmonkey +44 (0)131 2080811+44 (0)7921
121021www.wiredmonkey.co.ukwww.wiredmonkey.co.uk/blog
> This e-mail is confidential and should not be used by anyone who is > not the original intended recipient. If you have received
this > e-mail in error please inform the sender and delete it from your > mailbox
or any > other storage mechanism. Wiredmonkey cannot accept liability for any
statements > made which may be the sender's own and not expressly made on behalf of > Wiredmonkey. Electronic mail is not a secure form of communication and > can
be > subject to interference.
>
And unless stuff has change since I left the coding arena full time -
you should also avoid using Select (*) - again this may have been all
improved over the years?
Sent from my iPhone
On 16 Oct 2009, at 13:12, "Shaun Perry" <sh...@wiredmonkey.co.uk> wrote:
> Hope everyone's looking forward to the weekend. I certainly am!
> I asked a few weeks ago if anyone had seen a coldfusion database
> query tool. I found a plugin for ExtJS which basically creates the
> WHERE statement from the choices you make (CherryExt). I've ported
> the backend code to Coldfusion but am having a few problems with the
> SQL. Some the SQL has subqueries for counting rows etc. If I pass in
> a SQL filter of "materialsent = 1" into the first query it obviously
> fails, so I got around it by then querying a query. It means I have
> to load the whole recordset in the first query (very slow with large
> recordsets) and also qoq do not allow me to use the limit command.
> Does anyone have any suggestions of how I could do this better or
> point me in the right direction, SQL isn't my strongest skill!
> <cfquery name="returnData"
> datasource="#application.settings.datasource#">
> SELECT tbl_organisations.id, tbl_organisations.name,
> tbl_organisations.datecreated,
> (SELECT COUNT(*)
> FROM lnk_materials
> WHERE tbl_organisations.id = lnk_materials.orgId) AS
> materialsent
> FROM tbl_organisations
> </cfquery>
> <cfquery name="returnData" dbtype="query">
> SELECT *
> FROM returnData
> WHERE #getSQL(filters)#
> </cfquery>
> This e-mail is confidential and should not be used by anyone who is
> not the original intended recipient. If you have received this e- > mail in error please inform the sender and delete it from your
> mailbox or any other storage mechanism. Wiredmonkey cannot accept
> liability for any statements made which may be the sender's own and
> not expressly made on behalf of Wiredmonkey. Electronic mail is not
> a secure form of communication and can be subject to interference.
And unless stuff has change since I left the coding arena full time - you should also avoid using Select (*) - again this may have been all improved over the years?
Sent from my iPhone
On 16 Oct 2009, at 13:12, "Shaun Perry" <sh...@wiredmonkey.co.uk> wrote: > Hi all > > Hope ever...
|
