- This topic has 5 replies, 2 voices, and was last updated 13 years, 1 month ago by
.
-
Posts
-
Is it possible in Hibernate/scaffolding/JPA/Spring MVC to use the child fields in a one-to-many/many-to-one relationship in the where clause of a named query?
i have tried many variations of:
select parent ParentClass as parent, child ChildCollection as child
where child.dateOfBirth = ?1and also
select parent ParentClass as parent, child parent.Child as child
where child.dateOfBirth = ?1I want to retrieve from the database (oracle) all the parent records (and their associated children records) with children who were born on the date specified.
note: the foreign key (parent_id) that appears (parent_id) in the database table for the child does not appear in the scaffolded domain bean of the child, so I can not even write my own queries.
Solutions?
Have you tried the JOIN keyword? I think it would go something like this:
SELECT parent FROM ParentClass parent JOIN parent.children child WHERE child.dateOfBirth = ?1
where “children” is the relationship name.
That is awesome! It definitely makes things easier.
I have read that there is no guarantee of unique results when querying on a one-to-many, so I used a linkedhashset for the results of the query.
In the linkedhashset for the results, I am seeing the same parent id twice, once with a child that has a matching date of birth and once with a child that does not match date of birth)
Is there a way to prevent this or a workaround that will not be a performance hit.
Ok, I think that makes sense from a JPQL perspective. I’d try adding a DISTINCT keyword as shown in the example under section 10.2.2.2 in this document.
“Distinct” is described in section 10.2.7 of that same reference document.
HTH
