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 <mkienen..mail.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 <juerge..saar.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 <dombr..mail.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 <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 : Thu Mar 18 2010 - 02:54:02 EDT