Hi All,
I am writing a web app to generate lists of people in our organization (University) to use for mailing. I have quite complex requirements for the search part of the app, so I am looking for advice or hints in the ways this is implementable, by people who have had more experience with Hibernate (and SQL, actually) than myself.
The main entities are person, structure, function, and attribute. An attribue is a kind of person category. Person to attribute is a simple many-to-many relation. A person can have several functions, and belong to several structures (organizational units or departments if you wish), and both are dependent, so one person belongs to structure X with the function A. A more concrete example :
John is a assistant in the Applied Math Lab and
John is also a postdoc student in the CS dep.
This is modelized with a table person_structure_function table, that has the fields person_id, structure_id and function_id. I had to make this an entity, even thought it's just little more than a join table, since it references 3 tables.
Now, my app must be able build criteria (whether using the Criteria API or in HQL) based on groups. A group is a collecton of functions, structures, attributes and direct references to persons. Though most groups contain just one or a few of only one of the categories, it is possible to mix them.
Then, the groups are regrouped into categories in the search mask presentation, and for each category, one can additionnally set a filter by Structure.
Finally I will add a general filter on the person's fields.
One additional complication is that the structures are in a tree, i.e. the Structure table/entity references itself. Structure has a parent structure and a list of children structures. Only the top-level structure will be presented to the user, but the query must select the people belonging to this structure and all its children, no limit of depth.
I have started to test some HQL, but if possible would rather implement the final solution with the Criteria API if possible, since the whole query must be dynamically built every time and I am affraid of string concatenation problems. I also have no idea on how to select the (sub)children of structure in the where clause.
For now I only have some HQL that I am writing and testing in Eclipse tools. Once I have the whole query I'll try to translate it to my the criteria API to make it dynamic. The HQL so far :
Code:
from PersonStructureFonction psf
where
(
( //start of a category
(
( //start of a group
(
//conditions on the function
fonctionId in (5101002, 5111006) or
fonctionId in (5101006)
) or
(
//condition on an atribute entity
1001 = some (select p.id from psf.person p)
)
) //end of group
//possibly more groups :
(
...
)
) //end of the groups list
//structure filter for the whole category
and
(
structure_id in (200000) // recursive query for the children ??
)
) //end of the category
or
(
//repeat for next category
)...
and
(
//general filter conditions (e.g. something like p.birth_date between Y and Y)
)
)
Before going further and possibly invest a lot of time and efforts in something that will not be efficient or even work good, I would welcome any input from somebody who has had experience (or just good ides) in that kind of functionalities, namely:
1. Build query dynamically with numerous imbricated criteria using many-to-many relations
2. Search using an element of a tree as filter, but using all its children as well (person belongs to department X or any of its children)
To give an idea of the volume of data, I've got a few thousands rows in the person table and a few hundreds in the functions and structures. Not huge but enough to try to limit the number of joins.
Sorry for the long post, I hope that at least it is understandable ! Thanks to those who had the patience to read it to the end, and even more thanks to any welcome help !
Regards,
Pierre
Login
FAQ
Search