How to select optional many-to-one

hlfsousa · **Posted:** Thu Jun 22, 2006 8:07 am

I'm using Hibernate 3.2.0CR1 (distributed with JBoss 4.0.4 GA). I have an entity that looks like this (just mentioning the meaningful attributes):

Code:

@Entity
public class SomeEntity {
    @Id
    private Integer id;

    @ManyToOne(optional = true)
    @JoinColumn(name = "OTHER", nullable = true)
    private OtherEntity other;

    // other properties, getters and setters
}

@Entity
public class OtherEntity { ... }

Note that I can leave SomeEntity.other null. Now, when I query "select e from SomeEntity e" everything works fine. At a certain point I just want to get the "id" and "other" properties of a specific SomeEntity. The obvious way to do it would be to query "select e.id, e.other from SomeEntity e where e.id=:id". However, if "other" is null, the query returns no result at all.
Is there any way to get this query to return an array containing the id on the first position and null on the second?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Does using an outer join help? "select e.id, e.other from SomeEntity e left outer join e.other where e.id=:id"

hlfsousa · **Posted:** Fri Jun 23, 2006 8:26 am

Thank you, I though outer joins only worked for collections. Though I still believe it should be rated as a workaroundable bug.