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

    CMR one-to-many not working

    Erik Sliman Apprentice

      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:

      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
          Erik Sliman Apprentice

          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
            Erik Sliman Apprentice

            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:


            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:
            "=" ...
            "<>" ...

            PLEASE HELP!