Good that my solution worked!
Well, I'm not an expert on Criteria, so I really can't say out of my head...
Just one note: in the future, please use the nhusers mailing list. This one
is used by the developers of NHibernate (and me...) for discussing issues
regarding the development of NHibernate itself.
RP
On Friday, June 14, 2013 9:20:31 AM UTC+1, Milan Gornik wrote:
>
>
> Thanks Ricardo!
>
> That is a working solution, many thanks!
>
> I was trying out different variations and in the end I used something like
> this:
>
> criteria.AddOrder(new
> Order(Projections.SqlProjection("substring(FromEmail,
> locate('@', FromEmail) + 1) as Domain", new[] {"Domain"},
>
> new
> IType[] {NHibernateUtil.String}), query.OrderByAscending));
>
> I checked generated SQL and I see that the ORDER BY part was defined for
> this. I tried adding the same SqlProjection into criteria.SetProjection()
> call to make the SQL call a part of resultset. However, in that case I
> could never make the NHibernate recognize that alias later on. For
> instance, if I use same lines as above and introduce "Domain" alias in the
> projections, I was thinking I can probably do criteria.AddOrder(new
> Order("Domain"... However, it would fail at runtime complaining that
> "Domain" is not known alias. I checked generated SQL and saw that unlike
> other fields, the SQL field "Domain" never got the NHibernate internal
> alias (like y1_). Do you know if there is a way to achieve that? I thought
> that second parameter in SqlProjection (new [] {"Domain"}) links the SQL
> projection to other NHibernate projections, but that seem to work
> differently.
>
> Regards,
>
> Milan
> On Thursday, June 13, 2013 4:59:50 PM UTC+2, Ricardo Peres wrote:
>>
>> This works on my machine:
>>
>> ISQLFunction f = new SQLFunctionTemplate(NHibernateUtil.String,
>> "SUBSTRING(Name, CHARINDEX('@', Name) + 1, 0)");
>> var products = session.CreateCriteria(typeof(Product))
>> .AddOrder
>> (
>> Order.Asc
>> (
>> Projections.SqlFunction(f, NHibernateUtil.String)
>> )
>> )
>> .List();
>>
>>
>> I had to use CHARINDEX instead of LOCATE because I'm using SQL Server,
>> also, SUBSTRING requires a third parameter, I used 0.
>>
>> RP
>>
>>
>> On Thursday, June 13, 2013 12:32:08 PM UTC+1, Milan Gornik wrote:
>>>
>>>
>>> Hello,
>>>
>>> I am using NHibernate version 2.0.5 and building ICriteria to display
>>> data from two joined tables. Criteria is sorted (AddOrder()) and also
>>> limited (SetFirstResult(), SetMaxResults()). Everything works out fine.
>>> Now, I am trying to perform ordering which should go by result of SQL
>>> functions performed on the attributes (which are already in the criteria).
>>> The SQL I would like to perform is as following:
>>>
>>> SELECT FromEmail FROM Emails
>>> ORDER BY SUBSTRING(FromEmail,LOCATE('@',FromEmail)+1);
>>>
>>> The SELECT part is more complex in my code but I'm keeping it minimal
>>> here for the sake of clarity. Can anyone help me with adding this? I was
>>> thinking that I should probably add new projection to my existing ones and
>>> that new projection should execute the SQL code SUBSTRING(...) and that
>>> then I should AddOrder() by that new property/alias. However, I'm having
>>> trouble forming the code that should do this. Or, if that is easier I could
>>> AddOrder() passing SqlFunction or SqlProjection. Any of the two
>>> approaches would be fine, if I could figure out the right syntax. Any help
>>> is appreciated!
>>>
>>> Milan Gornik
>>>
>>>
--
---
You received this message because you are subscribed to the Google Groups
"nhibernate-development" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
