Re: Order By Expression

From: Michael Gentry (mgentr..asslight.net)
Date: Tue Mar 16 2010 - 19:55:51 EDT

  • Next message: Juergen Saar: "Re: Order By Expression"

    I'm not aware of a way to sort by an expression in Cayenne.

    mrg

    On Tue, Mar 16, 2010 at 1:23 PM, Mike Kienenberger <mkienen..mail.com> wrote:
    > Dave,
    >
    > By the way, the example you gave doesn't work under Oracle.
    >
    > You'd have to use
    >
    > select * from artist ORDER BY case when artist_name = 'Tom' then 1
    > else 2 end DESC;
    >
    > Not sure if that's relevant your environment.
    >
    > And just to clarify, when I said "I don't think it supports
    > expressions, only columns, in the ordering.", I meant that I don't
    > think Cayenne supports expressions in the ordering.
    >
    > On Tue, Mar 16, 2010 at 2:11 PM, Mike Kienenberger <mkienen..mail.com> wrote:
    >> Michael,
    >>
    >> I think you're misunderstanding him.
    >>
    >> He's telling you exactly what he wants.
    >>
    >> SELECT * FROM artist ORDER BY artist_name = 'Tom' DESC;
    >>
    >> http://www.informit.com/articles/article.aspx?p=339935&seqNum=2
    >>
    >> http://searchoracle.techtarget.com/answer/CASE-expressions-in-the-ORDER-BY-clause
    >>
    >> I'm not sure if Cayenne supports it.  I've never seen that notation
    >> before.  I don't think it supports expressions, only columns, in the
    >> ordering.
    >>
    >> I'd say it's a pretty database-specific function (but maybe I'm wrong).
    >>
    >>
    >> On Tue, Mar 16, 2010 at 1:53 PM, Michael Gentry <mgentr..asslight.net> wrote:
    >>> Hi Dave,
    >>>
    >>> You are close, but not quite there.  The ordering only applies to a
    >>> column (artistName), but you are giving it a qualifier/expression
    >>> (artistName = 'Tom').  You need to create the expression separately
    >>> (multiple ways to do that, but I'll show one):
    >>>
    >>> Expression expression = ExpressionFactory.match("artistName", "Tom");
    >>> SelectQuery query = new SelectQuery(Artist.class, expression);
    >>> query.addOrdering("artistName", SortOrder.DESCENDING);
    >>> context.performQuery(query);
    >>>
    >>> You might want to look at:
    >>>
    >>> http://cayenne.apache.org/doc30/parameterized-queries.html
    >>> http://cayenne.apache.org/doc30/api/org/apache/cayenne/exp/ExpressionFactory.html
    >>> http://cayenne.apache.org/doc30/api/org/apache/cayenne/exp/Expression.html
    >>>
    >>> Expressions are where you do the WHERE clause.  Also, instead of
    >>> hardcode "artistName", you probably have Artist.ARTIST_NAME_PROPERTY
    >>> available as a constant which is safer to use.
    >>>
    >>> mrg
    >>>
    >>>
    >>> On Tue, Mar 16, 2010 at 1:33 PM, Dave Dombrosky <dombr..mail.com> wrote:
    >>>> Yes I'm using Cayenne 3.  I'm not sure if I stated the problem in
    >>>> enough detail, because it seems like you guys are confused.  Or maybe
    >>>> I just don't understand how to use what you are telling me about.
    >>>>
    >>>> Maybe it would be better if I was helped with a full example.  Using
    >>>> the Artist class from Cayenne's test schema, how would I go about
    >>>> creating a SelectQuery to order all artists with the name "Tom" first?
    >>>>  Basically to generate a query similar to this:
    >>>>
    >>>> SELECT * FROM artist ORDER BY artist_name = 'Tom' DESC;
    >>>>
    >>>> Would it be like this?
    >>>>
    >>>> SelectQuery query = new SelectQuery(Artist.class);
    >>>> query.addOrdering("artistName = 'Tom'", SortOrder.DESCENDING);
    >>>> context.performQuery(query);
    >>>>
    >>>> Because that still gets the error Unsupported ordering expression:
    >>>> artistName = 'Tom'.
    >>>>
    >>>> Am I doing something wrong, or is this impossible with a SelectQuery?
    >>>> I'd rather not use SQLTemplate if I can avoid it.
    >>>>
    >>>> -Dave
    >>>>
    >>>> On Tue, Mar 16, 2010 at 8:39 AM, Michael Gentry <mgentr..asslight.net> wrote:
    >>>>> Hi Dave,
    >>>>>
    >>>>> Since you are seeing deprecation warnings I'm assuming you are using
    >>>>> Cayenne 3?  If so, you should use:
    >>>>>
    >>>>> addOrdering(Ordering ordering) or
    >>>>> addOrdering(String sortPathSpec, SortOrder order)
    >>>>>
    >>>>> These are defined for your SelectQuery object.  Of course, if you are
    >>>>> using the first of those methods, you'll have to create your own
    >>>>> Ordering object first.  The second creates one for you behind the
    >>>>> scenes.
    >>>>>
    >>>>> Let me know if you need additional pointers!
    >>>>>
    >>>>> mrg
    >>>>>
    >>>>>
    >>>>> On Tue, Mar 16, 2010 at 1:11 AM, Dave Dombrosky <dombr..mail.com> wrote:
    >>>>>> Is there any way to use sort expressions in a query?  Something like
    >>>>>> "ORDER BY column = id"?  I get the error "Unsupported ordering
    >>>>>> expression" when trying to execute a query with this in it.
    >>>>>>
    >>>>>> Also, it looks like I might be able to do this using in-memory
    >>>>>> sorting, but the Ordering(Expression sortExpression, ...) methods are
    >>>>>> deprecated.  So what's the preferred way to sort on expressions in
    >>>>>> Cayenne?
    >>>>>>
    >>>>>> -Dave
    >>>>>>
    >>>>>
    >>>>
    >>>
    >>
    >



    This archive was generated by hypermail 2.0.0 : Tue Mar 16 2010 - 19:56:48 EDT