Yes, I've seen both NHiberante Search and Spatial. Unfortunately, we're using SQL Server 2005 so NHibernate Spatial isn't an option. I might go with NHibernate Search, but I'd prefer to do it using the current architecture in place.
On Nov 4, 1:15 pm, Fabio Maulo <[email protected]> wrote: > did you see NHibernate.Spatial ? > > 2009/11/4 Kevin Pang <[email protected]> > > > > > > > I've come up with a couple solutions, but they don't feel all that > > great... Here's what I've gathered so far: > > > I can add support in HQL for SQL's CONTAINS function by using a custom > > NHibernate dialect like so: > > > public class MyDialect : MsSql2008Dialect > > { > > public MyDialect() > > { > > RegisterFunction("contains", new StandardSQLFunction > > ("contains", null)); > > } > > } > > > That will allow me to construct the following HQL query: > > > session.CreateQuery("from Restaurant where contains > > (RestaurantName, :keywords)") > > .SetString("keywords","Sizzler") > > .List(); > > > But that doesn't solve the issue with retrieving and sorting by > > distances. It seems in order to do this I have a couple options: > > > 1. Add a formula property to my Restaurant.hbm.xml file that will > > calculate the distance. This would look something like this: > > > <property name="Distance" formula="dbo.DistanceBetween(@lat, @long, > > Latitude, Longitude)" type="double" />. > > > However, this has the side effect (I think) that retrieving any > > Restaurant will run this distance calculation. I don't want this to > > happen. Retrieving a single restaurant by its ID shouldn't need to > > calculate any distances. I only care about distances when users are > > searching for restaurants near them. > > > 2. Break everything out into a <sql-query> that will return a custom > > type (e.g. RestaurantSearchResult). This would look something like > > this: > > > <sql-query name="RestaurantQuery"> > > <return class="RestaurantSearchResult" alias="restaurants"> > > <return-property name="RestaurantId" column="RestaurantId" /> > > <return-property name="Distance" column="Distance" /> > > </return> > > > SELECT > > Restaurants.*, > > dbo.DistanceBetween(@lat, @long, Latitude, Longitude) AS > > Distance > > FROM Restaurants > > WHERE Contains(RestaurantName, @keywords) > > </sql-query> > > > This seems like it should do what I want, but for some reason doesn't > > feel all that right. Any suggestions would be appreciated. > > > Kevin > > > On Nov 4, 3:31 am, Wayne Douglas <[email protected]> wrote: > > > you should think about using NHIbernate.Search for this :) > > > > On Tue, Nov 3, 2009 at 5:26 PM, Kevin Pang <[email protected]> wrote: > > > > > Let's say I'm building a restaurant review application (e.g. Yelp) > > > > using SQL Server 2005 and NHibernate. I want to allow users to search > > > > for restaurants by keywords and/or proximity to their current > > > > location. The keyword search will use the SQL Server 2005 full text > > > > search CONTAINS function, while the distance calculation will be done > > > > using a stored procedure that can calculate the distance between two > > > > lat/long points. > > > > > For the sake of simplicity, let's say I have a table called > > > > Restaurants with the following columns: > > > > > RestaurantId int > > > > RestaurantName nvarchar(100) > > > > Latitude float > > > > Longitude float > > > > > What's the best way to construct this query? > > > > > A few requirements: > > > > 1. Users can specify keywords or location or both or neither > > > > 2. The end result needs to be ranked by distance, then relevance > > > > 3. Resulting IList (or whatever we're returning) needs to have > > > > Restaurant information as well as the distance in it for displaying > > > > purposes. I'm assuming we need to create a new class just to handle > > > > the search results (e.g. RestaurantSearchResult which holds both a > > > > Restaurant instance and a Distance property)? > > > > -- > > > Cheers, > > > > w:// > > -- > Fabio Maulo --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
