All the Toms first, but then a secondary ordering applied after that.
-Dave
On Thu, Mar 18, 2010 at 8:48 AM, Michael Gentry <mgen...@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 <dom...@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 <mkien...@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 <juer...@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 <dom...@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 <mgen...@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 <dom...@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
>>>>> >>
>>>>> >
>>>>>
>>>>
>>>
>>
>
