That's a good point.
Now, to the next scenario, where I have to pull all of the theaters that are
within 60 miles of the computed location. The statement is nearly the same:
SELECT * FROM
theater t
WHERE
sqrt(pow(69.172 * (t.latitude-:lat(float)),2) +
pow(50.8750184225293 * (t.longitude-:lng(float)),2)) <= 60
I guess it's possible the answer would be the same here, but just double
checking to make sure that a SQL projection is the best approach.
Thanks for your help!
On Sat, May 7, 2011 at 6:59 PM, Francois Ward <[email protected]>wrote:
> I don't -think- you can in Criteria. Probably possible in HQL with a custom
> dialect, but the gain would be low.
>
> Since you're not filtering using those custom functions, I would think you
> could simply select the data and do the calculation inside a public
> property
> in your entity though... no real need to do it server side. Wouldn't even
> change performance much.
>
> -Francois
>
> -----Original Message-----
> From: [email protected] [mailto:[email protected]] On Behalf
> Of cs
> Sent: Saturday, May 07, 2011 7:21 PM
> To: nhusers
> Subject: [nhusers] rewriting query as ICriteria
>
> I have the following query (PostgreSQL) that I'd like to convert to
> ICriteria:
>
> SELECT
> t.theater_id as tid,
> round(cast (sqrt(pow(69.172 * (latitude-:lat(float)),2) +
> pow(50.8750184225293 * (t.longitude-:lng(float)),2)) as
> numeric),2)
> as distance
> FROM
> theater t
>
> Is it possible to write this query using QueryOver/ICriteria? Or am I
> better
> off projecting this as SQL? Ideally, I'd like for this to work in both
> dev/production with developer tests using SQLite and production using
> Postgres. Some of the functions I'm using aren't supported on SQLite, which
> is causing problems for me.
>
> Thanks!
>
> --
> 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.
>
>
> --
> 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.
>
>
--
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.
