2 Replies Latest reply on Nov 25, 2002 11:46 PM by erik777

    CMR one-to-many not working

    erik777

      I have a query that traverses Domains-->Roles-->UserRoles-->Users.

      It works until I include the last relationship in the query, which I defined in the ejb-jar as a one-to-many. UserRoles is actually an intersecting key for Roles and Users, with a foreign key for each (roleId, userId).

      Thus, the following query works:

      SELECT DISTINCT OBJECT(d)
      FROM domains AS d, IN (d.roles) r, IN (r.userRoles) ur
      WHERE r.name ?1 AND
      ur.userId < 2

        • 1. Re: CMR one-to-many not working
          erik777

          Correction: The latter query returns NO rows, not one row. The first query correctly returns two rows.

          • 2. Re: CMR one-to-many not working
            erik777

            Well, I diagnosed if further. Here is the SQL it is blowing up on:

            mysql> SELECT DISTINCT t0_d.id FROM Domains t0_d, Roles t1_r, UserRoles t2_ur, Users t3_ur_user WHERE (t1_r.name 'ERIK' AND t2_ur.userId < 2 AND t3_ur_user.id < 2) AND (t2_ur.user=t3_ur_user.id AND t1_r.id=t2_ur.roleId AND t0_d.id=t1_r.domainId);

            ERROR 1054: Unknown column 't2_ur.user' in 'where clause'

            It appears to be getting "user" instead of using "userId" in the join with the following relationship defined in the ejb-jar:

            <ejb-relation>
            <ejb-relation-name>User-UserRoles</ejb-relation-name>
            <ejb-relationship-role>
            <ejb-relationship-role-name>user-has-userroles</ejb-relationship-role-name>
            One
            <relationship-role-source>
            <ejb-name>UserBean</ejb-name>
            </relationship-role-source>
            <cmr-field>
            <cmr-field-name>userRoles</cmr-field-name>
            <cmr-field-type>java.util.Set</cmr-field-type>
            </cmr-field>
            </ejb-relationship-role>
            <ejb-relationship-role>
            <ejb-relationship-role-name>userroles-belongs-to-user</ejb-relationship-role-name>
            Many
            <cascade-delete/>
            <relationship-role-source>
            <ejb-name>UserRoleBean</ejb-name>
            </relationship-role-source>
            <cmr-field>
            <cmr-field-name>user</cmr-field-name>
            </cmr-field>
            </ejb-relationship-role>
            </ejb-relation>

            However, if I change "user" to "userId" here, it blows up on deploy, choking on the query:

            22:55:44,597 ERROR [EjbModule] Starting failed
            org.jboss.deployment.DeploymentException: Error compiling ejbql; - nested throwable: (org.
            plugins.cmp.ejbql.ParseException: Encountered "<" at line 5, column 33.
            Was expecting one of:
            "=" ...
            "<>" ...
            )
            at
            org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.(JDBCEJBQLQuery.java:46)

            PLEASE HELP!