Hi Michael, hi James,
I think the query proposed by James is correct and should do the job,
except in case the query should consider the user assigned to the task
directly (Class Task has a persistent field assigneduser). Then we need
to add an OR clause checking the assigneduser:
SELECT task FROM Task task, IN(t.team.userCollection) user
WHERE user.username = :username OR task.assigneduser.username = :username
Another syntax would be
SELECT task FROM Task task JOIN task.assigneduser assigneduser JOIN
task.team team JOIN team.userCollection user
WHERE assigneduser.username = :username OR user.username = :username
You should consider using LEFT OUTER JOIN clauses, if the relationship
fields used in the JOIN path might be null of if the collection of users
in a team might be empty.
I hope this helps.
Regards Michael
> What JPQL have you tried so far?
>
> How about, "Select t from Task t, IN(t.team.userCollection) user where
> user.username = :username"
>
> - James
>
>
> Woods, Michael G wrote:
>
>> In my following project, the Users are in (multiple) Teams and Tasks are
>> assigned both a User and Team. I'm having a problem crafting a query
>> that will return Tasks assigned to each Team the User is in, whether
>> assigned directly to that User or not.
>>
>>
>>
>> After trying numerous queries, many resulting in some form of syntax
>> error or Exception thrown because it didn't like the IndirectList type
>> and was expecting another type (Team), I've run out of ideas.
>>
>>
>>
>> I have a User object and am able to get all Tasks assigned just fine,
>> however going the extra step and getting all Tasks associated to the
>> Teams the User is in does not work. Returning collections via
>> user.getTaskCollection() then a teams.getTaskCollection() works just
>> fine, though I need to perform this as a query due to certain other
>> designs.
>>
>>
>>
>> Is there a query that should get this done, or does the code need to be
>> changed a bit? I've included some bits below:
>>
>>
>>
>>
>>
>> Users:
>>
>> @Id
>>
>> @Column(name = "USERNAME", nullable = false)
>>
>> private String username;
>>
>>
>>
>> ...other generic User data...
>>
>>
>>
>> @OneToMany(mappedBy = "assigneduser")
>>
>> private java.util.Collection <Task> taskCollection;
>>
>>
>>
>> @ManyToMany(mappedBy="userCollection")
>>
>> private java.util.Collection <Team> teamCollection;
>>
>>
>>
>> Task:
>>
>> @JoinColumn(name = "ASSIGNEDUSER")
>>
>> @ManyToOne
>>
>> private Users assigneduser;
>>
>>
>>
>> @JoinColumn(name = "TEAMID", referencedColumnName="TEAMID")
>>
>> @ManyToOne
>>
>> private Team team;
>>
>>
>>
>> Team:
>>
>> @Id @GeneratedValue(generator="teamid_gen")
>>
>> @Column(name = "TEAMID", nullable = false)
>>
>> private BigDecimal teamid;
>>
>>
>>
>> @Column(name = "NAME")
>>
>> private String name;
>>
>>
>>
>> @OneToMany(mappedBy = "team")
>>
>> private java.util.Collection <Task> taskCollection;
>>
>>
>>
>> @ManyToMany(fetch=FetchType.EAGER)
>>
>> @JoinTable(name = "team_users",
>>
>> joinColumns = {_at_JoinColumn(name = "TEAMID",
>> referencedColumnName = "TEAMID")},
>>
>> inverseJoinColumns = {_at_JoinColumn(name = "USERNAME",
>> referencedColumnName = "USERNAME")})
>>
>> private java.util.Collection <Users> userCollection;
>>
>>
>>
>>
>>
>> Thanks,
>>
>> Mike
>>
>>
>>
>>
>
>
--
Tech_at_Spree Engineering GmbH Tel.: +49/(0)30/235 520-33
Buelowstr. 66 Fax.: +49/(0)30/217 520-12
10783 Berlin mailto:mbo.tech_at_spree.de
Geschaeftsfuehrung: Anna-Kristin Proefrock
Sitz Berlin, Amtsgericht Charlottenburg, HRB 564 52