Frank, are you aware of the CFOUTPUT GROUP attribute? That would eliminate
the need to do the 2 queries below. Many miss it, though it's indeed a
valuable feature to create just these sort of "control break" reports. I'll
show an example in a moment.
And Michael, are you saying that you ARE trying to use the GROUP attribute
on CFOUTPUT, but you're wanting to limit the inner loop? Sadly, it does NOT
accept a Maxrows on it, so you can't do that, but I have another solution
you could use. (The answer is NOT adding a maxrows to the outer cfoutput
loop, as that instead limits how many of the outer loop values to show,
which is not what you want.)
First, here's a basic cfoutput group demo, for those not familiar with it.
It's a classic "control break" report, in this case produce a report, by
city, of the artists and their art, as found in the demo artgallery
datasource that comes with CF8 and above:
<cfquery datasource="cfartgallery" name="getArt">
SELECT *
FROM art a, artists t
WHERE a.artistID = t.artistID
ORDER BY city asc
</cfquery>
<cfoutput query="getArt" group="city">
<h2>#city#</h2>
<cfoutput>
#artname# (<em>by #firstname# #lastname#</em>)
#DollarFormat(price)#<br>
</cfoutput>
</cfoutput>
The output (first two groups) would look like this:
Atlanta
Beauty (by Raquel Young) $100,000.00
Mom (by Raquel Young) $85,000.00
Mountains (by Raquel Young) $150,000.00
Paradise (by Raquel Young) $300,000.00
Mystery (by Raquel Young) $250,000.00
Berkeley
Do it (by Austin Weber) $30,000.00
You Don't Know Me (by Austin Weber) $42,700.00
Prize Fight (by Austin Weber) $25,000.00
Toxic (by Austin Weber) $22,000.00
1958 (by Austin Weber) $75,000.00
But now let's say we wanted no more than 3 of these to be shown per city.
While we can't add maxrows to the inner loop (inner cfoutput), we can just
keep track of the count ourselves, and only show those we want.
<cfoutput query="getArt" group="city">
<h2>#city#</h2>
<cfset ct=0>
<cfoutput>
<cfif ct++ lt 3>
#artname# (<em>by #firstname# #lastname#</em>)
#DollarFormat(price)#<br>
</cfif>
</cfoutput>
</cfoutput>
Some may note that this DOES still loop over all the inner loop results, but
there's just no way to stop that. If this were a real CFLOOP, we could use
the cfbreak tag to "jump out of the inner loop", but it does not work in
CFOUTPUT loops (would be nice). Now, some may note that a change in CF10
allows CFLOOP query loops to use the GROUP attribute, so one could use the
CFBREAK then.
Let us know if this helps, Michael (and Frank).
/charlie
From: [email protected] [mailto:[email protected]] On Behalf Of Frank Moorman
Sent: Monday, July 23, 2012 6:07 PM
To: [email protected]
Cc: Michael Brown
Subject: Re: [ACFUG Discuss] Have a question about group limits
Michael,
I am not sure, but I think this is what you are looking for...
I do not believe that MySQL supports the LIMIT clause in a subquery, so I
believe the following is easiest without creating a stored procedure or a
unreadable monstrosity of SQL...
<cfquery name="groups" datasource="xxx">
SELECT DISTINCT dest
FROM tableB
WHERE top10 = "1";
</cfquery>
<cfloop query="groups">
<cfquery name="top10" datasource="xxx">
SELECT dest, property, url_e, price, description
FROM tableB
WHERE top10 = "1"
AND dest = <cfqueryparam value="#groups.dest#">
LIMIT 10
</cfquery>
<!--- Either print here, or combine in one large query, using QueryNew()
outside the loop and QueryAddRow() for each result --->
</cfloop>
You definitely want to use the CFQUERYPARAM in the second query. Even though
the dest field is coming straight from the database and probably does not
need validation, it turns the SQL into a prepared SQL for the database so
that MySQL will cache the execution plan and save a little time when it is
executed multiple times.
Let me know if that is what you wanted...
Frank
On 07/23/2012 05:28 PM, Michael Brown wrote:
Thanks Larry,
Sorry for not being clear. I want to limit the number records displayed
"per" group. In the database there are thousands of records, but I just
want to display 10 records per group. When I run the query, it only
displays 10 records for only 1 group in the "dest" field.
Hope that's a little clearer.
THANKS
On Mon, Jul 23, 2012 at 5:14 PM, Larry Morrow <[email protected]> wrote:
Hi Michael,
I may be the first and the least capable of helping, but I did not quite
understand what you are trying to accomplish.
Your query as is will list the results so that the records will be sorted by
the group.
When you say limit, can you provide a description of a results you are
looking to achieve.
Hopefully, I was clear in my question.
Larry J. Morrow
COO & CTO
A-PLUS Community Solutions, Inc.
Office: 678.534.8326 ext 100 <tel:678.534.8326%20ext%20100>
Fax: 801.843.5832
Cell: 678.416.8032
www.a-plus.net
Please Note: The information in this email contains privileged,
confidential, and proprietary information and is otherwise protected from
disclosure. If you have received this email in error, please notify A-PLUS
Community Solutions, Inc. immediately by replying to this message and
deleting it from your computer. Anyone other than the intended is prohibited
from reading, copying, or distributing this transmission.
From: [email protected] [mailto:[email protected]] On Behalf Of Michael Brown
Sent: Monday, July 23, 2012 5:05 PM
To: [email protected]
Subject: [ACFUG Discuss] Have a question about group limits
Hello fellow group members,
I can't seem to get my output to limit the number of records displayed per
group in the database. I using mysql with cf9. This is my query that I'm
attempting to get data from. The group field is "dest".
<cfquery name="GetData" datasource="xxx_mysql" >
select dest, property, url_e, price, description
from tableB
where top10 = "1"
order by dest
</cfquery>
Thanks!
--
Michael Brown
770-605-5240
Marketing Solutions with vision!
http://www.singleconcepts.com
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 7323 (20120723) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink <http://www.fusionlink.com>
-------------------------------------------------------------
--
Michael Brown
770-605-5240
<http://singleconcepts.com/images/newlogo2011_email.jpg>
Marketing Solutions with vision!
http://www.singleconcepts.com
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
