ColdFusion ORM: sort order for entity relationships

Question

I've got an app which 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?

I'm wondering if you can use Sam Farmer's suggestion for this question ... it looks like that question is about a situation similar to yours. — Dave DuPlantis

Adam Tuttle Adam Tuttle · Accepted Answer · 2011-07-27T12:26:22

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.

ColdFusion ORM: sort order for entity relationships

1 Answers