Skip to content

Release 2.8.2
Compare
Choose a tag to compare
@andrei-markeev andrei-markeev released this 18 Apr 20:18
· 44 commits to master since this release
2.8.2
87269cd

Now it is possible to create nested joins.

For this purpose, optional 3rd parameter fromList of LeftJoin/InnerJoin can be used:

/** Join the list you're querying with another list.
    Joins are only allowed through a lookup field relation.
    @param lookupFieldInternalName Internal name of the lookup field, that points to the list you're going to join in.
    @param alias Alias for the joined list
    @param fromList (optional) List where the lookup column resides - use it only for nested joins */
InnerJoin(lookupFieldInternalName: string, alias: string, fromList?: string): IJoin;

/** Join the list you're querying with another list.
    Joins are only allowed through a lookup field relation.
    @param lookupFieldInternalName Internal name of the lookup field, that points to the list you're going to join in.
    @param alias Alias for the joined list
    @param fromList (optional) List where the lookup column resides - use it only for nested joins */
LeftJoin(lookupFieldInternalName: string, alias: string, fromList?: string): IJoin;

For example, let's say we have 3 SharePoint lists:

Orders
Title
Description
Amount
Customer (lookup to Customers -> Title)

Customers
Title
City (lookup to Cities -> Title)

Cities
Title

Then, we can extend Orders with City of the customer using the following CamlJs query:

var query = new CamlBuilder().View(["Title", "City"])
	.LeftJoin("Customer", "customersList")
	.LeftJoin("City", "citiesList", "customersList")
		.Select("Title", "City")
	.Query()
	.ToString();

Result:

<View>
    <ViewFields>
        <FieldRef Name="Title" />
        <FieldRef Name="City" />
    </ViewFields>
    <Joins>
        <Join Type="LEFT" ListAlias="customersList">
            <Eq>
                <FieldRef Name="Customer" RefType="ID" />
                <FieldRef Name="ID" List="customersList" />
            </Eq>
        </Join>
        <Join Type="LEFT" ListAlias="citiesList">
            <Eq>
                <FieldRef Name="City" RefType="ID" List="customersList" />
                <FieldRef Name="ID" List="citiesList" />
            </Eq>
        </Join>
    </Joins>
    <ProjectedFields>
        <Field ShowField="Title" Type="Lookup" Name="City" List="citiesList" />
    </ProjectedFields>
    <Query />
</View>
Assets 4