Reputation: 11
This CF query does what I want but I'd like to show only one occurrence of each matching "HEADLINE" that has a count greater than 5. I don't need to display the actual count, just the headline which will be a link using that headline as a url variable. And there must be a more efficient way for my code to work?
<cfquery name="HeadInfo" datasource="certify">
select headline
from post
where deleted = 0
and headline IS NOT NULL
order by altid desc
</cfquery>
<cfoutput>#HeadInfo.RecordCount#</cfoutput>
<cfoutput query="HeadInfo">
<cfquery name="CountInfo" datasource="certify">
select *
from post
where deleted = 0
and headline = '#HeadInfo.Headline#'
order by headline desc
</cfquery>
<cfif CountInfo.RecordCount GT 5>
#HeadInfo.headline# - Count:#CountInfo.RecordCount#<br>
</cfif>
</cfoutput>
Upvotes: 1
Views: 102
Reputation: 1138
Let's assume you have a MySQL or MariaDB table named post with the following structure:
CREATE TABLE IF NOT EXISTS `post` (
`altid` int(11) NOT NULL AUTO_INCREMENT,
`headline` varchar(50) DEFAULT NULL,
`post` varchar(255) DEFAULT NULL,
`deleted` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`altid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4;
Populated with some test values like these:
INSERT INTO `post` (`altid`, `headline`, `post`, `deleted`) VALUES
(1, 'headline1', 'post headline1 is foo', 0),
(2, 'headline1', 'post headline1 is bar', 0),
(3, 'headline1', 'post headline1 is foobar', 0),
(4, 'headline1', 'post headline1 is contoso', 0),
(5, 'headline1', 'post headline1 is contoso foo', 0),
(6, 'headline1', 'post headline1 is contoso bar', 0),
(7, 'headline2', 'post headline2 is foo is deleted', 1),
(8, 'headline2', 'post headline2 is bar', 0),
(9, 'headline2', 'post headline2 is barFoo', 0),
(10, 'headline2', 'post headline2 is barFoo contoso', 0),
(11, 'headline2', 'post headline2 is foo contoso', 0),
(12, 'headline2', 'post headline2 is contoso bar', 0),
(13, NULL, 'post headline3 is bar with NULL headline', 0),
(14, 'headline2', 'post headline2 of another post', 0),
(15, 'headline2', 'post headline2 of another foobar post', 0),
(16, 'headline3', 'post headline3 some post for count<5', 0);
You could achieve this even without a JOIN
as some have suggested, because it's the same table and this kind of SELECT won't need to have the table JOINED to itself.
In case you need to output the headline recordcount of all headlines (including those < 5), but output only the hits with counts > 5, do it with just one SELECT like follows:
<cfquery name="HeadInfo" datasource="certify">
SELECT headline, count( headline ) as headlineCount -- aggregate function count()
FROM post
WHERE deleted = 0
and headline IS NOT NULL
GROUP BY headline -- group by for aggregate count() function
ORDER BY altid desc
</cfquery>
<cfoutput>
Total of headlines #HeadInfo.recordcount#<br>
<cfloop query="HeadInfo">
<cfif HeadInfo.headlineCount GT 5>
#HeadInfo.headline# - Count:#HeadInfo.headlineCount#<br>
</cfif>
</cfloop>
</cfoutput>
The output is:
Total of headlines 3
headline2 - Count:7
headline1 - Count:6
But if the recordcount of all headlines isn't needed, then use the aggregate SQL function count()
with GROUP BY
using the HAVING
clause to select only the headlines with count > 5 already in SQL. That will reduce payload inbetween the DB connection with the CF Engine (please see my SQL-Comments inbetween the lines):
<cfquery name="HeadInfo" datasource="certify">
SELECT headline, count( headline ) as headlineCount -- aggregate function count()
FROM post
WHERE deleted = 0
and headline IS NOT NULL
GROUP BY headline -- group by for aggregate count() function
HAVING count( headline ) > 5 -- get headlines with more than 5 posts only (aggregate function count)
ORDER BY altid desc
</cfquery>
<cfoutput>
<cfloop query="HeadInfo">
#HeadInfo.headline# - Count:#HeadInfo.headlineCount#<br>
</cfloop>
</cfoutput>
The output is:
headline2 - Count:7
headline1 - Count:6
Upvotes: 4
Reputation: 7066
You need to do it in one query.
<cfquery name="HeadInfo" datasource="headlines">
SELECT P.headline
FROM post as P
INNER JOIN (
SELECT headline
FROM post
WHERE deleted = 0
GROUP BY headline
HAVING COUNT(headline) > 5
) AS PC
ON P.headline = PC.headline
WHERE P.deleted = 0
AND P.headline IS NOT NULL
ORDER BY altid DESC
</cfquery>
<ul>
<cfoutput query="HeadInfo">
<li>#HeadInfo.headline#</li>
</cfoutput>
</ul>
Upvotes: 2