-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: ORing in HQL
PostPosted: Wed Oct 27, 2004 2:03 pm 
Beginner
Beginner

Joined: Fri Oct 15, 2004 4:54 pm
Posts: 32
Hibernate version:
2.1.6

Mapping documents:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
   
<hibernate-mapping>
<class name="tess.Division" table="DIVISION">
    <id name="id"
        type="java.lang.Integer"
        column="DIVISION_ID">
        <generator class="assigned" />
    </id>
    <property
        name="name"
        type="java.lang.String"
        column="DIVISION_NAME"
        not-null="true"
        length="60"/>
</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
   
<hibernate-mapping>
<class name="tess.Person" table="PERSON">
    <id name="id"
        type="java.lang.Integer"
        column="PERSON_ID">
        <generator class="increment" />
    </id>
    <property
        name="name"
        type="java.lang.String"
        column="PERSON_NAME"
        not-null="true"
        length="60"/>
    <many-to-one
        name="division"
        class="tess.Division"
        not-null="true">
        <column name="DIVISION_ID" />
    </many-to-one>
</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
   
<hibernate-mapping>
<class name="tess.Report" table="REPORT">
    <id name="id"
        type="java.lang.Integer"
        column="REPORT_ID">
        <generator class="increment" />
    </id>
    <property
        name="report"
        type="java.lang.String"
        column="REPORT"
        not-null="true"
        length="255"/>
    <property
        name="status"
        type="java.lang.String"
        column="STATUS"
        not-null="true"
        length="12"/>
    <many-to-one
        name="author"
        class="tess.Person"
        not-null="true">
        <column name="PERSON_ID" />
    </many-to-one>
</class>
</hibernate-mapping>



Code between sessionFactory.openSession() and session.close():
Code:
String HQL =
   "from tess.Report as report " +
   "where (report.status != 'Draft') or (report.author.division.id = ?) ";
List parms = new ArrayList();
parms.add(new Integer(1));
List list =
   session.find(HQL, parms.toArray(), new Type[] {Hibernate.INTEGER});


Full stack trace of any exception that occurs:

Name and version of the database you are using:
Sybase ASE 12.5

The generated SQL (show_sql=true):
Code:
select report0_.REPORT_ID as REPORT_ID,
       report0_.REPORT as REPORT,
       report0_.STATUS as STATUS,
       report0_.PERSON_ID as PERSON_ID
from REPORT report0_, PERSON person1_
where (((report0_.STATUS!='Draft' ))
     or((person1_.DIVISION_ID=1
     and report0_.PERSON_ID=person1_.PERSON_ID)))


Debug level Hibernate log excerpt:
n/a

the setup is i have reports that are written by people, each of whom is assigned to a division. reports can have two statuses, 'draft' or 'complete', and folks in the same division can see draft reports by each other. so my find, in english, is "give me all the complete reports and the draft reports from folks in my division". that is what the HQL above is meant to convey.

the sql that gets generated doesn't do what i want it to. the problem is that the first part doesn't get the join condition so it gets a cartesion product. bottom line is i get n copies of non-draft reports, where n is the number of person records.

basically, the sql for the where is (A | (B & C)) when it needs to be ((A | B) & C)

so, does my HQL look okay?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 29, 2004 1:46 pm 
Beginner
Beginner

Joined: Fri Oct 15, 2004 4:54 pm
Posts: 32
i found a bug report on this from the end of september that referenced two or three previous bug reports on the same subject, all of which were rejected. the comment, and i have no reason to disbelieve it, was "you should use a from clause join for your problem. There is reasoning behind the current behavior."

so my two questions are:
1) what is a "from join clause"?
2) what is the reasoning behind the current behavior? either i don't understand HQL very well (which is, of course, true), or this is unexpected behavior

thanks.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 29, 2004 2:31 pm 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
don't have much time but try
something like

select report
from tess.Report as report
join report.author auth
join auth.division div
where (report.status != 'Draft'
or div.id = ?);

_________________
Anthony,
Get value thanks to your skills: http://www.redhat.com/certification


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.