Hi Matt,
Yeah, I was referring to doing it with the API. I'm still having trouble though. Any time I try to use the relationship="openGrouping" or relationship="closeGrouping" to group my query logic, I get SQL errors. So I'm probably not completely grokking how to correctly do this. (I'm using the latest Mura version from SVN as of May 25).
Basically, I'm putting together an event calendar, where an "Event" is an subtype of page with extended attributes and I'm trying to put together a search plugin for it. So here is what I've got:
<cfset feed=application.feedManager.read('') />
<cfset feed.setSiteID(request.siteid) />
<cfif len(request.categoryID)>
<cfset feed.setCategoryID(request.categoryID) />
</cfif>
<cfset feed.setSortBy('displayStart') />
<cfset feed.setSortDirection("asc") />
<cfset feed.addAdvancedParam(relationship="AND", field='tcontent.subType', criteria="Event", dataType='varchar') />
<cfif len(request.keywords)>
<cfset feed.addAdvancedParam(relationship='openGrouping')>
<cfloop list="#request.keywords#" index="keyword">
<cfset feed.addAdvancedParam(relationship="OR", field='tcontent.title', criteria=keyword, condition='CONTAINS', dataType='varchar')>
<cfset feed.addAdvancedParam(relationship='OR', field='tcontent.body', criteria=keyword, condition='CONTAINS', dataType='varchar')>
<cfset feed.addAdvancedParam(relationship='OR', field='tcontent.summary', criteria=keyword, condition='CONTAINS', dataType='varchar')>
<cfset feed.addAdvancedParam(relationship='OR', field='venue', criteria=keyword, condition='CONTAINS', dataType='varchar')>
<cfset feed.addAdvancedParam(relationship='OR', field='address1', criteria=keyword, condition='CONTAINS', dataType='varchar')>
<cfset feed.addAdvancedParam(relationship='OR', field='address2', criteria=keyword, condition='CONTAINS', dataType='varchar')>
<cfset feed.addAdvancedParam(relationship='OR', field='city', criteria=keyword, condition='CONTAINS', dataType='varchar')>
</cfloop>
<cfset feed.addAdvancedParam(relationship="closeGrouping") />
</cfif>
<cfif len(request.eventTitle)>
<cfset feed.addAdvancedParam(relationship="AND", field='tcontent.title',criteria=request.eventTitle,condition='contains',datatype="varchar") />
</cfif>
<cfif len(request.venue)>
<cfset feed.addAdvancedParam(relationship="AND", field='venue',criteria=request.venue,condition='contains',datatype="varchar") />
</cfif>
<cfif len(request.zipCode) and not len(request.zipDistance)>
<cfset feed.addAdvancedParam(relationship="AND", field='zip',criteria=request.zipCode,condition='equals',datatype="varchar") />
</cfif>
<cfif len(request.zipCode) and len(request.zipDistance)>
<cfset zipCodes = getZipCodesByDistance(request.zipCode,request.zipDistance)>
<cfset feed.addAdvancedParam(field='zip',criteria=preserveSingleQuotes(zipCodes),condition='in',datatype="varchar") />
</cfif>
<cfset rs=application.feedManager.getFeed(feed)>
Basically, the "keywords" field in the search form can take a comma-delimited list of keywords and I want to loop through that list and get all Events where any of the keywords appear in any of the Events' attributes (including extended ones). I then want to be able to further filter the search via field-specific search filters, if any of those fields were filled out (title, venue, zip code, distance from zip code).
If I type in, for example, "music, sports" in the keyword field, I get an SQL error and here is a snippet of the generated SQL:
...AND tcontent.siteid = 'njgg' and ( tcontent.subType = 'Event' ( tcontent.title like '%sports%' OR tcontent.body like '%sports%'...
As you can see, the problem is that there is no 'AND' between 'Event' and the opening parenthesis, and I'm not sure how to correct that. (putting another grouping around the 'Event' criteria doesn't help).
So at this point, I'm stumped. Any help you (or anyone) could offer would be much appreciated.
Thanks.
________________
objectivebias.com
