I have defined the following relationships:
Subscription (n)--Subscriber-->(1) Profile
Subscription (n)--Subscribee-->(1) Profile
In other words, a Subscription has two relations to a Profile. In one case the role is of a SubsdriberProfile and in the other case the role is of a SubscribeeProfile.
Now, I am trying to find any subscriptions that have anything to do with a particular profile. I use the following query:
select object(s) from subscription s where s.subscriberProfileValue = ?1 or s.subscribeeProfileValue = ?1
What JBOSS 3.2.3 is generating is:
20:58:36,232 DEBUG [Subscription#findByProfile] Executing SQL: SELECT t0_s.id, t0_s.id, t0_s.subscriberNote, t0_s.subscribeeNote, t0_s.createDate, t0_s.subscriberprofileid, t0_s.subscriberid, t0_s.subscribeeprofileid, t0_s.subscribeeid FROM subscription t0_s, profile t1_s_subscriberProfileValue, profile t2_s_subscribeeProfileValue WHERE ((t1_s_subscriberProfileValue.id=?) AND t0_s.subscriberprofileid=t1_s_subscriberProfileValue.id) OR ((t2_s_subscribeeProfileValue.id=?) AND t0_s.subscribeeprofileid=t2_s_subscribeeProfileValue.id)
It's doing a 3 table (unbounded) join, where I would expect it to just do something like this:
SELECT t0_s.id, t0_s.id, t0_s.subscriberNote, t0_s.subscribeeNote, t0_s.createDate, t0_s.subscriberprofileid, t0_s.subscriberid, t0_s.subscribeeprofileid, t0_s.subscribeeid FROM subscription t0_s WHERE t0_s.subscriberprofileid=? OR t0_s.subscribeeprofileid=?
Is there a way for me to rework my query so that the above is produced instead of a 3 table join?
Is there a standard EJB way to alter the construction of this query so that it is not so expensive? I am going to break it up into two different queries, but I was hoping that there would be an easier way.