- This topic has 5 replies, 2 voices, and was last updated 12 years, 11 months ago by cconway.
-
AuthorPosts
-
urklnmeMemberIs 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?
cconwayMemberHave 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.
urklnmeMemberThat 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.
cconwayMemberOk, 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
urklnmeMemberThank you very much. Thanks for helping out a newbie.
It really has made a difference for me.
cconwayMemberI’m happy I could help!
-
AuthorPosts