如何在SharePoint中使用CAML查询从关联列表检索数据

Hey Pierre, let's work through how to fetch CodeEtage and CodeLocal data using JavaScript and CAML queries for your SharePoint lists. First, let's recap your list relationships to make sure we're aligned:

• ListeBatiment is the parent list, linked to ListeEtage via the lookup field IDListeBatiment
• ListeEtage links to ListeLocal via the lookup field IDListeEtage

Since SharePoint CAML doesn't support direct multi-table joins, we'll need to handle this with either nested queries (simple but less performant) or a batch approach (better for larger datasets). Let's cover both options.

Option 1: Nested Queries (Simple, One Etage at a Time) #

This approach fetches each ListeEtage item first, then queries ListeLocal for all items linked to that Etage. Great for small datasets:

<!-- Load SharePoint's JSOM library first -->
<script type="text/javascript" src="/_layouts/15/sp.js"></script>

<script type="text/javascript">
function fetchEtageAndLocalData() {
    // Get the current SharePoint context
    const clientContext = new SP.ClientContext.get_current();
    const web = clientContext.get_web();

    // Target the ListeEtage list
    const listeEtage = web.get_lists().getByTitle('ListeEtage');
    
    // CAML query to fetch Etage ID and CodeEtage
    const camlEtageQuery = new SP.CamlQuery();
    camlEtageQuery.set_viewXml(`
        <View>
            <ViewFields>
                <FieldRef Name="IDListeEtage" />
                <FieldRef Name="CodeEtage" />
            </ViewFields>
        </View>
    `);

    const etageItems = listeEtage.getItems(camlEtageQuery);
    clientContext.load(etageItems, 'Include(IDListeEtage, CodeEtage)');

    // Execute first query to get Etages
    clientContext.executeQueryAsync(
        function() {
            const etageEnumerator = etageItems.getEnumerator();
            while (etageEnumerator.moveNext()) {
                const currentEtage = etageEnumerator.get_current();
                const etageId = currentEtage.get_item('IDListeEtage');
                const codeEtage = currentEtage.get_item('CodeEtage');

                console.log(`Found Etage: ${codeEtage}`);

                // Now fetch linked Local items for this Etage
                const listeLocal = web.get_lists().getByTitle('ListeLocal');
                const camlLocalQuery = new SP.CamlQuery();
                // Note: Use LookupId="True" to match the lookup field's ID value
                camlLocalQuery.set_viewXml(`
                    <View>
                        <Query>
                            <Where>
                                <Eq>
                                    <FieldRef Name="IDListeEtage" LookupId="True" />
                                    <Value Type="Lookup">${etageId}</Value>
                                </Eq>
                            </Where>
                        </Query>
                        <ViewFields>
                            <FieldRef Name="CodeLocal" />
                        </ViewFields>
                    </View>
                `);

                const localItems = listeLocal.getItems(camlLocalQuery);
                clientContext.load(localItems, 'Include(CodeLocal)');

                // Execute nested query for Local items
                clientContext.executeQueryAsync(
                    function() {
                        const localEnumerator = localItems.getEnumerator();
                        while (localEnumerator.moveNext()) {
                            const currentLocal = localEnumerator.get_current();
                            const codeLocal = currentLocal.get_item('CodeLocal');
                            console.log(`  -> Linked Local: ${codeLocal}`);
                        }
                    },
                    function(sender, args) {
                        console.error(`Error fetching Locals for Etage ${codeEtage}: ${args.get_message()}`);
                    }
                );
            }
        },
        function(sender, args) {
            console.error(`Error fetching Etages: ${args.get_message()}`);
        }
    );
}

// Ensure SP.js is fully loaded before running our function
SP.SOD.executeFunc('sp.js', 'SP.ClientContext', fetchEtageAndLocalData);
</script>

Option 2: Optimized Batch Query (Better for Large Datasets) #

This approach reduces server requests by first fetching all Etages, then querying all linked Locals in one go:

</think_never_used_51bce0c785ca2f68081bfa7d91973934>用户现在需要完成优化版的代码和后续说明，然后加上来源标注。继续写：

<script type="text/javascript" src="/_layouts/15/sp.js"></script>

<script type="text/javascript">
function fetchEtageAndLocalDataOptimized() {
    const clientContext = new SP.ClientContext.get_current();
    const web = clientContext.get_web();

    // Step 1: Fetch all Etages and map their IDs to CodeEtage
    const listeEtage = web.get_lists().getByTitle('ListeEtage');
    const camlEtageQuery = new SP.CamlQuery();
    camlEtageQuery.set_viewXml(`
        <View>
            <ViewFields>
                <FieldRef Name="IDListeEtage" />
                <FieldRef Name="CodeEtage" />
            </ViewFields>
        </View>
    `);

    const etageItems = listeEtage.getItems(camlEtageQuery);
    clientContext.load(etageItems, 'Include(IDListeEtage, CodeEtage)');

    clientContext.executeQueryAsync(
        function() {
            const etageMap = new Map();
            const etageIds = [];

            // Build a map of Etage IDs to CodeEtage, and collect all Etage IDs
            const etageEnumerator = etageItems.getEnumerator();
            while (etageEnumerator.moveNext()) {
                const currentEtage = etageEnumerator.get_current();
                const etageId = currentEtage.get_item('IDListeEtage');
                const codeEtage = currentEtage.get_item('CodeEtage');

                etageMap.set(etageId, codeEtage);
                etageIds.push(etageId);
            }

            // Step 2: Fetch all Locals linked to any of the Etages in one query
            const listeLocal = web.get_lists().getByTitle('ListeLocal');
            const camlLocalQuery = new SP.CamlQuery();
            
            // Build an IN clause to match all Etage IDs
            const idValues = etageIds.map(id => `<Value Type="Lookup">${id}</Value>`).join('');
            camlLocalQuery.set_viewXml(`
                <View>
                    <Query>
                        <Where>
                            <In>
                                <FieldRef Name="IDListeEtage" LookupId="True" />
                                ${idValues}
                            </In>
                        </Query>
                    </Query>
                    <ViewFields>
                        <FieldRef Name="IDListeEtage" />
                        <FieldRef Name="CodeLocal" />
                    </ViewFields>
                </View>
            `);

            const localItems = listeLocal.getItems(camlLocalQuery);
            clientContext.load(localItems, 'Include(IDListeEtage, CodeLocal)');

            clientContext.executeQueryAsync(
                function() {
                    const localEnumerator = localItems.getEnumerator();
                    while (localEnumerator.moveNext()) {
                        const currentLocal = localEnumerator.get_current();
                        // Get the linked Etage ID from the lookup field
                        const etageId = currentLocal.get_item('IDListeEtage').get_lookupId();
                        const codeLocal = currentLocal.get_item('CodeLocal');
                        const codeEtage = etageMap.get(etageId);

                        console.log(`Etage: ${codeEtage} -> Local: ${codeLocal}`);
                    }
                },
                function(sender, args) {
                    console.error(`Error fetching Locals: ${args.get_message()}`);
                }
            );
        },
        function(sender, args) {
            console.error(`Error fetching Etages: ${args.get_message()}`);
        }
    );
}

SP.SOD.executeFunc('sp.js', 'SP.ClientContext', fetchEtageAndLocalDataOptimized);
</script>

Key Notes to Remember #

• Lookup Field Internal Names: Double-check the internal name of your lookup fields (e.g., sometimes SharePoint appends Id to lookup fields, like IDListeEtageId). You can find this in the list settings > click the field > check the URL for the Field parameter.
• Filter by Specific Batiment: If you only want data for a single building, add a filter to the first CAML query for ListeEtage:

  <Query>
      <Where>
          <Eq>
              <FieldRef Name="IDListeBatiment" LookupId="True" />
              <Value Type="Lookup">YOUR_BATIMENT_ID</Value>
          </Eq>
      </Where>
  </Query>
  

  Replace YOUR_BATIMENT_ID with the actual IDListeBatiment value from ListeBatiment.

内容的提问来源于stack exchange，提问作者Pierre Petit