1
Vote

Get extra And in resultant query

description

I am trying to do use conditional logic to register multiple And and Or statements. For the most part this working well except when the FilterBy if evaluates to true. In that scenario I get an extra And in the CAML query that causes the query to fail. If there is a better way to go about what I am trying to accomplish I would appreciate the input.
            var camlB = new CamlBuilder();
            var caml;

            if (PersonalizationScope && PersonalizationScope.length > 0) {
                caml = camlB.Where()
               .TextField("ServiceLocation").In(PersonalizationScope);
            }
            else{
                caml = camlB.Where()
                .TextField("ServiceLocation").IsNotNull(PersonalizationScope);

            }

            if (FilterBy > 0) {
               caml.And()
               .LookupField("CategoryLookup").Id().EqualTo(FilterBy);
            }

            if (searchTerm) {
                if (contentType == FEATURED_CONTENT_CONSTANTS.NewsContentType) {
                    caml.And()
                    .TextField('FullNewsStory').Contains(searchTerm)
                    .Or()
                    .TextField('PreviewDescription').Contains(searchTerm)
                    .Or()
                    .TextField('Title').Contains(searchTerm);
                }
                else if (contentType == fea.FeaturedContentType) {
                    caml.And()
                    .TextField('Title').Contains(searchTerm)
                    .Or()
                    .TextField('PreviewDescription').Contains(searchTerm)
                }
            }

            whereCondition = caml.ToString();
This generates the following XML when FilterBy evaluates to false. This is valid CAML.
        <Where>
            <And>
                <In>
                    <FieldRef Name="Test" />
                    <Values>
                        <Value Type="Text">Atlanta</Value>
                        <Value Type="Text">North & UK</Value>
                    </Values>
                </In>
                <Or>
                    <Contains>
                        <FieldRef Name="FullNewsStory" />
                        <Value Type="Text">dui</Value>
                    </Contains>
                    <Or>
                        <Contains>
                            <FieldRef Name="PreviewDescription" />
                            <Value Type="Text">dui</Value>
                        </Contains>
                        <Contains>
                            <FieldRef Name="Title" />
                            <Value Type="Text">dui</Value>
                        </Contains>
                    </Or>
                </Or>
            </And>
        </Where>
This is the XML that is generated by FilterBy evaluates to true. Notice the extra And in the beginning of the Where. This is invalid CAML.
        <Where>
            <And>
                <And>
                    <In>
                        <FieldRef Name="Test" />
                        <Values>
                            <Value Type="Text">Atlanta</Value>
                            <Value Type="Text">North & UK</Value>
                        </Values>
                    </In>
                    <Eq>
                        <FieldRef Name="CategoryLookup" LookupId="True" />
                        <Value Type="Integer">5</Value>
                    </Eq>
                    <Or>
                        <Contains>
                            <FieldRef Name="FullNewsStory" />
                            <Value Type="Text">du</Value>
                        </Contains>
                        <Or>
                            <Contains>
                                <FieldRef Name="PreviewDescription" />
                                <Value Type="Text">du</Value>
                            </Contains>
                            <Contains>
                                <FieldRef Name="Title" />
                                <Value Type="Text">du</Value>
                            </Contains>
                        </Or>
                    </Or>
                </And>
            </And>
        </Where>
Side note, if I add the And() function call so that it is part of the first If statement then the CAML is generated correctly. I suspect that whatever is being returned by the .Where() is somehow different than what is returned by the And() and that is what causes the issue.

comments

omlin wrote May 2, 2016 at 6:06 PM

I'd recommend using All and Any and CamlBuilder.Expression for composing dynamic queries. This will simplify the code and make it more visual.
var PersonalizationScope = [];
var FilterBy = 1;
var searchTerm = 'something';
var FEATURED_CONTENT_CONSTANTS = { NewsContentType: 'test' };
var contentType = 'test';
var fea = { FeaturedContentType: 'testF' };
    
var conditions = [];
if (PersonalizationScope && PersonalizationScope.length > 0)
  conditions.push(CamlBuilder.Expression().TextField("ServiceLocation").In(PersonalizationScope));
else
  conditions.push(CamlBuilder.Expression().TextField("ServiceLocation").IsNotNull());

if (FilterBy > 0)
  conditions.push(CamlBuilder.Expression().LookupField("CategoryLookup").Id().EqualTo(FilterBy));

if (searchTerm) {
  if (contentType == FEATURED_CONTENT_CONSTANTS.NewsContentType) {
     conditions.push(CamlBuilder.Expression()
                    .TextField('FullNewsStory').Contains(searchTerm)
                    .Or()
                    .TextField('PreviewDescription').Contains(searchTerm)
                    .Or()
                    .TextField('Title').Contains(searchTerm));
  }
  else if (contentType == fea.FeaturedContentType) {
     conditions.push(CamlBuilder.Expression()
                    .TextField('Title').Contains(searchTerm)
                    .Or()
                    .TextField('PreviewDescription').Contains(searchTerm));
  }
}

var query = new CamlBuilder().Where().All(conditions).ToString();
This generates the following:
<Where>
    <And>
        <IsNotNull>
            <FieldRef Name="ServiceLocation" />
        </IsNotNull>
        <And>
            <Eq>
                <FieldRef Name="CategoryLookup" LookupId="True" />
                <Value Type="Integer">1</Value>
            </Eq>
            <Or>
                <Contains>
                    <FieldRef Name="FullNewsStory" />
                    <Value Type="Text">something</Value>
                </Contains>
                <Or>
                    <Contains>
                        <FieldRef Name="PreviewDescription" />
                        <Value Type="Text">something</Value>
                    </Contains>
                    <Contains>
                        <FieldRef Name="Title" />
                        <Value Type="Text">something</Value>
                    </Contains>
                </Or>
            </Or>
        </And>
    </And>
</Where>
Also there's a couple mistakes in how you use the CamlBuilder, in order to not to make those I really recommend using the CamlJs Console, you can save like hours of development time by using it.

lgoolsby wrote May 2, 2016 at 6:41 PM

Oh man, that is much easier! I didn't see an example of using this approach in the documentation, you should definitely add it! Super nice, and thanks a ton!