Hibernate version:
3.2.5
Name and version of the database you are using:
- MySQL 5.0.41-community-nt
- SQLServer 2005
The ideal query sql:
Code:
select count(slide_id) from (
select distinct f.folder_slide_id, s.slide_id
from space_slide as s
left join space_folder_slide as f on (f.slide_id = s.slide_id)
left join space_attribute_value as a on (a.slide_id = s.slide_id)
where s.is_archived = 0
and
(s.slide_id not in (select slide_id from space_folder_slide)
and (s.external_ref like '%win%' or a.value like '%win %'))
or
(f.folder_id in (select folder_id from space_folder where space_folder.is_resource_name = 0)
and (s.external_ref like '%win%' or a.value like '%win%'))
group by s.slide_id, f.folder_slide_id) as res_count;
We need to support both database server.I've read pretty much all the pages of the forum to find a solution on this, but the only solution found were:
- Using Criteria API with the Projection call, but we use Query, not Criteria
- Using ScrollableResults and getting to last row and then getting the row number, but this causes a performance issue on MySQL.
This is the hibernate query I tried:Code:
select count(s.id) from (
select distinct f.id, s.id
from Slide s
left join s.folders f
left join s.attributeValues a
where s.archived = false
and (s.folders.size = 0 and (s.filename like '%win%' or s.externalSlideReference like '%win%' or f.externalSlideReference like '%win%' or a.value like '%win%'))
or ( f.folder.id in (6, 4, 7, 10, 11, 3, 9) and (s.filename like '%win%' or s.externalSlideReference like '%win%' or f.externalSlideReference like '%win%' or a.value like '%win%'))
group by s.id, f.id) res_count
But Hibernate doesn't support select queries as from clause.
What can I do to fix this ?
Login
FAQ
Search
