Sorting on nullable child property in CoolStorage

Question

Lets says we have Films and HeaderItems. Sometimes a Film has a HeaderItem linked to it OneToOne but not always.

Now I want to sort all the Films on their title BUT I would like the films with a HeaderItem linked to them to sort on HeaderItem.Position (if HeaderItem is not null).

I've tried to implement a OneToMany on Films and a OneToOne but I can get the sorting to work. He can't convert the QueryExpression to String or vice versa.

Any ideas on how to do this?

Answer 1

I haven't tried it, but this may actually work:

  Film.List().OrderedBy("HeaderItem.Position,Title");

If that doesn't work, I'm afraid you'll have to create a view or execute ad-hoc SQL using CASE statements in your ORDER BY clause.

Answer 2

Ok, I figured it out.

First things first, I changed the relation between Film and HeaderItem to a OneToMany.

Then I used

Film.OrderedList("$ISNULL(HeaderItem.ShowOnTopInCategory, 0)-, Top10, MovieTitle");

to get a list of films order by headerItems first and then the rest.

Works great. Btw, the $-sign in front of the ISNULL avoids the ISNULL from being replaced by the CoolStorage SQL parser.