Hey guys,

I have an optional FK association that I mapped as follows:

@OneToOne(optional=true,fetch=FetchType.LAZY,cascade=CascadeType.ALL)
@JoinColumn(name = "OP_STS_ID", referencedColumnName="CTGY_ID",nullable=true,insertable = false, updatable = false)
@NotFound(action=NotFoundAction.IGNORE)
private Category operatingStatus;


The idea is that not every value in the column OP_STS_ID will have a valid FK to the category table; that's what makes the relationship optional.

However, this is not working at all. When querying using HQL Hibernate is using an inner join and I need it to do an outer join to grab all the values.... So right now it issues "and event0_.OP_STS_ID=category5_.CTGY_ID " and I NEED it to issue "and event0_.OP_STS_ID=category5_.CTGY_ID (+)"

The (+) should indicate that this association is optional, but I can't get it to work!!

This is the HQL:

Query q = s
.createQuery("select distinct e from Event e join e.facilities "
+ "assc join e.scheduleList sch join sch.dailyViewList dv "
+ "where assc.facility.id=80007838 and (e.operatingStatus.englishName is null" +
" or e.operatingStatus.englishName <> 'Closed')"
+ "and dv.scheduleDate>? and dv.scheduleDate<?");

Please help!


Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:3.3.1GA

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:Oracle 10g

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html

You are currently using an implicit join to the 'operatingStatus' (e.operatingStatus.englishName ....). Hibernate never generates outer joins in this case. You need to do an explicit 'left join'. Eg.