How's about http://www.halestorm.co.uk/temp.cfm
with this code:
<cfscript>
q = QueryNew("Forum_ID,Thread_ID,Post_ID,Thread_Title,Post_Content");
QueryAddRow(q, 4);
QuerySetCell(q, "Forum_ID", "1", 1);
QuerySetCell(q, "Forum_ID", "1", 2);
QuerySetCell(q, "Forum_ID", "1", 3);
QuerySetCell(q, "Forum_ID", "2", 4);
QuerySetCell(q, "Thread_ID", "1", 1);
QuerySetCell(q, "Thread_ID", "2", 2);
QuerySetCell(q, "Thread_ID", "1", 3);
QuerySetCell(q, "Thread_ID", "3", 4);
QuerySetCell(q, "Post_ID", "1", 1);
QuerySetCell(q, "Post_ID", "2", 2);
QuerySetCell(q, "Post_ID", "3", 3);
QuerySetCell(q, "Post_ID", "4", 4);
QuerySetCell(q, "Thread_Title", "Thread ##1", 1);
QuerySetCell(q, "Thread_Title", "Thread ##2", 2);
QuerySetCell(q, "Thread_Title", "Thread ##1", 3);
QuerySetCell(q, "Thread_Title", "Thread ##3", 4);
QuerySetCell(q, "Post_Content", "This is a test", 1);
QuerySetCell(q, "Post_Content", "This is another test", 2);
QuerySetCell(q, "Post_Content", "Reply to 'this is a test'", 3);
QuerySetCell(q, "Post_Content", "test in a new 'forum' area", 4);
</cfscript>
<cfdump var="#q#">
<cfquery name="threads" dbtype="query">
SELECT * FROM q ORDER BY Forum_ID, Thread_ID
</cfquery>
<cfdump var="#threads#">
<cfoutput query="threads" group="Forum_ID">
Forum: #threads.Forum_ID#<br />
<cfoutput group="Thread_ID">
- - - - -#threads.Thread_Title#<br />
</cfoutput>
</cfoutput>
Ade
-----Original Message-----
From: Ram Wissel [mailto:[EMAIL PROTECTED]
Sent: 03 June 2005 14:32
To: CF-Newbie
Subject: RE: Distinct query of queries trouble
That makes sense on the DISTINCT trying to act across all the rows since I
was using an * to pull in data from the tables. Thanks for the order by
tip, nesting them like that should work for what I'm needing. But I'm still
confused on if I need to filter out the results so that thread's with
several search hits don't show multiple times. Does the GROUP command create
something like a ValueList of distinct values without repeats?
let me give you an example data set to try and explain:
Forum_ID Thread_ID Post_ID Thread_Title Post_Content
1 1 1 Thread #1 This is a test
1 2 2 Thread #2 This is another test
1 1 3 Thread #1 Reply to "this is a test"
2 3 4 Thread #3 test in a new 'forum'
area
If searching for 'test' I'd like to return the results:
Matches found: 4 in 3 threads
Forum "1":
Thread #1 (table with author, lastpost date, etc pulled from 'select *' in
search)
Thread #2 (similar table info..)
Forum "2":
Thread #3 (similar table info..)
I'll play around with trying to group the results as you described, but I
was under the impression I needed to somehow filter or comibine the common
thread_id's prior to displaying the group?
Thanks for the help.
>DISTINCT acts across all returned rows.
>
>A quick way to output what you want is to use the group attribute in
>cfoutput.
>
>
><CFQUERY NAME='keywordsearch' ...>
>SELECT (needed fields)
>FROM (proper tables)
>WHERE post_content LIKE'%#i#%'
>ORDER BY ThreadID
></CFQUERY>
>
><cfoutput query="keywordsearch" group="ThreadID">
> #keywordsearch.ThreadID#<br />
></cfoutput>
>
>
>If you then want to group by another column, forum_id, then try this.
>
>
><CFQUERY NAME='keywordsearch' ...>
>SELECT (needed fields)
>FROM (proper tables)
>WHERE post_content LIKE'%#i#%'
>ORDER BY FieldID, ThreadID
></CFQUERY>
>
><cfoutput query="keywordsearch" group="FieldID">
>
> <cfoutput group="ThreadID">
> #keywordsearch.ThreadID#<br />
> </cfoutput>
>
></cfoutput>
>
>
>The syntax is probably off with that last bit of code, have a play about
>with it and see what you come up with.
>
>Ade
>
>-----Original Message-----
>From: RammaR [mailto:[EMAIL PROTECTED]
>Sent: 03 June 2005 11:31
>To: CF-Newbie
>Subject: Distinct query of queries trouble
>
>
>I'm trying to generate a search results page for a simple forum type page.
>My problem is that I'm searching the post content for the inputed keywords
>and then return a distinct list of the threads because often there are
>mutliple search hits within a common thread. So what I've done is a main
>query for the search with then pulls in all of the user/thread/forum data.
>Then do a 'distinct' query of queries on those results to filter out the
>multiple thread listings. So i might get 15 hits in 10 threads, which is
>working fine. However when I go to display the results I'm having trouble
>using that distinct list, everything I'm trying seems to show all 15 with
>repeats instead of 10.
>
>for this example the query structure is like this:
>
><CFQUERY NAME='keywordsearch' ...>
>SELECT (needed fields)
>FROM (proper tables)
>WHERE post_content LIKE'%#i#%'
></CFQUERY>
>
><CFQUERY DBTYPE = "query" NAME="thread_count_query">
>SELECT DISTINCT thread_ids
>FROM keywordsearch
></CFQUERY>
>
><CFQUERY NAME="populate_results_list" DBTYPE="query"
>MAXROWS="#records_per_page#">
><trying to determine what fits here, originally:>
>SELECT DISTINCT unique_thread_ids, *
>FROM keywordsearch
></CFQUERY>
>
>Oh, and secondly I'm going to also want to group them by the forum_id field
>when displaying, but that should be a GROUP BY at the end of the query
>correct?
>
>Any tips or input would be greatly appreciated,
>thanks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:15:1084
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/15
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:15
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
