Correct, the example was a simple one I made up just to explain how I was trying to sort on a expression. PostgreSQL supports expressions in the ORDER BY clause just the same as it does in the WHERE clause. It's a really neat feature. It's too bad it's not a standard feature.
Any other ideas on how I could do this easily? I'd rather not resort to SQLTemplate because it's built as a fairly complex SelectQuery right now. It would be nice if I could intercept Cayenne's generated SQL to modify it before it gets sent to the database. That's really the only way I see where I could still use a SelectQuery at this point. -Dave On Wed, Mar 17, 2010 at 12:27 PM, Mike Kienenberger <[email protected]> wrote: > This is my understanding of Dave is asking for, and it's quite simple: > > Get a list of all artists, sorted so that those artists named 'Tom' > appear before all other artists. It's a made-up example, and his real > order expression is probably more meaningful and complicated. > > However, it's unlikely to be supported on many database platforms, and > those that do support it probably do so using different custom sql > syntaxes. For instance, in oracle, you'd have to use "order by case > when" to make this specific example work. > > On Wed, Mar 17, 2010 at 2:10 AM, Juergen Saar <[email protected]> wrote: >> Don't know what you expect, but the statement is really strange ... >> >> I Know: >> SELECT * FROM artist where artist_name = 'Tom'; >> >> Or >> SELECT * FROM artist ORDER BY artist_name DESC; >> >> or did you mean: >> SELECT * FROM artist where artist_name like 'Tom%' ORDER BY artist_name >> DESC; >> >> I think you should know a little about SQL before starting with cayenne ... >> >> >> The third statement would look like: >> >> SelectQuery query = new SelectQuery(Artist.class); >> query.addExpression(ExpressionFactory.likeExp("artistName","Tom"); >> query.addOrdering("artistName", SortOrder.DESCENDING); >> context.performQuery(query); >> >> For the Attribute-Names you should use the static Strings in the >> Artist-Class >> >> >> 2010/3/16 Dave Dombrosky <[email protected]> >> >>> 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 <[email protected]> >>> 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 <[email protected]> >>> 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 >>> >> >>> > >>> >> >
