CODEWITHSUNDEEP
ormcoldfusion
sebduggan
sebduggan

Reputation: 561

ColdFusion ORM: sort order for entity relationships

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?

Upvotes: 2

Views: 859

Answers (1)

Adam Tuttle
Adam Tuttle

Reputation: 19804

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.

Upvotes: 3

Related Questions