Skip to main content

Cloud

“OR” logic in fulltextsqlquery

In some instances, the OR logic in fulltextsqlquery works as an AND which could be very confusing when trying to build queries. For example, let’s say you have two managed properties that are both mapped to two different crawled properties. Now if you execute a query and search for a term that is available in one item and that this item meets one of the filtering criteria set up in your OR logic but not the other one then you will not get no results back (!). Suddenly your OR looks and behaves more like an AND. So basically, the item needs to have the two crawled properties in order to be filtered by both managed properties in order to get captured as a result. Here is a quick example:

Crawled Properties

Item #1

Item #2

ArticleTitle

Title1

Title2

ArticleAuthor

Bob

Billy

ContentType

Doc

Doc

Query #1:
WHERE ContentType="Doc" AND (ArticleTitle="Title1" OR ArticleAuthor="Billy")

Results #1: Item #1 and Item #2 get returned.

Now let’s consider the following data:

Crawled Properties

Item #1

Item #2

ArticleTitle

Title1

Title2

ArticleAuthor

Bob

Billy

ArticlePrice

14

Doesn’t exist

ContentType

Doc

Doc

Query #2:
WHERE ContentType="Doc" AND (ArticleTitle="Title1" OR ArticlePrice="30")

Results #2: No results.

This will return no results though Item #1 should be returned.

One work around is to actually restate all conditions for each criteria in your query to obtain the correct results as show in this query:

Query #3:
WHERE (Content type="Doc" AND ArticleTitle =" Title1") OR (Content type="Doc" AND ArticlePrice ="30")

Results #3: Item #1 get returned.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

PointBridge Blogs

More from this Author

Follow Us
TwitterLinkedinFacebookYoutubeInstagram