I recently have apllied the same scenario of two collections. (File and
Content)
Please review the following code and evaluate if this can help you.
(NOTE: the collections are also documented by a database architecture;
hence, the example here provides the utilization of both collections and
their respective database tables)
<CFSET ContentCollection = "ContentIndex">
<!--- retrieve content--->
<CFSEARCH
name = "GetContentResults"
collection = "#ContentCollection#"
criteria = "#client.Scriteria#"
>
<CFIF GetContentResults.RecordCount gt 0>
<CFSET ResultList = ValueList(GetContentResults.Key)>
<CFELSE>
<CFSET ResultList = 0>
</CFIF>
<CFSET DocDirectory = "d:\Docs\">
<CFSET DocCollection = "documentindex">
<!--- retrieve requested files --->
<CFSEARCH
name = "GetDocResults"
collection = "#DocCollection#"
criteria = "#client.Scriteria#"
>
<!--- my documents have a known key where filename looks like this ....
#########_filename.foo--->
<CFIF GetDocResults.RecordCount gt 0>
<CFOUTPUT QUERY="GetDocResults">
<CFIF CurrentRow is 1>
<CFSET FileList =
Left(GetFileFromPath(ListFirst(ValueList(GetDocResults.Key))), 9)>
<CFELSE>
<CFSET FileList = FileList&", "
&Left(GetFileFromPath(ListGetAt(ValueList(GetDocResults.Key),
#CurrentRow#)), 9)>
</CFIF>
</CFOUTPUT>
<CFELSE>
<CFSET FileList = 0>
</CFIF>
<!--- perform a union on the database tables to retrieve information that
makes sense--->
<CFQUERY NAME="GetResults" DATASOURCE="#DSN#">
SELECT
dv.FileID FID,
null ContentID,
d.startdate StartDate,
d.title title,
d.status status,
i.identifier section,
null mapping,
dt.description docType,
dv.size size,
dt.img img
FROM docversion dv, documents d, doctypes dt, identifiers i
WHERE dv.FileID IN (#fileList#)
AND d.status < '100'
AND d.docid = dv.docid
AND dt.doctypeID = d.doctypeID
AND d.identifierid = i.identifierid
UNION ALL
SELECT
null FID,
c.contentid ContentID,
c.startdate StartDate,
c.title title,
c.status status,
i.identifier section,
i.mapping mapping,
null docType,
null size,
null img
FROM ContentItems c, ContentIdentifiers ci, identifiers i
WHERE
c.status < '100'
AND c.contentid IN (#ResultList#)
AND c.contentid = ci.contentid
AND ci.identifierid = i.identifierid
ORDER BY
<!--- optional... use client variable to sort results--->
<CFPARAM NAME="client.ssortby" DEFAULT="0">
<CFIF IsDefined("url.sortby")>
<CFSET sortby = url.sortby>
<CFSET Client.sSortBy = sortby>
<CFELSE>
<CFIF Client.SSortBy is "0">
<CFSET sortby = 0>
<CFELSE>
<CFSET sortby = Client.SSortBy>
</CFIF>
</CFIF>
<CFSWITCH EXPRESSION=#sortby#>
<CFCASE VALUE="dateasc">
StartDate asc, title
<CFSET WALDOSORT = "date">
<CFSET ARROW = "U">
<CFSET SORTING = "desc">
</CFCASE>
<CFCASE VALUE="datedesc">
StartDate desc, title
<CFSET WALDOSORT = "date">
<CFSET ARROW = "D">
<CFSET SORTING = "asc">
</CFCASE>
<CFCASE VALUE="sectionasc">
section, startdate desc
<CFSET WALDOSORT = "section">
<CFSET ARROW = "U">
<CFSET SORTING = "desc">
</CFCASE>
<CFCASE VALUE="sectiondesc">
section desc, startdate desc
<CFSET WALDOSORT = "section">
<CFSET ARROW = "D">
<CFSET SORTING = "asc">
</CFCASE>
<CFDEFAULTCASE>
StartDate desc, title
<CFSET WALDOSORT = "date">
<CFSET ARROW = "U">
<CFSET SORTING = "asc">
</CFDEFAULTCASE>
</CFSWITCH>
</CFQUERY>
<!--- output in table format.... analyze results of union and display
links, images, titles accordingly --->
<TABLE WIDTH="100%" cellpadding="4" cellspacing="0" border="1">
<CFOUTPUT>
<TR>
<TD CLASS="TblTxtSm" width="2"> </TD>
<TD CLASS="TblTxtSm">Title</TD>
<CFIF WALDOSORT is "section">
<TD CLASS="TblTxtHead">
<a href="./start.htm?section=results&sortby=section#sorting#"
class="LinkWhite">Section <IMG SRC="../images/icn_arrow#arrow#.gif"
BORDER="0" ALIGN="ABSMIDDLE"></A>
</TD>
<CFELSE>
<TD CLASS="TblTxtSm"><a href
="./start.htm?section=results&sortby=sectionasc" style="text-decoration:
none;"
class="LinkBlue">Section</TD>
</CFIF>
<CFIF WALDOSORT is "date">
<TD CLASS="TblTxtHead">
<a href="./start.htm?section=results&sortby=date#sorting#" class
="LinkWhite">Date <IMG SRC="../images/icn_arrow#arrow#.gif"
BORDER="0" ALIGN="ABSMIDDLE"></A>
</TD>
<CFELSE>
<TD CLASS="TblTxtSm"><a href
="./start.htm?section=results&sortby=datedesc" style="text-decoration:
none;" class="LinkBlue">Date</TD>
</CFIF>
</TR>
</CFOUTPUT>
<CFOUTPUT QUERY="GetResults" STARTROW=#url.showrow# MAXROWS
=#DisplayRowsBy#>
<TR VALIGN="top">
<CFIF Trim(FID) gt 0>
<TD class="TblTxtSm" width="2">#currentrow#</TD>
<TD CLASS="TblTxtSm">
<a href="#localdomain#?section=download&fileid=#FID#" style
="text-decoration: none;" class="LinkBlue">
<img src="../images/#img#" alt="View" border="0" align="LEFT">
#title#</a>
<CFIF status is 0> <FONT class
="TblTxtSmGhost">« archived »</FONT></CFIF>
<BR>
<FONT class="TblTxtSmGhost">#docType# [<CFSET ksize = size /
1000>#ROUND(ksize)# KB]</FONT>
</TD>
<TD CLASS="TblTxtSm" nowrap>#section#</TD>
<TD CLASS="TblTxtSm" nowrap>#DateFormat(startdate, fdate)
# </TD>
<CFELSE>
<TD class="TblTxtSm" width="2">#currentrow#</TD>
<TD CLASS="TblTxtSm">
<A HREF="./#Mapping#&contentid=#contentid#" style
="text-decoration: none;" class="LinkBlue">
<IMG SRC="../images/icn_read.gif" ALT="View" BORDER="0" ALIGN
="texttop"> #title#</a>
<CFIF status is 0>
<FONT class="TblTxtSmGhost">« archived
»</FONT></CFIF></TD>
<TD CLASS="TblTxtSm" nowrap>#section#</TD>
<TD CLASS="TblTxtSm" nowrap>#DateFormat(startdate, fdate)#</TD>
</CFIF>
</TR>
</CFOUTPUT>
</TABLE>
Good Luck,
Robert Mann
Senior Web Specialist
Kaiser Permanente
Drug Information
______________________________________________________________________
Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
