hi. im using mysql db. i have to tables in database : employees and registration. in registration table are written employees registered working hours(registering by projects tasks). hours are registered every day. i need to make query which returns a list of all employees and their registered hours during some period. if employee didnt registered hours during that period, query should return employee and null. i can write this query code in sql, but i cant make it work in hql. here is sql which works fine:
Code:
SELECT Employees.firstname, sum(reg.hours)
FROM (select * from registration where registration.date
BETWEEN "2007-01-01" and "2007-01-31") as reg
RIGHT JOIN employees
ON Employees.id=reg.EmployeeID group by Employees.id;
here are my tables in mapping file:
Code:
<hibernate-mapping package="ideja.insight.domain" default-lazy="false">
...
<class name="Employable" table="employees" polymorphism="explicit"
discriminator-value="BASE" abstract="true">
<id name="id" column="id">
<generator class="identity"/>
</id>
<discriminator column="discriminator" type="string"/>
<property name="firstName" column="firstname"/>
<property name="lastName" column="secondname"/>
<property name="email" column="email"/>
<property name="login" column="login"/>
<property name="password" column="password"/>
<property name="type" column="type" />
<many-to-one name="profile" column="resourceprofileid"/>
<subclass name="Employee" discriminator-value="EMPL">
</subclass>
<subclass name="Applicant" discriminator-value="APPL">
</subclass>
</class>
<class name="Registration" table="registration">
<id name="id" column="id">
<generator class="identity"/>
</id>
<property name="date" column="date" />
<property name="hours" column="hours"/>
<property name="freezed" column="freezed"/>
<many-to-one name="employable" column="employeeid"/>
<many-to-one name="projectsTasks" column="projectstasksid"/>
</class>
employable is parent class for employee and applicant.
i tried the fallowing hql query:
Code:
HibernateTemplate template = getHibernateTemplate();
List result = template.find("
select b.id, sum(reg.hours) from Registration reg " +
"right join reg.employable b " +
"where reg.date is null or reg.date
between '2007-01-01' and '2007-01-31' " +
"group by b.id
");
The problem is: if some employee registered only for example on 2007-02-15 date. this query would not return this employee at all( i expect it to return this employee id and null for sum(reg.hours).
If there are no entries in registration table at all it returns all employees and null hours for everybody(works fine).
if there are registered hours during specified period in registration table, it works fine too.
So the problem is that only if employee has registered hours on different period than specified, this query doesn't return this employee at all.
im new to hibernate, and i dont understand why if i dont specify "reg.date is null" in where clouse it only returns matching rows like inner join in sql(in registration and emplloyes ). so maybe someone knows how to convert sql that i specified above to fully working hql[/code]
Login
FAQ
Search