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 <mkien...@gmail.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 <mkien...@gmail.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 <mgen...@masslight.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 <dom...@gmail.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 <mgen...@masslight.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 <dom...@gmail.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 >>>>>> >>>>> >>>> >>> >> >
