cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Juergen Saar <juer...@jsaar.org>
Subject Re: Order By Expression
Date Fri, 19 Mar 2010 06:58:15 GMT
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 <dombrd@gmail.com>

> All the Toms first, but then a secondary ordering applied after that.
>
> -Dave
>
> On Thu, Mar 18, 2010 at 8:48 AM, Michael Gentry <mgentry@masslight.net>
> 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 <dombrd@gmail.com>
> 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 <mkienenb@gmail.com>
> 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 <juergen@jsaar.org>
> 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 <dombrd@gmail.com>
> >>>>
> >>>>> 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 <
> mgentry@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 <dombrd@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
> >>>>> >>
> >>>>> >
> >>>>>
> >>>>
> >>>
> >>
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message