CodeFluent related collection sorting

Question

I have two CodeFluentEntities collections that are related in a typical Master/Detail relationship: Invoices and Invoice details.

While I can load the invoice collection in order by the invoice's number, I can't quite figure out how to get the related collection of invoice details to load in order by the invoice's line numbers.

This works in retrieving the invoice's in the desired sequence:

Dim _page As New CodeFluent.Runtime.PageOptions("[Invoice].[InvoiceNumber]", System.ComponentModel.ListSortDirection.Ascending)
Dim _orders as Accounting.AR.OrderCollection.PageLoadAll(0, -1, _page)
OrderCollectionBindingSource.DataSource = _orders

The InvoiceDetail collection is loaded in a random order. What I would like to do is have the related collection sorted by [InvoiceDetail].[LineNumber]

score 1 · Accepted Answer · answered Apr 26 '16 at 08:22

1

CodeFluent Entities offers several options to handle collections sorting:

Static sorting: the collection is sorted in SQL using CFQL methods. You can also overwrite the LoadBy...() method if you need to. However, it is probably better to create a CFQL method, called LoadBy...OrderedBySomething().
Dynamic sorting: the collection is sorted in SQL depending on sorting parameters.
Client side sorting: with the Sort method that can be overriden for your needs.

All of these options are documented here: http://www.softfluent.com/documentation/?BOM_SP_Sorting.html

In your case, you want to change the method used by the Orders property to load the related collection of orders. So you need to create a new CFQL method (or replace the existing one) with static sorting. Then you can set this method as the one to use using the loadMethodName attribute:

<cf:entity name="Customer">
  <cf:property name="Id" key="true" />
  <cf:property name="FullName" />

  <!-- loadMethodName -->
  <cf:property name="Orders" loadMethodName="LoadByCustomerOrderedByCreationDate" typeName="{0}.OrderCollection" relationPropertyName="Customer" />
</cf:entity>

<cf:entity name="Order">
  <cf:property name="Id" key="true" />
  <cf:property name="CreationDate" sortable="true" typeName="date" />
  <cf:property name="Customer" typeName="{0}.Customer" relationPropertyName="Orders" />

  <!-- Static sorting: `ORDER BY ...` -->
  <cf:method name="LoadByCustomerOrderedByCreationDate" body="LOAD(Customer) WHERE Customer = @Customer ORDER BY CreationDate" />
</cf:entity>

The Orders property is:

public OrderCollection Orders
{
    get
    {
        if (this._orders == null)
        {
            // code omitted for brevity
            this._orders = OrderCollection.LoadByCustomerOrderedByCreationDate(this);
        }
        return this._orders;
    }
}

answered Apr 26 '16 at 08:22

meziantou

20,589
7
64
83

A couple of problems. If I use the model you suggest, the link between the Customer and Orders in the database is broken. If I use CustomerCollection.LoadAll only the Customer rows are returned whereas before the .LoadAll returned a hierarchical collection which included the OrderCollection for each Customer as part of the returned Customer. – Greg Delaney Apr 26 '16 at 15:31
What I am attempting is obtain a hierarchical collection for all Customers and their Orders where the Customer collection is sorted by the CustomerNumber and the related Orders are sorted by the OrderNumber – Greg Delaney Apr 26 '16 at 15:33
I don't understand why you can't create 2 methods `CustomerCollection.LoadAllOrderedByCustomerNumber` and `OrderCollection.LoadByCustomerOrderedByOrderNumber`, and use the `loadMethodName` attribute on `Customer.Orders`. This way customers are sorted as expected, and `customers[i].Orders` will be sorted by OrderNumber. – meziantou Apr 26 '16 at 18:43
The example used is greatly simplified as I tried to reduce what I need to ultimately do. First, I can't break the database and its established relations. CFE works fine as the model identifies and maintains the links managed by the database. – Greg Delaney Apr 26 '16 at 20:55
I have many tables (50+) that are stacked in a multilevel (6) hierarchy. A user can start anywhere in any table and work up or down the hierarchy and the sort order depends on the user's searching requirements. Each table has multiple columns that can be used as a sort criteria so the number of permutations that would have to be created are significant. – Greg Delaney Apr 26 '16 at 20:55
My original question asked how a sort criteria could be provided using the CF.Runtime.PageOptions. Since each collection has sortable properties that can be specified in PageOptions, I though this would be a reasonable approach that would not require writing multiple methods. – Greg Delaney Apr 26 '16 at 20:57
Your suggestion worked, but it deleted all the parent GUID keys thus breaking the database relationships that other systems depend upon. – Greg Delaney Apr 26 '16 at 20:58
It would be easier to loop through each collection, retrieve the related collection list, sort the list and repopulate. – Greg Delaney Apr 26 '16 at 21:01

CodeFluent related collection sorting

1 Answers1