Castle ActiveRecord - sorting by custom sql expression

Question

Hey, I'm using MonoRail ActiveRecord and have a query that returns businesses within a certain number of miles of a user. I was able to use Expression.Sql to do the distance calculation, however I now need to sort the results by the distance. How do I add a custom sql expression into criteria.AddOrder()?

Here's the code:

var criteria = DetachedCriteria.For<Core.Models.Business>();
            criteria.Add(Expression.Sql(String.Format(@"(3959*acos(cos(radians({0}))*cos(radians(this_.Latitude))*cos(radians(this_.Longitude)-radians({1}))
                    +sin(radians({0}))*sin(radians(this_.Latitude)))) <= {2}", coordinates.Latitude, coordinates.Longitude, radiusInMiles)));
            //TODO: order by distance (formula above!)
            var businesses = FindAll(criteria);
            return businesses.ToList();

Thanks! Justin

Answer 1

You can subclass the Order class and define your own custom ordering. See this example which defines a random sort for SQL Server.

Answer 2

Thanks for the response, but I'm not clear on how it works. This is what I have so far:

public class ProximitySort: Order
    {
        private decimal Latitude { get; set; }
        private decimal Longitude { get; set; }

        public ProximitySort(decimal latitude, decimal longitude) : base("", true) 
        {
            Latitude = latitude;
            Longitude = longitude;
        }

        public override string ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery) {
            return String.Format(@"(3959*acos(cos(radians({0}))*cos(radians(this_.Latitude))*cos(radians(this_.Longitude)-radians({1}))
                    +sin(radians({0}))*sin(radians(this_.Latitude))))", Latitude, Longitude);
        }
    }

And then how do I call it?

criteria.AddOrder(ProximitySort.Asc("what to put in here?"));

Thanks, Justin