1 Reply Latest reply on Jan 12, 2004 1:02 AM by aparaapara

    Strange SQL finder generation

    aparaapara

      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?

      Thanks.
      -AP_


        • 1. Re: Strange SQL finder generation
          aparaapara

          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.

          Thanks.
          -AP_