Actually it works for me in a similar example I butchered quickly from something I had. Here we have Nodes optionally pointing to Categories which are just other Nodes. So its very similar to your scenario. Here is my entity definition. Its all JPA annotations:
Code:
package au.com.lawlex.taipan.app.core.node;
import java.util.Collection;
import java.util.Collections;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;
@Entity
@Table(name = "node_tree_new")
public class NodeLightNew
implements java.io.Serializable
{
private long nodeId;
private Long categoryId;
private NodeLightNew category;
private Collection<NodeLightNew> subCategories = Collections.emptyList();
private String name;
public NodeLightNew()
{
}
@Column(name = "node_id", unique = true, nullable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Id
public long getNodeId()
{
return nodeId;
}
public void setNodeId(final long nodeId)
{
this.nodeId = nodeId;
}
@Column(name = "category_id", insertable = false, updatable = false)
public Long getCategoryId()
{
return this.categoryId;
}
public void setCategoryId(final Long categoryId)
{
this.categoryId = categoryId;
}
@ManyToOne(optional = true)
@JoinColumn(name = "category_id", nullable = true)
public NodeLightNew getCategory()
{
return this.category;
}
public void setCategory(final NodeLightNew category)
{
this.category = category;
}
@OneToMany(targetEntity=au.com.lawlex.taipan.app.core.node.NodeLightNew.class, mappedBy = "category")
public Collection<NodeLightNew> getSubCategories()
{
return this.subCategories;
}
public void setSubCategories(final Collection<NodeLightNew> subCategories)
{
this.subCategories = subCategories;
}
@Column(name = "name", nullable = false)
public String getName()
{
return this.name;
}
public void setName(final String name)
{
this.name = name;
}
} // end class NodeLight
And here is the working query:
Code:
select nl1.name, (select nl2.name from NodeLightNew nl2 where nl1.categoryId = nl2.nodeId) from NodeLightNew nl1
And actually this query appears to be working fine as well which is very much like your query:
Code:
select nl1.nodeId, nl1.name, nl2.name from NodeLightNew nl1 left outer join nl1.category nl2
So my feeling now is that may be you haven't got your mappings quite right?