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 <dombr..mail.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 <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 - 08:50:12 EDT