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://
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
