Complex queries

[dlid]

Complex groups will break

SELECT * FROM [Decisions] WHERE [Title] LIKE ? AND (([Motivation] IS NULL AND [IsBoss] = ?) or (P = ? AND P2 < ?))

[dlid]

Yes, quite broken... This is the generated xml

<View>
    <Query>
        <Where>
            <And>
                <Contains>
                    <FieldRef Name="Title" />
                    <Value Type="Text">summer</Value>
                </Contains>
                <And>
                    <IsNull>
                        <FieldRef Name="Motivation" />
                    </IsNull>
                    <Or>
                        <Eq>
                            <FieldRef Name="IsBoss" />
                            <Value Type="Text">did</Value>
                        </Eq>
                        <And>
                            <Eq>
                                <FieldRef Name="P" />
                                <Value Type="Number">10</Value>
                            </Eq>
                            <Lt>
                                <FieldRef Name="P2" />
                                <Value Type="Number">6</Value>
                            </Lt>
                        </And>
                    </Or>
                </And>
            </And>
        </Where>
    </Query>
</View>

There should be an OR in there... I have to think about what this should look like when it's not so late...

[dlid]

Hm, I think it should actually look like this...

<View>
    <Query>
        <Where>
            <And>
                <Contains>
                    <FieldRef Name="Title" />
                    <Value Type="Text">The title</Value>
                </Contains>
                <Or>
                    <And>
                        <IsNull>
                            <FieldRef Name="Motivation" />
                        </IsNull>
                        <Eq>
                            <FieldRef Name="IsBoss" />
                            <Value Type="Text">He-Man</Value>
                        </Eq>
                    </And>
                    <And>
                        <Eq>
                            <FieldRef Name="P" />
                            <Value Type="Number">1000</Value>
                        </Eq>
                        <Lt>
                            <FieldRef Name="P2" />
                            <Value Type="Number">3500</Value>
                        </Lt>
                    </And>
                </Or>
            </And>
        </Where>
    </Query>
</View>

This result is from removing the () from the second part of the where statement:

camlsql.prepare('SELECT * FROM [Decisions] WHERE [Title] LIKE ? AND ([Motivation] IS NULL AND [IsBoss] = ?) or (P = ? AND P2 < ?)', ['The title', 'He-Man', 1000, 3500])