SharePoint Search SQL: search in location and below (dynamic)

Question

I have a client application that represents the complete SharePoint structure (sitecollections, sites & subsites, doclibs, folders), of any given 3rd pary SharePoint site, as a navigation tree.

Users can click on any level in that tree, and do a freetext search. They expect their search results to originate in either the selected location or below. For instance,

if they click on a site, they only want results from within that site.

if they click on a folder, they only want results from within that folder and any subfolders.

I'm trying to accomplish this using a restriction on the PATH property. However, whenever I include a LIKE predicate on the PATH the search results turn up empty. What am I doing wrong?

SharePoint Search SQL:

SELECT 
  URL,Path,FileName,Version,Size,LastModifiedTime,DocID 
FROM 
  Scope() 
WHERE 
  ContentClass='STS_ListItem_DocumentLibrary' AND 
  Path LIKE 'http://servername/doclib001/%' AND 
  FREETEXT(DEFAULTPROPERTIES, 'test' )

(SharePoint2010)

why do you need custom solution when your requirment can be met using Out of box features — Ashutosh Singh-MVP SharePoint
How many characters are actually in 'servername/doclib001/%'? (I ask because it looks like it was redacted and you may be running into the 64 character limit) — Kit Menke
@Ashutosh Singh: I'm integrating SharePoint access into existing client applications. I'm not sure how OOTB features are going to solve my problems. — Paul-Jan
@Kit Menke: Good catch. I was within the limit here, but I didn't take string length into account. — Paul-Jan

Paul-Jan Paul-Jan · Accepted Answer · 2010-12-01T19:23:23

Querying the Path never seems to yield any results. However, querying the Site with the equals predicate operator does exactly what I want, i.e.

SELECT  
  URL,Path,FileName,Version,Size,LastModifiedTime,DocID  
FROM  
  Scope()  
WHERE  
  ContentClass='STS_ListItem_DocumentLibrary' AND  
  Site = 'http://servername/doclib001' AND  
  FREETEXT(DEFAULTPROPERTIES, 'test' )

yields results from doclib001 als well as doclib001/folder001 and below. Which is rather unexpected, given that (a) Site suggests site/web, not arbitrary URL and (b) I'm using an exact equal operator, not a like/contains... but it works nicely, so I'm not complaining!

Non-recursive searches seem to be rather impossible thus far.

SharePoint Search SQL: search in location and below (dynamic)

1 Answers