Hello
I'm newbie and don't know how to make the following query:
I have 2 tables "project" and "module", where a project has many modules, these two tables have a Boolean field named "deleted", which is true if any record is deleted.
Now I need to make a query to bring all projects that not has been deleted, with theirs respective not deleted modules too. Now the problem is that if there's a project without modules or with deleted modules, it must be returned anyway.
I made the following query:
Code:
select p, pm from Project p
join fetch p.modules as pm
where p.deleted = false and pm.deleted = false
order by p.title, pm.title
The problem with this query is that if a project hasn't associated modules, that project wouldn't be returned.
To resolve this I have done the following:
Code:
from Project where project.deleted = false order by project.title
then it returns all instances of project. Later, I make the following query for each of the project instances
Code:
select m from Module m
join m.project as mp
where m.deleted = false and mp.idProject = ?
order by m.title
The problem is that obviously execute too many queries, where maybe the information could be brought in just one query.
I would appreciate any help. In advance, thank you very much.
Login
FAQ
Search