How to do a dynamic OrderBy

[Paul McKinney]

I'm just getting into using Squeryl and have been able to create a
couple of queries that do what I want but now I want them to be a bit
more dynamic. For one query I want to change how the results are
ordered depending on the situation. Right now I have:

from(userItems, typeHierarchies)((ui, th) =>
where(ui.userId === user.idField
and ui.typeHierarchyId === th.idField
and ui.typeHierarchyId === topLevelType.id)
select (ui) orderBy(ui.rating desc)).toList


Instead of ordering by "ui.rating" sometime I want the results ordered
by "ui.name". My first thought was to create a method to
conditionally set the order value but I'm not familiar with the
Squeryl API enough to figure out what types I need to use or if I'm
even approaching this the right way.

So one thought was.

def getOrderByValue(ui: ???) = {
if (somethingIsTrue) {
ui.rating
else {
ui.name
}
}


select (ui) orderBy(getOrderByValue(ui) desc)).toList


I'm not sure what type to use for "???". I know there must be a simple
answer for this
it's just I've not been able to find an example of doing this. It is
also possible I might be using the wrong approach for doing this. Any
help would be appreciated.

[David Whittaker]

Hi Paul,

A Squeryl from(...) statement takes objects of type Queryable[A], Queryable[B], etc in the first parameter list and then passes instances of A, B, etc to the function in the second parameter list.  So if your schema contains something like

lazy val userTimes = table[UserItem] // creates Table[UserItem] <: Queryable[UserItem]

Then ui would be of type UserItem.

Your second issue is a little more complicated.  The orderBy method takes an argument of type ExpressionNode and usually the compiler will take care of properly converting whatever you pass in.  The method you are delegating to is going to return either a rating, which I assume is a number, or a name, which I assume is a String, so the return type will likely be inferred as Any and I think that would be too generic for the compiler to figure out how to properly convert it.  The fix should be pretty easy though.... just type the method as returining ExpressionNode and add the desc after each value type to force the conversion before the method returns:

def getOrderByValue(ui:UserTiem): ExpressionNode = {
  if (somethingIsTrue) {
     ui.rating desc
  else {
     ui.name desc
  }
}

[Maxime Lévesque]

You can do this :

val arbitratyArgs = ...

from(userItems, typeHierarchies)((ui, th) =>
where(ui.userId === user.idField
and ui.typeHierarchyId === th.idField
and ui.typeHierarchyId === topLevelType.id)

select (ui) orderBy(getOrderBy(ui, arbitratyArgs))).toList

def getOrderByValue(ui: UserItem, arbitraryArg: Whatever) =
if (arbitraryArg) // when it gets hairy, you can also use pattern
matching ....
ui.rating desc
else
ui.name asc

It's important that you explicitely call 'asc' or 'desc' when you
externalize the orderBy clause,

ML

2011/11/6 Paul McKinney <pmcki...@gmail.com>:

[Roland Kaercher]

Hi,

is it somehow possible to extend this approach to dynamically order by an arbitrary number of attributes? I mean like ordering by ui.attributeA in one case and by ui.attributeA and ui.attributeB at the same time in another case?

Kind regards,

Roland

[David Whittaker]

Roland,

Max would know better than me, but I'm thinking that it might not be possible given that you need to know how many orderBy statements you'll have to choose the right form of: http://squeryl.org/api/index.html#org.squeryl.dsl.boilerplate.OrderBySignatures.

I think the issue is that by-name parameters are not supported as varargs.

[Maxime Lévesque]

> I think the issue is that by-name parameters are not supported as varargs. 

Yes that's the reason the signature is not varargs based.

If you're interested, you could add this method :

  def orderBy(args: List[ExpressionNode]): QueryYield[R] = {

    _orderByExpressions = () => args

    this

  }

right here : http://github.com/max-l/Squeryl/blob/master/src/main/scala/org/squeryl/dsl/boilerplate/OrderBySignatures.scala 

(Forgive the mutability, these methods are called once on ever result set iteration.... they have to be as lightweight as possible ;-))

Then you can do something like :

def mapFieldsAstField(o: Person) = Map(

   "first name" -> o.firstName.~,

   "last name" -> o.lastName.~,

   "age" -> o.age.~

 )

//order : Seq[(String,Boolean)]  is a seq such as : Seq(("age", true), ("first name", false))

//where the boolean is true if descending, false otherwise.

def selectOrdered(order: Seq[(String,Boolean)]) = 

  from(persons)(p => {

   val f = mapFieldsAstField(p)

   val ordering = 

     order.map ({ x =>  

        val field = f(x.1) 

        if(x._2) field.asc else field.desc

      }).toList

    val ob = mapFields.sortBy(_._1)

    where(.....)

    select(p)

    orderBy(ordering)

  })

I'm tempted to add this orderBy(l: List[ExpressioNode]) signature, let me know if it works for you.

ML

2012/2/25 David Whittaker <da...@iradix.com>

[Roland Kaercher]

Hello Max,

this works for me after I changed the body to:
_orderByExpressions = () => args.map(() => _)

It would be great if you could add it to the OrderBySignatures trait,
I am sure others who have highly dynamic order by requirements would
benefit from that, too.

Kind regards,

Roland

P.s.: Thank you both for your quick replies - this is a great list!

On 26 Feb., 15:16, Maxime Lévesque <maxime.leves...@gmail.com> wrote:
> > I think the issue is that by-name parameters are not supported as
>
> varargs.
>
> Yes that's the reason the signature is not varargs based.
>
> If you're interested, you could add this method :
>
>   def orderBy(args: List[ExpressionNode]): QueryYield[R] = {
>     _orderByExpressions = () => args
>     this
>   }
>

> right here :http://github.com/max-l/Squeryl/blob/master/src/main/scala/org/squery...<https://github.com/max-l/Squeryl/blob/master/src/main/scala/org/squer...>

> 2012/2/25 David Whittaker <d...@iradix.com>

>
>
>
>
>
>
>
> > Roland,
>
> > Max would know better than me, but I'm thinking that it might not be
> > possible given that you need to know how many orderBy statements you'll
> > have to choose the right form of:

> >http://squeryl.org/api/index.html#org.squeryl.dsl.boilerplate.OrderBy...
> > .

> >>> 2011/11/6 Paul McKinney <pmckinn...@gmail.com>:

[Luis Ángel]

Has this been add it to OrderBySignatures trait? I need that feature and would be better to not have to maintain a forked branch.

Kind regards.

[Roland Kaercher]

Hello Luis,

it is available at least since 0.9.5-1.

Kind regards,

Roland

[des.m...@gmail.com]

Hi Max

I don't understand how this can compile: ordering is a list of OrdrByArg (as returned by asc/desc). But the orderBy function expects an ExpressionNode which is not compatible?

Des