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 <mkienen..mail.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 <mkienen..mail.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 <mgentr..asslight.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 <dombr..mail.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 <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 : Tue Mar 16 2010 - 19:56:48 EDT