I'm am trying to do a left outer join using the criteria api and alias', is this possible?

for example in hql:
select x
from X as x
left outer join x.column as y

Obviously this query would return all results in table X regardless of whether it had a matching value in the join table for x.column.

My problem is, can this be reproduced using the criteria api, I am really hoping to use it in this way since I already have a large filtering/sorting api which builds up the criteria for queries and dont want to abort this just for the odd outer join query.

Thanks

Criteria API can use outer joins for purposes of eager fetching, but doesn't support outer joins for purposes of restrictions (when using alias in 'where' part). The only workaround - using subselects.

Am I misunderstanding the eager fetching term? Should an eager fetch include rows which dont a have a value in the join table?
for example:
Table1 Table2
col1 col2 (FK) col1 col2
1 a a x
2 b b y
3 c

hql query:
SELECT tbl1
FROM Table1 as tbl1
LEFT OUTER JOIN tbl1.col2 as tbl2

Gives: col1 col2 joinedcol
1 a x
2 b y
3 c

When I try to create this using the Criteria API I loose the 3rd row.
eg: createCriteria(Table1.class).createCriteria("col2").setFetchMode("col2",FetchMode.JOIN).

I accept that if i place any restrictions on the query it should not be there, but otherwise i need t dsiplay it, even if it is null/empty.

Would you mind showing an example of this query using the Criteria API, if above is wrong?

Thanks in advance!

Show your mapping. In order to use outer join, your property should be nullable.

<many-to-one name="relation" class="Relation" not-null="false" optimistic-lock="true" update="true" unique="false" insert="true" lazy="false" embed-xml="false" outer-join="true" fetch="join">
<column name="R_FK" length="16" not-null="false"/>
</many-to-one>

Your examples is very fragmentary and not correlated together. It is difficult to understand what exactly is happens in your code. Sorry, I'm not telepathy capably yet. :-)

For example, how your mapping related to your example of the query?

createCriteria(Table1.class).createCriteria("col2").setFetchMode("col2",FetchMode.JOIN)

One possible mistake with this query - you create nested context "col2" and set fetch mode within this context. As result you try to set fetchmode to "col2.col2" property. Is this is your intention?

Appologies for bad code example. The table examples above dont render more than 1 space between words so everything has been displayed wrong, ie squashed to the left!

Foreign key in Table1 would be:
<many-to-one name="col2" class="Table2" not-null="false" optimistic-lock="true" update="true" unique="false" insert="true" lazy="false" embed-xml="false" outer-join="true" fetch="join">
<column name="Tabl2_FK" length="16" not-null="false"/>
</many-to-one>

Primary key in Table2 would be:
<id name="id" type="java.lang.Long">
<column name="Table2_PK" not-null="true"/>
<generator class="sequence"/>
</id>

If i do:
session.createCriteria(Table1.class).list()
I would get all results, ie 3 rows from table1

The minute i specify any kind of join using the Criteria API, for example:
if i do:
session.createCriteria(Table1.class).createCriteria("col2").setFetchMode("col2",FetchMode.JOIN)
OR session.createCriteria(Table1.class).createAlias("col2").setFetchMode("col2",FetchMode.JOIN)
I loose the row in Table1 where col1=3 because c is not in Table2, like an inner join.

Im looking for an example to reproduce my original hql query above just for this simple setup then ill worry about applying it to my real situation.

You don't need to create alias to set fetch mode. By default it should use settings from mapping, but you can override them with setFetchMode:



I suppose, that creating alias is triggered using inner join.

Yes, using createAlias forces an inner join. A left outer join is not an option.

If you agree that this issue should be fixed (or feature should be added) vote yes here:

http://opensource2.atlassian.com/projec ... e/HHH-1162

Thanks!