Deriving an attribute from ManyToMany-linked objects
I've got two entities A and B with a many-to-many relationship. Entity A has an enum attribute C saved as string. Now I want B to have the maximum C from all connected As.
I could write a JPA query easily, but where do you put queries that span over two entities? I'm working with DAOs that only work with one entity at a time. I thought about getting all As from a B in a loop in Java so that it all runs in one transaction, but I think this might result in many selects where only one is needed.
Any recommendation is welcome.
Consider the following example:
a jewelry store has a OneToMany relationship to jewels. Now I'd like to have a transitive attribute "biggestPieceSize" which reflects the max() value of the connected entities' size attribute.
Looping is highly inefficient solution because it unnecessarily fetches to much unneeded data from the db.
In general, write a JPQL query, pick any entity involved in the query and place the query into the DAO class for that entity.
Since you are returning B here, it seems to me that placing the query in DAO<B> is the most natural choice.