6 Replies Latest reply on Oct 15, 2002 12:59 PM by Clarke Wood

    EJB-QL question

    Clarke Wood Newbie

      I'm running into some difficulty formulating an EJB-QL query.

      I have gotten two different queries working on their own but am trying to combine the two into a single query but am not having much luck.


      Here are the two queries I have gotten working:


      <query-method>
      <method-name>ejbSelectMachinesByUser</method-name>
      <method-params>
      <method-param>blah.User</method-param>
      </method-params>
      </query-method>
      <result-type-mapping>Remote</result-type-mapping>
      <ejb-ql><![CDATA[SELECT DISTINCT OBJECT(m) FROM User u, Machine m, IN (m.machineClasses) mc, IN (mc.permissions) p WHERE u = ?1 AND p.user = u]]></ejb-ql>



      <query-method>
      <method-name>ejbSelectMachinesByUser</method-name>
      <method-params>
      <method-param>blah.User</method-param>
      </method-params>
      </query-method>
      <result-type-mapping>Remote</result-type-mapping>
      <ejb-ql><![CDATA[SELECT DISTINCT OBJECT(m) FROM User u, Machine m, IN (m.machineClasses) mc, IN (mc.permissions) p WHERE u = ?1 AND p.userGroup MEMBER OF u.userGroups]]></ejb-ql>



      Here is the combined query I've been working on (unsuccessfully):


      <query-method>
      <method-name>ejbSelectMachinesByUser</method-name>
      <method-params>
      <method-param>blah.User</method-param>
      </method-params>
      </query-method>
      <result-type-mapping>Remote</result-type-mapping>
      <ejb-ql><![CDATA[SELECT DISTINCT OBJECT(m) FROM User u1, User u2, Machine m, IN (m.machineClasses) mc, IN (mc.permissions) p WHERE (u1 = ?1 AND p.user = u1) OR (u2 = ?1 AND p.userGroup MEMBER OF u2.userGroups)]]></ejb-ql>



      The desired result of this query is simply to merge the results of the two other queries. I have a feeling I'm making a simple syntax mistake, but I've been at this for a day and a half now so I figured I'd ask. Is this possible to do in one EJB-QL query? Or do I need to use JBoss-QL instead?

        • 1. Re: EJB-QL question
          Alexey Loubyansky Master

          What is the error you are getting?

          BTW, the first query could be rewritten to:
          SELECT DISTINCT OBJECT(m) FROM Machine m, IN (m.machineClasses) mc, IN (mc.permissions) p WHERE p.user = ?1

          • 2. Re: EJB-QL question
            Clarke Wood Newbie

            I'm not getting an error, but I'm also not getting the desired results. A permission has a machine class and either a User or a UserGroup but not both. Users can belong to UserGroups. Machines belong to MachineClasses.

            The two possible paths of relation are:
            User -> Permission -> MachineClass -> Machine
            or
            User -> UserGroup -> Permission -> MachineClass -> Machine

            I'm trying to write a query that will return all machines to which a user is related. When I run the first or second query I posted earlier, I get the correct lists of machines. When I run the combined query I posted however, an empty set is returned.

            I'm thinking that maybe I've written the third query so that it will only return a Machine if both a User and a UserGroup the User belongs to are associated with the same Permission, which should never be the case.

            • 3. Re: EJB-QL question
              Alexey Loubyansky Master

              Looks weird to me either. Could you please increase log level to DEBUG for category org.jboss.ejb.plugins.cmp in server/default/conf/log4j.xml file and check the generated sql statement for this query?
              Then we'll see were the problem is.

              • 4. Re: EJB-QL question
                Clarke Wood Newbie

                Here is the generated SQL for query 1:

                2002-10-14 18:39:16,340 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.blah/User.ejbSelectMachinesByUserTest1] Executing SQL: SELECT DISTINCT t0_m.id FROM machines t0_m, machine_classes t2_mc, machine_class_membership t3_m_machineClasses_RELATION_TAB, machine_class_permissions t4_p, users t1_p_user WHERE ((t1_p_user.id=?)) AND (t0_m.id=t3_m_machineClasses_RELATION_TAB.machine_id AND t2_mc.id=t3_m_machineClasses_RELATION_TAB.machine_class_id AND t4_p.user_id=t1_p_user.id AND t2_mc.id=t4_p.machine_class_id)


                Here is the generated sql for query 2:

                2002-10-14 18:39:16,424 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.blah/User.ejbSelectMachinesByUserTest2] Executing SQL: SELECT DISTINCT t0_m.id FROM users t1_u, machines t0_m, machine_classes t4_mc, machine_class_membership t5_m_machineClasses_RELATION_TAB, machine_class_permissions t6_p, user_groups t2_u_userGroups, user_group_membership t7_u_userGroups_RELATION_TABLE, user_groups t3_p_userGroup WHERE ((t1_u.id=?) AND (t2_u_userGroups.id = t3_p_userGroup.id)) AND (t1_u.id=t7_u_userGroups_RELATION_TABLE.user_id AND t2_u_userGroups.id=t7_u_userGroups_RELATION_TABLE.user_group_id AND t0_m.id=t5_m_machineClasses_RELATION_TAB.machine_id AND t4_mc.id=t5_m_machineClasses_RELATION_TAB.machine_class_id AND t6_p.user_group_id=t3_p_userGroup.id AND t4_mc.id=t6_p.machine_class_id)


                Here's the generated sql for the combined query:

                2002-10-14 18:39:16,264 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.blah/User.ejbSelectMachinesByUser] Executing SQL: SELECT DISTINCT t0_m.id FROM users t1_u1, users t3_u2, machines t0_m, machine_classes t6_mc, machine_class_membership t7_m_machineClasses_RELATION_TAB, machine_class_permissions t8_p, user_groups t5_p_userGroup, users t2_p_user, user_groups t4_u2_userGroups, user_group_membership t9_u2_userGroups_RELATION_TABLE WHERE (((t1_u1.id=?) AND (t2_p_user.id = t1_u1.id)) OR ((t3_u2.id=?) AND (t4_u2_userGroups.id = t5_p_userGroup.id))) AND (t0_m.id=t7_m_machineClasses_RELATION_TAB.machine_id AND t6_mc.id=t7_m_machineClasses_RELATION_TAB.machine_class_id AND t8_p.user_group_id=t5_p_userGroup.id AND t8_p.user_id=t2_p_user.id AND t3_u2.id=t9_u2_userGroups_RELATION_TABLE.user_id AND t4_u2_userGroups.id=t9_u2_userGroups_RELATION_TABLE.user_group_id AND t6_mc.id=t8_p.machine_class_id)

                • 5. Re: EJB-QL question
                  Alexey Loubyansky Master

                  Do you have some sql client? Can you run the problematic query in it?
                  It's difficult for me to read this huge sql and tell you what's wrong in it not even knowing the db structure.
                  If you believe it's a bug, please, post a simple test case that I can deploy and figure it out.

                  • 6. Re: EJB-QL question
                    Clarke Wood Newbie

                    Well, I finally tracked down the issue and determined that it's just not feasible to write the query I need in EJB-QL. I have now switched to using <declared-sql> and everything is working great. Thanks for all your help!