Inside FetchXML pt 4 – order

It’s often important to sort the results of your query, and in FetchXML you can do this with the <order> element. This is equivalent to the ORDER BY clause in SQL.

<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <order attribute="fullname" />
  </entity>
</fetch>

Sorting by multiple fields

By adding more <order> elements, the results will be sorted by each attribute in sequence. The orders are applied, well, in order. The results will be sorted by the first <order> attribute, then the second one, and so on.

<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <order attribute="fullname" />
    <order attribute="createdon" />
  </entity>
</fetch>

This will order the results by fullname, then by createdon. In this way we can ensure that two records with the same name will be sorted so that the earliest created record comes before later records.

Reversing ordering

Each <order> is sorted in ascending order by default, so numerically lower, alphabetically earlier or earlier date values come first.

This ordering is reversible by applying the descending attribute. This can be selectively applied to individual <order> elements, so some sorts are applied in ascending order and others descending.

<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <order attribute="fullname" />
    <order attribute="createdon" descending="true" />
  </entity>
</fetch>

In this way we can reverse the previous logic and prefer a more recent record over older ones where two have the same name, but keep the alphabetical ordering.

Sorting on lookup fields

You might expect an ordering applied to a lookup field to sort by the guid value in the lookup field itself. Indeed, you can write a sort as:

<fetch>
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="primarycontactid" />
    <link-entity name="contact" to="primarycontactid" from="contactid">
      <attribute name="fullname" />
    </link-entity>
    <order attribute="primarycontactid" />
  </entity>
</fetch>

If you run this query however, you’ll see that the results are actually ordered according to the full name of the primary contact.

This is true of any lookup field – the results are sorted according to the primary name attribute of the related entity. This is even true for polymorphic lookup fields such as customer fields. This makes sense from the perspective of the user interface – click on the header of the lookup column and you’d expect it to sort by the name. But this does not guarantee that records with the same guid will be retrieved consecutively, which can cause problems if you are using these IDs in integrations.

If you have code that relies on getting records with the same guid from a lookup field next to each other, order by the primary key of the related record instead of the foreign key lookup field. For example, rewriting the previous query as follows will order the results by the contact ID instead of the name:

<fetch>
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="primarycontactid" />
    <link-entity name="contact" to="primarycontactid" from="contactid">
      <attribute name="fullname" />
      <order attribute="contactid" />
    </link-entity>
  </entity>
</fetch>

Sorting on linked entities

As we’ve just seen in the last example, an <order> can be placed within a <link-entity> as well as within the main <entity> to apply the ordering to attributes within the linked entity.

Bear in mind there is no way to specify the sequence that orderings are applied across multiple entities. Orders from the main <entity> are always applied first, then the first <link-entity>, then any sub-link-entities, and so on. In this example:

<fetch>
  <entity name="account">
    <attribute name="accountid" />
    <link-entity name="contact" to="primarycontactid" from="contactid">
      <link-entity name="systemuser" to="ownerid" from="systemuserid">
        <attribute name="fullname" />
        <order attribute="fullname" />
      </link-entity>
      <attribute name="fullname" />
      <order attribute="fullname" />
    </link-entity>
    <link-entity name="systemuser" to="ownerid" from="systemuserid">
      <attribute name="fullname" />
      <order attribute="fullname" />
    </link-entity>
    <order attribute="name" />
    <order attribute="createdon" />
  </entity>
</fetch>

CDS sorts the results by:

• Account name
• Account creation date
• Primary contact name
• Primary contact owner name
• Account owner name

In programming terms, this is a depth first search.

Sorting & Paging

When you execute any query, by default you will only get up to 5,000 records back. To get any more you need to use paging to get the next 5,000, and the next, and so on.

For paging to work correctly, the data must be sorted so that CDS can work out where the next page should start from.

Because a sort on any field apart from the primary key can result in two records with the same value being next to each other, it would be impossible to use just those values to determine where the next page should start. If you sort by the name of contacts and the last record in the first page has a name of Mark Carrington, the next page could start with another Mark Carrington record as well. There is no way of telling exactly which of these records should be the start of the next page.

To resolve this problem, CDS automatically adds an extra sort on the primary key field. This makes sure there is a unique value for the system to use to tell exactly where the next page should start.

Remember though that a sort can’t be applied to the top-level entity after one that was applied to a link entity. This means that if you’ve sorted the query by a link entity, this automatic sort by the primary key of the main entity can’t be added without giving a different sort order to what the user asked for, so it’s not added and you won’t get a paging cookie back. You can still ask for the next page, but it will be retrieved less efficiently.

Sorting & Aliases

If you choose to give aliases to the attributes in your query (I looked at this in part 1 of this series, and recommended against it), be careful about sorting by it. If you execute this query:

<fetch>
  <entity name="contact">
    <attribute name="fullname" alias="name" />
    <order attribute="fullname" />
  </entity>
</fetch>

you will get the expected results, but when you try to move on to the next page you’ll get the same records again. If you keep moving on to the next page you’ll get stuck in an infinite loop as you never actually move on to the next page.

If you have a look into the contents of the paging cookie you receive, you’ll see it shows that the last record in the page has a value of null for the fullname attribute, so the first record of the next page should be the next one that also has a null name or the first non-null value. This will obviously give the first page of data again.

I can see why this would happen – the record that was retrieved doesn’t have a fullname attribute, as it’s been aliased to name instead.

To avoid this problem, see my earlier advice and don’t use aliases unless you’re doing aggregate queries! If you must, add the attribute again without an alias, e.g.

<fetch>
  <entity name="contact">
    <attribute name="fullname" alias="name" />
    <attribute name="fullname" />
    <order attribute="fullname" />
  </entity>
</fetch>

When CDS calculates the paging cookie to give back, the final record in the page will have a fullname value to use and therefore the cookie will correctly identify where to start the next page.