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.
