I’m trying to combine 2 queries into one but I’m not sure what the best way is.
Basically, I want to compare the sum of products from this week compared to last week.
Here are 2 sql queries that do what I need:
Code:
Select sum(amount) from products where productCategoryId = 10 and day=This week <--date range pseudo code
Select sum(amount) from products where productCategoryId = 10 and day=last week
I have a Product object that is mapped with hibernate and I have zero problems running these 2 queries separately.
What I want to do is combine the 2 like you would normal sql
Code:
Select sum(current.amount) as current_amount, sum(previous.amount) as previous_amount from products current,
products previous where
current.productCategoryId=10 and current.day=this week and
previous.productCategoryId=10 and previous.day=last week
Theta style joins looked promising and almost got what I needed. I forgot to account for a case when the last week requirement doesn’t exist.
So my actual query looks like:
Code:
select
sum(current.amount) as current_amount,
sum(previous.amount) as previous_amount
from Products current
left join Products previous previous.productCategoryId=10 and previous.day=last week
where current.productCategoryId=10 and current.day=this week
This way, if the previous week doesn’t exist then the sum for the current week is still returned.
Is there an HQL equivalent for this? I’m not sure how to do it without the on keyword.
Im’ using Hibernate vs 3.2.6
Thanks,
Login
FAQ
Search
