I've been researching how to use outer joins with Criteria, specifically I have a one to one or zero relationship (specified as a many to one in the parent object), when querying the parent I want to filter the results based on values of the child (if it exists, if the child does not exist I want the option of including the parent). I also want to order by a value in the child, if it exists. After searching google and the forum I've seen a lot of conflicting info about outer/left joins and Criteria, several opended and closed issues and various patches that may or may not be in the version I am running (3.2). Various posts I have seen have mentioned aliases, fetchmodes, joinfragments, etc, but I haven't found any solid documentation. So I guess I am asking what is the current status of doing what I would like using Criterias and not HSQL? If it is possible, are there some simple examples someone could point me to so I can figure this out?

Hello,

I guess this won't help but it works:

Criteria c = session.createCriteria(Person.class);
c.createAlias("events","ev",Criteria.LEFT_JOIN);


c.add( Restrictions.disjunction()
.add(Restrictions.like("ev.title","%I%"))
.add(Restrictions.isEmpty("events")));
c.addOrder(Order.desc("ev.title"));

Returns 3 persons with one of them without event.
And the order clause on events.title works.

I can't seem to get that to work, all I get is "org.hibernate.QueryException: could not resolve property".

If you want some help, post your mapping and your java code, i.e. the part where you do the query. Your error message is a very generic one.

Ok, here's the two relevent mapping files, I use annotations but I regenerated these since they are shorter:

Forum object:

<hibernate-mapping>
<class name="dealcatcher.kolinka.database.dbo.DBOForum" table="Forum" schema="dbo" catalog="Kolinka_Index_Dev">
<id name="forumId" type="int">
<column name="Forum_ID" />
<generator class="assigned" />
</id>
<many-to-one name="forumProfile" class="dealcatcher.kolinka.database.dbo.DBOForumProfile" fetch="select">
<column name="last_profile_id" />
</many-to-one>
<many-to-one name="crawl" class="dealcatcher.kolinka.database.dbo.DBOCrawl" fetch="select">
<column name="last_crawl_id" />
</many-to-one>
<property name="forumName" type="string">
<column name="Forum_Name" not-null="true" />
</property>
<property name="forumUrl" type="string">
<column name="Forum_URL" not-null="true" />
</property>
<property name="siteUrl" type="string">
<column name="Site_URL" />
</property>
....
</class>
</hibernate-mapping>

Forum profile:

<hibernate-mapping>
<class name="dealcatcher.kolinka.database.dbo.ForumProfile" table="ForumProfile" schema="dbo" catalog="Kolinka_Index_Dev">
<id name="id" type="int">
<column name="ID" />
<generator class="assigned" />
</id>
<many-to-one name="datePatternByThreadPatternId" class="dealcatcher.kolinka.database.dbo.DBODatePattern" fetch="select">
<column name="Thread_Pattern_ID" />
</many-to-one>
<many-to-one name="datePatternByTopicPatternId" class="dealcatcher.kolinka.database.dbo.DBODatePattern" fetch="select">
<column name="Topic_Pattern_ID" />
</many-to-one>
<many-to-one name="parameterSeparator" class="dealcatcher.kolinka.database.dbo.DBOParameterSeparator" fetch="select">
<column name="Parameter_Separator_ID" />
</many-to-one>
<many-to-one name="forumSoftware" class="dealcatcher.kolinka.database.dbo.DBOForumSoftware" fetch="select">
<column name="Forum_Software_ID" />
</many-to-one>
<many-to-one name="forum" class="dealcatcher.kolinka.database.dbo.DBOForum" fetch="select">
<column name="forum_id" not-null="true" />
</many-to-one>
<property name="profileCode" type="java.lang.Integer">
<column name="Profile_Code" />
</property>
<property name="threadTypeId" type="java.lang.Integer">
<column name="Thread_Type_ID" />
</property>
<property name="topicListTypeId" type="java.lang.Integer">
<column name="Topic_List_Type_ID" />
</property>
<property name="robotsAllowCrawl" type="java.lang.Boolean">
<column name="robots_allow_crawl" />
</property>
<property name="robotsAllowPrintable" type="java.lang.Boolean">
<column name="robots_allow_printable" />
</property>
<property name="dateEntered" type="timestamp">
<column name="date_entered" length="23" not-null="true" />
</property>
<set name="forumProfileErrors" inverse="true">
<key>
<column name="Profile_ID" not-null="true" />
</key>
<one-to-many class="dealcatcher.kolinka.database.dbo.DBOForumProfileError" />
</set>
<set name="forumStatisticses" inverse="true">
<key>
<column name="profile_id" not-null="true" unique="true" />
</key>
<one-to-many class="dealcatcher.kolinka.database.dbo.DBOForumStatistics" />
</set>
<set name="forums" inverse="true">
<key>
<column name="last_profile_id" />
</key>
<one-to-many class="dealcatcher.kolinka.database.dbo.DBOForum" />
</set>
</class>
</hibernate-mapping>


My working HQL query:

Transaction transaction=session.beginTransaction();

StringBuilder queryStr = new StringBuilder();
queryStr.append("select f from DBOForum as f ");
queryStr.append(" left join f.lastProfile as lp ");
queryStr.append(" where f.availability = 0 ");
queryStr.append(" and lp is null or (lp.forumSoftware.id <>105 and lp.dateEntered < :dateEntered) ");

queryStr.append(" and f.enabled = 1 ");
queryStr.append(" order by lp.dateEntered, f.id ");

Query query = session.createQuery(queryStr.toString());
//log.debug("dateEntered: "+DateUtils.get24HoursAgo());
query.setParameter("dateEntered" , DateUtils.get24HoursAgo());
query.setMaxResults(1);
List<DBOForum> forumList= (List<DBOForum>)query.list();


My non-working Criteria query:

Transaction transaction=session.beginTransaction();
Criteria criteria=session.createCriteria(DBOForum.class);

criteria.createAlias("lastProfile", "lastpro", Criteria.LEFT_JOIN);
criteria.add(Restrictions.eq("availability", ForumAvailability.AVAILABLE.value()));
criteria.add(Restrictions.eq("enabled", Boolean.TRUE));
criteria.add(Restrictions.not(Restrictions.ilike("forumUrl", "%ezboard.com%")));

criteria.add(Restrictions.disjunction()
.add(Restrictions.isNull("lastpro"))
.add(Restrictions.ne("lastpro.forumSoftware.id", 105))
);


criteria.add(Restrictions.disjunction()
.add(Restrictions.isNull("lastpro"))
.add(Restrictions.lt("lastpro.dateEntered", DateUtils.get24HoursAgo()))
);

criteria.addOrder(Order.asc("lastpro.dateEntered"));
criteria.setMaxResults(1);
List<DBOForum> forumList= (List<DBOForum>)criteria.list();


Let me know if you need any other info. Thanks in adviance for any help.

When hibernate said:
org.hibernate.QueryException: could not resolve property

It mentionned a property in the Exception stack, that's the main point.
Please post the stacktrace from your log file.

Oops, I knew I'd forget something:

22 Aug 2006 14:56:02 FATAL: dealcatcher.utils.database.DatabaseException: org.hibernate.QueryException: could not resolve property: lastpro of: dealcatcher.kolinka.database.dbo.DBOForum
dealcatcher.utils.database.DatabaseException: org.hibernate.QueryException: could not resolve property: lastpro of: dealcatcher.kolinka.database.dbo.DBOForum
at dealcatcher.kolinka.database.hibernate.HibernateProfileDatabase.getProfileForum(HibernateProfileDatabase.java:247)
at dealcatcher.kolinka.database.hibernate.diag.HibernateProfileDatabaseTest.testGetProfileforums(HibernateProfileDatabaseTest.java:23)
at dealcatcher.kolinka.database.hibernate.diag.HibernateTest.main(HibernateTest.java:44)
Caused by: org.hibernate.QueryException: could not resolve property: lastpro of: dealcatcher.kolinka.database.dbo.DBOForum
at org.hibernate.persister.entity.AbstractPropertyMapping.throwPropertyException(AbstractPropertyMapping.java:43)
at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:37)
at org.hibernate.persister.entity.AbstractEntityPersister.getSubclassPropertyTableNumber(AbstractEntityPersister.java:1319)
at org.hibernate.persister.entity.BasicEntityPropertyMapping.toColumns(BasicEntityPropertyMapping.java:31)
at org.hibernate.persister.entity.AbstractEntityPersister.toColumns(AbstractEntityPersister.java:1294)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumns(CriteriaQueryTranslator.java:434)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumnsUsingProjection(CriteriaQueryTranslator.java:394)
at org.hibernate.criterion.NullExpression.toSqlString(NullExpression.java:26)
at org.hibernate.criterion.Junction.toSqlString(Junction.java:58)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:334)
at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:82)
at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:68)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1543)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at dealcatcher.kolinka.database.hibernate.HibernateProfileDatabase.getProfileForum(HibernateProfileDatabase.java:228)
... 2 more

There is at least one line in your code using lastpro which is wrong.
Advice:
comment each line with lastpro
run
remove 1 comment
run
and so on

until you find the wrong one
Looking at your code I can not see where it is wrong :-(

Ok, I have found two errors, one of which could be a bug. First testing for null must be specified with the original column name, not the alias, like:

criteria.add(Restrictions.disjunction()
.add(Restrictions.isNull("lastProfile"))
.add(Restrictions.lt("lastpro.dateEntered", DateUtils.get24HoursAgo()))
);

Perhaps this is the intended behavior but it is not intuitive.

Second, I can't figure out how to reference another table from the Profile, like below:

criteria.add(Restrictions.disjunction()
.add(Restrictions.isNull("lastProfile"))
.add(Restrictions.ne("lastpro.forumSoftware.id", 105))
);


I get :
org.hibernate.QueryException: could not resolve property: forumSoftware.id of: dealcatcher.kolinka.database.dbo.DBOProfile

I tried adding aliases, but Hibernate didn't like that, it was trying to associate the alias with a column in Forum, not ForumProfile. All I really need is the PK which is in the lastProfile table anyway, so I don't really need a join to a 3rd table. I could use a SQL restriction, but at this point I can't figure out what I would call any of the tables or columns.

you CAN do that:

crit.createAlias("banques", "bq");
crit.createAlias("banques.agence", "ag");


or if you prefer

That was one of the variations I tried earlier, however I get an error like this:

23 Aug 2006 14:02:31 FATAL: dealcatcher.utils.database.DatabaseException: org.hibernate.QueryException: not an association: forumSoftware
dealcatcher.utils.database.DatabaseException: org.hibernate.QueryException: not an association: forumSoftware
at dealcatcher.kolinka.database.hibernate.HibernateProfileDatabase.getProfileForum(HibernateProfileDatabase.java:254)
at dealcatcher.kolinka.database.hibernate.diag.HibernateProfileDatabaseTest.testGetProfileforums(HibernateProfileDatabaseTest.java:23)
at dealcatcher.kolinka.database.hibernate.diag.HibernateTest.main(HibernateTest.java:44)
Caused by: org.hibernate.QueryException: not an association: forumSoftware
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getPathEntityName(CriteriaQueryTranslator.java:216)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.createCriteriaEntityNameMap(CriteriaQueryTranslator.java:191)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.<init>(CriteriaQueryTranslator.java:81)
at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:59)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1543)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at dealcatcher.kolinka.database.hibernate.HibernateProfileDatabase.getProfileForum(HibernateProfileDatabase.java:235)
... 2 more


I have also tried
criteria.createAlias("lastProfile.forumSoftware", "lastproForum");
But I received the same error.

Since lastProfile never appear in your mapping,
the only thing we can say from your current mapping is that what follows will work:

Criteria criteria=session.createCriteria(DBOForum.class);
criteria.createAlias("forumProfile", "forumprof");
criteria.createAlias("forumprof.forumSoftware", "forumprofsoft");
....
.add(Restrictions.ne("forumprofsoft.id", 105))

I am not sure I follow, though I changed the names of the aliases everything else is the same. Here is the full code from my query:


criteria.createAlias("lastProfile", "lastpro", Criteria.LEFT_JOIN);
criteria.createAlias("lastpro.forumSoftware", "lastproForum");

criteria.add(Restrictions.eq("availability", ForumAvailability.AVAILABLE.value()));
criteria.add(Restrictions.eq("enabled", Boolean.TRUE));


criteria.add(Restrictions.disjunction()
.add(Restrictions.isNull("lastProfile"))
.add(Restrictions.ne("lastproForum.id", 105))
);

criteria.add(Restrictions.disjunction()
.add(Restrictions.isNull("lastProfile"))
.add(Restrictions.lt("lastpro.dateEntered", DateUtils.get24HoursAgo()))
);


criteria.addOrder(Order.asc("lastpro.dateEntered"));

criteria.setMaxResults(1);
List<DBOForum> forumList= (List<DBOForum>)criteria.list();

The error is from creating the dotted alias itself, not from the restriction Restrictions.ne("lastproForum.id", 105), I can comment that out and still get this error:

org.hibernate.QueryException: not an association: forumSoftware

Actually it is:
criteria.createAlias("lastProfile", "lastpro", Criteria.LEFT_JOIN);
criteria.createAlias("lastProfile.forumSoftware", "lastproForum");




If this code compile, the aliases should work as well.

Ok, I am an idiot, the problem was I wasn't actually linking to the forumsoftware table in my mapping, I was just using the id as an int field since that was all I ever needed. However, thank you for the help getting the link from forum -> forumprofile working, that had me stumped.