ORM question- sorting one to many when sort order is in the next table over

I’d have no problem doing this in SQL but I’m relatively new to ORM.

Simplifying a bit, this is the basic schema I’m working with (four tables):

Events -|-o< Registrations -|-o< FunctionRegistrations >o-|- Functions

o-|- Events

The sort column is on Functions- so functions are sorted within a
particular event.

People register for the Event (Registrations) and register for functions
within the event (RegistrationFunctions).

I am looking at one specific Registration, one person for one event.

What I want to get here is a list of functions for this particular
registration, sorted correctly. The sort column is in Functions. I then
need to also get information related to that specific function registration
(like the price paid, which is in FunctionRegistrations).

Looking at a given registration, I have tried:

  • a one-to-many relationship with FunctionRegistrations. This lets me get
    the functions and the price, but I can’t sort the functions correctly as
    the sort column is in the next table over.

  • a many-to-many relationship between Registrations and Functions, with
    FunctionRegistrations as the link table and an orderby=“sort”. This gets me
    my functions in the right order but I am not clear then on how I can get
    information like the price out of the link table. I tried defining a
    one-to-many relationship back from Functions to FunctionRegistrations but
    this then gets me all the registrations. Which is sort of what you’d expect
    all right but not what I need!

Do I need to use HQL to do this, or can I get the default sort order right
in the model so I can just use EntityLoad()?

Hi,

Try creating a custom getter in your Registration cfc to override the
auto-generated getter for your one-to-many FunctionRegistration
relationship as follows:

public array function getFunctionRegistrations(){
return OrmGetSession().createFilter(
variables.functionRegistrations,“order by function.name” ).list();
}

That should order the FunctionRegistrations by the name of the
associated Function (obviously replace “name” with whatever field you
want to sort on).

Julian.On 9 September 2016 at 14:48, Ivan McAvinchey <@Ivan_McAvinchey> wrote:

Looking at a given registration, I have tried:

  • a one-to-many relationship with FunctionRegistrations. This lets me get
    the functions and the price, but I can’t sort the functions correctly as the
    sort column is in the next table over.

Many thanks, Julian, I just replaced “name” with my sort column and that
worked perfectly and got me exactly what I needed.

Ivan