I'm working with SQL since about 20 years, but I've no Idea what benefit comes from such an ordering.
Can you give me a real existing example from practice? OK, if you build the values for a drop-down-combo, the defaults are on top ... but that can be achieves with minimal coding 2010/3/18 Dave Dombrosky <[email protected]> > All the Toms first, but then a secondary ordering applied after that. > > -Dave > > On Thu, Mar 18, 2010 at 8:48 AM, Michael Gentry <[email protected]> > wrote: > > Is your intent to have all the Toms first and then all of the others > > (in random order) after the Toms? Or is it just the simplification > > and I'm missing something? > > > > Thanks, > > > > mrg > > > > > > On Thu, Mar 18, 2010 at 2:53 AM, Dave Dombrosky <[email protected]> > wrote: > >> 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 > >>>>> >> > >>>>> > > >>>>> > >>>> > >>> > >> > > >
