0 Replies Latest reply on Jan 19, 2003 7:54 PM by Ben Litchfield

    CMR self-reference problem

    Ben Litchfield Newbie

      I am having some problems doing a finder on the table defined below. I am using jboss3.0.5

      Here is my EJB-QL
      SELECT DISTINCT OBJECT(t) FROM Thread t WHERE t.topic.topicId = ?1 AND t.thread IS NULL

      JBossCMP is generating the following SQL from this
      SELECT DISTINCT t0_t.thread_id FROM thread t0_t, topic t1_t_topic WHERE (t1_t_topic.topic_id = ? AND t0_t.parent_thread_id IS NULL) AND (t0_t.topic_id=t1_t_topic.topic_id AND t0_t.parent_thread_id=t0_t.thread_id)

      notice the very last equality statement(t0_t.parent_thread_id=t0_t.thread_id), this should not be here and I don't know why it is.

      when it should be
      SELECT DISTINCT t0_t.thread_id
      FROM thread t0_t, topic t1_t_topic
      WHERE
      (t1_t_topic.topic_id = 60 AND t0_t.parent_thread_id IS NULL) AND
      (t0_t.topic_id=t1_t_topic.topic_id )

      Is this a bug or am I declaring something wrong?


      Here are my defined methods
      public abstract java.util.Collection getThreads();
      public abstract void setThreads(java.util.Collection threads);
      public abstract org.kelut.discourse.entity.ThreadLocal getThread();
      public abstract void setThread(org.kelut.discourse.entity.ThreadLocal thread);


      Here is my DB table definition
      CREATE TABLE thread
      (
      thread_id integer NOT NULL,
      parent_thread_id integer,-- REFERENCES thread thread_id)
      topic_id integer,
      subject varchar (4000),
      body varchar (4000),
      created timestamp,
      created_by integer,
      PRIMARY KEY (thread_id)
      );


      Here is ejb-jar.xml
      <ejb-relation >
      <ejb-relation-name>thread-cmp20-thread-cmp20</ejb-relation-name>

      <ejb-relationship-role >
      <ejb-relationship-role-name>thread-cmp20-2-has-thread-cmp20-1</ejb-relationship-role-name>
      Many
      <relationship-role-source >
      <ejb-name>Thread</ejb-name>
      </relationship-role-source>
      <cmr-field >
      <cmr-field-name>thread</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>

      <ejb-relationship-role >
      <ejb-relationship-role-name>thread-cmp20-1-has-thread-cmp20-2</ejb-relationship-role-name>
      One
      <relationship-role-source >
      <ejb-name>Thread</ejb-name>
      </relationship-role-source>
      <cmr-field >
      <cmr-field-name>threads</cmr-field-name>
      <cmr-field-type>java.util.Collection</cmr-field-type>
      </cmr-field>
      </ejb-relationship-role>

      </ejb-relation>

      Here is jbosscmp-jdbc.xml
      <ejb-relation>
      <ejb-relation-name>thread-cmp20-thread-cmp20</ejb-relation-name>
      <foreign-key-mapping/>

      <ejb-relationship-role>
      <ejb-relationship-role-name>thread-cmp20-2-has-thread-cmp20-1</ejb-relationship-role-name>
      <fk-constraint>true</fk-constraint>
      <key-fields/>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>thread-cmp20-1-has-thread-cmp20-2</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>threadId</field-name>
      <column-name>parent_thread_id</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      </ejb-relation>