JOIN syntax for HQL . Need help!

[Eugene Shaforostov]

I have two tables

create table News (
id  int(11) not null primary key autho_increment,
title varchar(120) not null,
 .....
 .....
 .....
author_id tinyint(4)
);
 
create table Users (
id  tinyint(4) not null primary key autho_increment,
name varchar(50) not null,
 .....
 .....
 .....
);

I need to join this tables by News.author_id and Users.id.
When I trying to query

from News as news join Users as users with news.author_id=users.id

I get the following error:
QuerySyntaxException: Path expected for join!

Please help me to resolve this problem.
Thanks in advance for any help.

[Edvins Reisons]

Can you post the mappings?
See also in the Hibernate documentation, in particular that, to be joinable, the entities need to be associated.

[Eugene Shaforostov]

Thanks, Edvins, I already read this reference, but the question remains.

Mapping for table News
News.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"  "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="News" table="news">
<id name="id" type="int" unsaved-value="null">
<column name="id" sql-type="int(11)" not-null="true"/>
<generator class="native"/>
</id>
<property name="title">
<column name="title" sql-type="varchar(120)" not-null="true"/>
</property>
<property name="shortnews">
<column name="short" sql-type="text" not-null="true"/>
</property>
<property name="fullnews">
<column name="full" sql-type="text" not-null="true"/>
</property>
<property name="date">
<column name="date" sql-type="date" not-null="true"/>
</property>
<property name="visits">
<column name="visits" sql-type="int(11)" not-null="true"/>
</property>
<property name="comment">
<column name="comment" sql-type="smallint(4)" not-null="true"/>
</property>
<property name="catid">
<column name="catid" sql-type="tinyint(4)" not-null="true"/>
</property>
<property name="author_id">
<column name="author_id" sql-type="tinyint(4)" not-null="true"/>
</property>
</class>
</hibernate-mapping>

Mapping for table Users
Users.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"  "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="Users" table="users">
<id name="id" type="int" unsaved-value="null">
<column name="id" sql-type="tinyint(4)" not-null="true"/>
<generator class="native"/>
</id>
<property name="name">
<column name="name" sql-type="varchar(50)" not-null="true"/>
</property>
<property name="password">
<column name="password" sql-type="varchar(50)" not-null="true"/>
</property>
<property name="status">
<column name="status" sql-type="varchar(15)" not-null="true"/>
</property>
<property name="reg_date">
<column name="reg_date" sql-type="date" not-null="true"/>
</property>
</class>
</hibernate-mapping>

[Edvins Reisons]

To use the join syntax, you need a relationship defined (I guess it is a many-to-one in this case) in the mappings. Without it, one can use a query like this:

from News as news, Users as users where news.author_id=users.id

[Eugene Shaforostov]

Thanks, this query works fine.
But I want to understand how use association mappings.
Mapping files should looks like this?
News.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"  "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="News" table="news">
<id name="id" type="int" unsaved-value="null">
<column name="id" sql-type="int(11)" not-null="true"/>
<generator class="native"/>
</id>
<property name="title">
<column name="title" sql-type="varchar(120)" not-null="true"/>
</property>
<property name="shortnews">
<column name="short" sql-type="text" not-null="true"/>
</property>
<property name="fullnews">
<column name="full" sql-type="text" not-null="true"/>
</property>
<property name="date">
<column name="date" sql-type="date" not-null="true"/>
</property>
<property name="visits">
<column name="visits" sql-type="int(11)" not-null="true"/>
</property>
<property name="comment">
<column name="comment" sql-type="smallint(4)" not-null="true"/>
</property>
<property name="catid">
<column name="catid" sql-type="tinyint(4)" not-null="true"/>
</property>
<many-to-one name="users" column="author_id" class="Users" insert="false" update="false"/>
</class>
</hibernate-mapping>

Users.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"  "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="Users" table="users">
<id name="id" type="int" unsaved-value="null">
<column name="id" sql-type="tinyint(4)" not-null="true"/>
<generator class="native"/>
</id>
<property name="name">
<column name="name" sql-type="varchar(50)" not-null="true"/>
</property>
<property name="password">
<column name="password" sql-type="varchar(50)" not-null="true"/>
</property>
<property name="status">
<column name="status" sql-type="varchar(15)" not-null="true"/>
</property>
<property name="reg_date">
<column name="reg_date" sql-type="date" not-null="true"/>
</property>
<set name="news" table="news" inverse="true">
   <key column="author_id"/>
   <one-to-many class="News"/>
</set>
</class>
</hibernate-mapping>

If it`s right then what query will return required result?

[Edvins Reisons]

Not going through the mappings; I would use a query like

from News n join n.users u

[Lisa DeSouza]

But is it not possible to use something equivalent to a left outer join if the entities don't contain each other as a set?

Edvins Reisons wrote:To use the join syntax, you need a relationship defined (I guess it is a many-to-one in this case) in the mappings. Without it, one can use a query like this:

from News as news, Users as users where news.author_id=users.id

[Lisa DeSouza]

But is it not possible to use something equivalent to a left outer join if the entities don't contain each other as a set?

This is my Category:

<class name="com.webaccess.onlinetest.model.beans.Category" table="ot_category_master">
        <id name="id" column="ID">
            <generator class="increment"/>
        </id>
        
        <property name="categoryName">
            <column name="name"/>
        </property>
        
        <property name="parentCatId">
            <column name="parent_cat_id"/>
        </property>
        
        <property name="createdBy">
            <column name="created_by"/>
        </property>
        
        <property name="createdDate">
            <column name="created_date"/>
        </property>
        
        <property name="modifiedBy">
            <column name="modified_by"/>
        </property>
        
        <property name="modifiedDate">
            <column name="modified_date"/>
        </property>

        <property name="organizationId">
            <column name="organization_id"/>
        </property>
    </class>

and this is my Organization:

<class name="com.webaccess.onlinetest.model.beans.Organization" table="ot_organization_details">
        <id name="id" column="ID">
            <generator class="increment"/>
        </id>
        <property name="name">
            <column name="name"/>
        </property>
   </class>

Similar to the linking in Category I have linking for 2-3 other entities.
I actually needed to retrieve them using an HQL query equivalent to this in MYSQL:

select count(category1_.ID) as col_0_0_, count(examinee2_.ID) as col_1_0_, count(logininfo3_.ID) as col_2_0_, count(questions4_.ID) as col_3_0_, count(test5_.ID) as col_4_0_ 
from ot_organization_details organizati0_
left outer join ot_category_master category1_
on category1_.organization_id=organizati0_.ID 
left outer join ot_examinee_details examinee2_
on examinee2_.organization_id=organizati0_.ID 
left outer join ot_login_details logininfo3_
on logininfo3_.organization_id=organizati0_.ID 
left outer join ot_question_bank questions4_
on questions4_.organization_id=organizati0_.ID 
left outer join ot_test_master test5_ 
on test5_.organization_id=organizati0_.ID
where organizati0_.ID=3 

The code you posted is equivalent to a basic join, right? and doesn't retrieve the data I need...

Edvins Reisons wrote:To use the join syntax, you need a relationship defined (I guess it is a many-to-one in this case) in the mappings. Without it, one can use a query like this:

from News as news, Users as users where news.author_id=users.id

[Rahul Babbar]

Lisa DeSouza wrote:But is it not possible to use something equivalent to a left outer join if the entities don't contain each other as a set?

Well...there is no 'straightforward' way in hibernate to do a "Left Outer Join" when your entities do not have a relationship...

but there are hacks...

Like the following set of SQL demonstrate..

 
create table A1(id number primary key);
create table B1(id number  );
alter table B1 add constraint B1_FK1 foreign key (id) references A1(id);

insert into A1 values(1);
insert into A1 values(2);
insert into A1 values(3);

insert into B1 values(1);
insert into B1 values(2);

Select a.id, b.id from A1 a left outer join B1 b on a.id = b.id ; // SQL with Join , gives three rows

Select a.id, (select id from B1  where id = a.id) from A1 a ; // SQL without join, gives same result

So, if it is hibernate, the above query will work....so you can get the desired result for a join even without using join .. :-)
But such a thing is not recommended, because of the performance impact that it may have if the number of joins on the table are more...and the query becomes complex...etc

[Lisa DeSouza]

That's what I finally ended up doing.... Thanks!

Rahul Babbar wrote:

Lisa DeSouza wrote:But is it not possible to use something equivalent to a left outer join if the entities don't contain each other as a set?

Well...there is no 'straightforward' way in hibernate to do a "Left Outer Join" when your entities do not have a relationship...

but there are hacks...

Like the following set of SQL demonstrate..

 
create table A1(id number primary key);
create table B1(id number  );
alter table B1 add constraint B1_FK1 foreign key (id) references A1(id);

insert into A1 values(1);
insert into A1 values(2);
insert into A1 values(3);

insert into B1 values(1);
insert into B1 values(2);

Select a.id, b.id from A1 a left outer join B1 b on a.id = b.id ; // SQL with Join , gives three rows

Select a.id, (select id from B1  where id = a.id) from A1 a ; // SQL without join, gives same result

So, if it is hibernate, the above query will work....so you can get the desired result for a join even without using join .. :-)
But such a thing is not recommended, because of the performance impact that it may have if the number of joins on the table are more...and the query becomes complex...etc