List join and projection

Http://msdn.microsoft.com/zh-cn/library/ee539975.aspx

This topic describes collaborative applications.ProgramList join and field projection in views and queries defined by the markup language (caml.

Join and projection in List View

The list view can contain fields from other lists that have been joined to the master list. CamlViewElement through its Child ElementJoinsAndProjectedfieldsTo implement this function, these two elements are passed in the object modelSpviewObjectJoinsAndProjectedfieldsAttribute. (SpqueryThe object has an attribute of the same name. For more information, seeJoin and projection in Query.)

List join in the view

JoinsThe element contains one or moreJoinElement. Each element in these elements creates an internal join or a left external join between two lists. At least one of these joins must come from the parent list of the view, which is called the primary list, and the other is called the external list. But there can be additional connections between the external list and another external list, and so on. There is no limit on the number of links in the link chain,JoinThe total number of elements, whether in the chain or not, cannot exceedSpwebapplicationObjectMaxquerylookupfieldsAttribute Value. The default value is 8. The list can be directly or as a link to it.

Remember some requirements when creating a list join. You cannot join any two lists without considering the type. If two lists can be joined, it is not just a join pair of any primary field and external field.The fields in the main list must be lookup fields, which must be used to find fields in the external list.For this reason, all connections are available between the image list.

The following example indicates that there is a Sharepoint Foundation website where parents form a club to sell old clothes to each other. OneOrdersList View, which displays the city and state where the buyer (customer) is located, and the city where the sales member (consignor) is located. To achieve this goal, two left external links are required:

OrdersToMembersToCitiesToStates

OrdersToMembersToCities

Note the following:JoinsTag-related:

EachJoinElementTypeThe attribute can be left or inner ".

BecauseOrdersToMembersThere are two joins, which must be separated. This is throughListaliasThis attribute is implemented in the first joinMembersThe list is assigned with the alias "customer", but "shipper" is assigned to the list in the second join ".

SlaveMembersToCitiesThere are also two joins, which are differentiated in the same way.

Any list alias is not explicitly mapped to the list anywhere. Ing is unnecessary because each join is in parallel with the existing query field, and the query field definition identifies the external list.

The join on field isFieldrefElement pair identifier. The first field indicates the query field in the main list and identifies it with an internal name. It must haveReftypeAttribute. If the connected primary list is not the parent list of the view, it also setsListAttribute. The second of each element pairFieldrefThe element identifies the external list through the alias again, and also identifies the foreign key field that must always be the ID field.

Copy <joins> <join type = 'left' listalias = 'customer'> <EQ> <fieldref name = 'mermername' reftype = 'id'/> <fieldref list = 'customer 'name = 'id'/> </EQ> </join> <join type = 'left' listalias = 'customer _ City'> <EQ> <fieldref list = 'customer 'name = 'cityname' reftype = 'id'/> <fieldref list = 'customer _ City' name = 'id'/> </EQ> </join> <join type = 'left' listalias = 'customer _ city_state '> <EQ> <fieldref list = 'customer _ City' name = 'statename' reftype = 'id'/> <fieldref list = 'Customer _ city_state 'name = 'id'/> </EQ> </join> <join type = 'left' listalias = 'shipper'> <EQ> <fieldref name = 'shippername' reftype = 'id'/> <fieldref list = 'shipper 'name = 'id'/> </EQ> </join> <join type = 'left' listalias = 'shipper _ City'> <EQ> <fieldref list = 'shipper 'name = 'cityname' reftype = 'id'/> <fieldref list = 'shipper _ City' name = 'id'/> </EQ> </join> </joins>

Projection field in view

ProjectedfieldsFields are created from the external list so that they can be used in the List View. The field must also be inViewElementViewfieldsChild element.

Continue to demonstrate the example of a parent club.ProjectedfieldsFields will be created for the city where the customer is located, the State where the customer is located, and the city where the consignor is located. Note that the following content is related to this tag:

The external list is identified by its alias, suchJoinsAs defined in the element.

ShowfieldAttribute identifies which field in the external list is used in the view.

TypeThe property always has the value "lookup ". For this reason,TypeThe property is not normallyFieldThe data type of the field as shown in the element. WhenFieldElement isProjectedfieldsIn sub-level,TypeOnly indicationJoin(InProjectedfieldsTheJoinsElement) is based on the existing query relationship between lists. All connections must be based on the existing query relationship. The following lists the types of caml fields that can be used as projection fields.

Copy <projectedfields> <field name = 'mermercity' type = 'lookup' list = 'customer _ City' showfield = 'title'/> <field name = 'mermercitystate' type =' lookup 'list = 'customer _ city_state 'showfield = 'title'/> <field name = 'shippercity' type = 'lookup 'list = 'shipper _ City' showfield = 'title' /> </projectedfields>

Only the following types of fields can be included inProjectedfieldsElement:

Calculated (treated as plain text)

Contenttypeid

Counter

Currency

Datetime

Guid

Integer

Note (only one line is supported)

Number

Text

As described aboveProjectedfieldsIt is also necessaryViewfieldsElement. The following mark continues with this example.

Copy <viewfields> <fieldref name = 'mermercity'/> <fieldref name = 'mermercitystate'/> <fieldref name = 'shippercity'/> </viewfields>

Join and projection in Query

join and projection fields can also be used in caml queries. In this case, the join and projection fields also use joins and projectedfields element. However, these elements are not child level of the query element. They are independent XML tags that constitute the spquery spquery . joins and spquery . projectedfields attribute value.

Generally, it is best to useLINQ to SharePoint providerTo query serversCode. Because caml hasJoinsAndProjectedfieldsElement. The LINQ to SharePoint provider can convert a LINQ query to a caml query.Join(In Visual BasicJoin) AndSelect(In Visual BasicSelect) Operator. We recommend that you useUse ADO. NET data service to query SharePoint Foundation.

If you want to directly create a caml query and explicitly setSpqueryTo generate a query using tools. To find such a tool, navigateWww.bing.comSearch for "caml query tool" (without quotation marks ). After Microsoft SharePoint Foundation 2010 is released, it may take some time to get support for GenerationJoinsAndProjectedfieldsAny tool of the element.

The following example shows that the customer's city is London.OrdersThe list returns the query of all orders. This example assumes thatOrdersThe list hasCustomernameField.MERsList, which hasCitynameField.CitiesList.

Query requiredOrdersToMERsAndMERsToCitiesThereforeJoinsThe attribute value is as follows.

Copy <joins> <join type = 'left' listalias = 'customer'> <EQ> <fieldref name = 'customer' reftype = 'id'/> <fieldref list = 'customer' 'name = 'id'/> </EQ> </join> <join type = 'left' listalias = 'mermercities '> <EQ> <fieldref list = 'customer' name = 'cityname' reftype = 'id'/> <fieldref list = 'mermercities 'name = 'id'/> </EQ> </join> </joins>

Because the queriedWhereTo test the customer's city, we need to create a customercity field. Therefore,ProjectedfieldsThe value is as follows.

Copy <projectedfields> <field name = 'mermercity' type = 'lookup' list = 'mermercities 'showfield = 'title'/> </projectedfields>

Next, we need to add the reference to this fieldViewfieldsTo make the field available. Therefore,ViewfieldsThe attribute value is as follows.

Copy <viewfields> <fieldref name = 'mermercity'/> </viewfields>

Finally,QueryThe property settings are as follows.

Copy <query> <where> <EQ> <fieldref name = 'mermercity'/> <value type = 'text'> LONDON </value> </EQ> </where> </query> Implicit join without joins Element

We recommend that you use explicitJoinsTo maximize the readability of the tag. This approach can also maximize the chances of your query tag being compatible with the SharePoint foundation of future versions. However, there is one way to support the implicit join of two lists without the need to useJoinsElement. You only need to create oneProjectedfieldsElement, except for the following exceptions: Sub-LevelFieldElement hasFieldrefAttribute insteadListAttribute.FieldrefOnly the lookup column in the source list is identified. In additionProjectedfieldsElement hasFieldrefAttribute insteadListAttribute, itsNameAttribute should be assigned a value different from any column in the source list. (Again, in this tag, you do not need to identify the target list because it is specified in the view link configuration.

For example, if the list and view relationships are the same as those in the previous sectionViewfieldsElement.

Copy <query> <where> <EQ> <fieldref name = 'mermername'/> <value type = 'text'> Hicks, cassie </value> </EQ> </where> </query> <viewfields> <fieldref name = 'mermername'/> </viewfields>

Note,WhereThe element is implicitly joined between the "orders" and "MERs" lists. You can onlyProjectedfieldsElement supports this query. You do not need to useJoinsElement. (Note:NameAttribute specified is different fromFieldrefAny name of the actual query column name specified by the attribute .)

Copy <projectedfields> <field name = 'orderingcustomer' type = 'lookup' fieldref = 'mermername' showfield = 'title'/> </projectedfields>

Even if you use this technique, there is a query relationship between the source column and the target list. In addition, you cannot link to the connection. For example, you cannot supportQueryElement. This query creates an implicit dual connection from orders to mers MERs to cities. The link must be explicitlyJoinsElement.

Read the full text

category: View comments on moss technology