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
>
>
>
--
View this message in context: http://www.nabble.com/Crafting-the-correct-Query-tf4250925.html#a12393185
Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.