ColdFusion ORM: sort order for entity relationships

I've got an app whi开发者_开发知识库ch contains a number of sports clubs, each having a number of contacts.

Each contact can be one of a number of types, and there's a specified display order for these.

I want to specify this sort order for the contacts in my entity relationship, but can't quite see how to do it.

To clarify things a little, here are my three (simplified) entity CFCs:

club.cfc

component persistent="true" table="clubs"
{
    // identifier
    property name="clubid" fieldtype="id" setter="false" generator="identity";

    // properties
    property name="clubname";

    // relationships
    property name="contacts" cfc="contact" singularname="contact" fieldtype="one-to-many" fkcolumn="clubid";
}

contact.cfc

component persistent="true" table="contacts"
{
    // identifier
    property name="contactid" fieldtype="id" setter="false" generator="identity";

    // properties
    property name="clubid";
    property name="name";

    //relationships
    property name="contacttype" cfc="contacttype" fieldtype="many-to-one" fkcolumn="type";
}

contacttype.cfc

component persistent="true" table="contacttypes"
{
    // identifier
    property name="type" fieldtype="id" insert="false" update="false";

    // properties
    property name="typename";
    property name="displayorder";    
}

So, in summary, what I want to do is get the club with its contacts sorted according to the displayorder value in contacttype.

Suggestions?

Sounds to me like you need to override getContacts in club.cfc to do a custom lookup using HQL. Unfortunately I'm no HQL wizard, and I don't have your db to test this against. But here's my somewhat wild guess at the HQL:

public array getContacts()
{
    return ormExecuteQuery(
        "from club cl, contact co, contacttype ct
         where co.clubid = cl.clubid and cl = :thisClub
         order by ct.displayorder"
        , { thisClub = this }
    );
}

I'm sure that's not right, but hopefully it'll get you started.