RowCountProjection failing on Criteria object with associations

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

RowCountProjection failing on Criteria object with associations

Adam Evans
I am using Hibernate Spatial with MySql and have a strange issue when
using hibernates criteria builder to create spatial queries. When I
run a Criteria without a RowCountProjection the query executes
successfully, when I add a RowCountProjection the query fails.

Looking at the generated sql, it looks like when creating the count
query the joins on our association are not being outputted to the sql
so we get the below:

Hibernate: select count(*) as y0_ from activity this_ where
within(location_p1_.location,?)
JDBCExceptionReporter - SQL Error: 1054, SQLState: 42S22
JDBCExceptionReporter - Unknown column 'location_p1_.location' in 'where clause'

Based on the sql outputted by hibernate I guess the exception is
correct as some joins are missing, these joins happen when I remove
the RowCountProjection.

The below query works:

-----
Polygon boundingBox = GeoHelper.createPolygonBox(53.481876, -2.233255,
10 /*distance in km */);
Criteria q = getSession().createCriteria(Activity.class);
q.createAlias("location.place", "location_place");
q.add(SpatialRestrictions.within("location_place.location", boundingBox));
return q.list();
--------

When it's updated to the below we get the sql exception

Polygon boundingBox = GeoHelper.createPolygonBox(53.481876, -2.233255,
10 /*distance in km */);
Criteria q = getSession().createCriteria(Activity.class);
q.createAlias("location.place", "location_place");
q.add(SpatialRestrictions.within("location_place.location", boundingBox));
q.setProjection(Projections.rowCount());

return q.list();

Has anyone seen similar behaviour before or know where's best to start
looking to fix this?

--
Thanks,
Adam
_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: RowCountProjection failing on Criteria object with associations

Karel Maesen
Administrator
Hi Adam,

Could you rerun the example but substituting for the SpatialRestrictions, a non-spatial Restriction? If you get the problem again, then it is clearly a Hibernate issue - most likely a configuration issue.

Regards,

Karel

On 17 Sep 2010, at 17:01, Adam Evans wrote:

> I am using Hibernate Spatial with MySql and have a strange issue when
> using hibernates criteria builder to create spatial queries. When I
> run a Criteria without a RowCountProjection the query executes
> successfully, when I add a RowCountProjection the query fails.
>
> Looking at the generated sql, it looks like when creating the count
> query the joins on our association are not being outputted to the sql
> so we get the below:
>
> Hibernate: select count(*) as y0_ from activity this_ where
> within(location_p1_.location,?)
> JDBCExceptionReporter - SQL Error: 1054, SQLState: 42S22
> JDBCExceptionReporter - Unknown column 'location_p1_.location' in 'where clause'
>
> Based on the sql outputted by hibernate I guess the exception is
> correct as some joins are missing, these joins happen when I remove
> the RowCountProjection.
>
> The below query works:
>
> -----
> Polygon boundingBox = GeoHelper.createPolygonBox(53.481876, -2.233255,
> 10 /*distance in km */);
> Criteria q = getSession().createCriteria(Activity.class);
> q.createAlias("location.place", "location_place");
> q.add(SpatialRestrictions.within("location_place.location", boundingBox));
> return q.list();
> --------
>
> When it's updated to the below we get the sql exception
>
> Polygon boundingBox = GeoHelper.createPolygonBox(53.481876, -2.233255,
> 10 /*distance in km */);
> Criteria q = getSession().createCriteria(Activity.class);
> q.createAlias("location.place", "location_place");
> q.add(SpatialRestrictions.within("location_place.location", boundingBox));
> q.setProjection(Projections.rowCount());
>
> return q.list();
>
> Has anyone seen similar behaviour before or know where's best to start
> looking to fix this?
>
> --
> Thanks,
> Adam
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: RowCountProjection failing on Criteria object with associations

GavinMartin
This post has NOT been accepted by the mailing list yet.
Thanks for great suggestion Karel!
I also faced same problem and now hope that your post will help me a lot in solving my issues.