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

Hibernate version:
3.1.0 beta 4

Full stack trace of any exception that occurs:
ERROR [PARSER] line 1:70: unexpected token: on

**************************************

Hi,

I'd like to know if there is a way to get objects using "join" with the same class. What I mean is: I want to get all the objects that don't have a corresponding object joined to it.

I need to know if there is an item in a list that is not in another list. I need to copy items form a list to another and I need to check if there is as inconsistency between the lists after some changes my user can do.

The SQL I'd use is:

select * from myTable t
left join mytable t2 on t2.myField = t.myField and t2.myGroup = :originalGroup
where
t.myGroup = :newGroup and t2.Id is null

thanks in advance.

Hibernate recognises HQL not SQL.


I would do this in HQL:


And in regards to your second question. The SQL stament is weird:
- t2.Id is null? how does a table has a row that does not have an id? should this write t2.FK is null?

Anyway, I would do this instead:

I can't help much without understanding the table structure more - what is the PK, and what columns for the FK for the join?

But anyways, if you can come up with the SQL SELECT that satisfies your requirement, Hibernate CAN do a query based on SQL. Just look at the examples for createSQLQuery in the Hibernate doc.

Hi,

Is there a way to use my own join condition in replacemant of the conditions used by hibernate. For example, when I use the HQL "from tableA left join tableB where tableA.id = 10", the resulting SQL generated by hibernate is "select field1, field2 from tableA left join tableB ON A.PK = B.FK where tableA.id = 10", but I don't want to use this ON clause, but my own ON clause to join without using the correct PK and FK in a specific situation in my software.

You could do something like:

SQLQuery query = session.createSQLQuery("select {p.*},{ph.*} from person p left outer join phone ph on ph.person_id=p.id and ph.active=1 where p.id=3");
query.addEntity("p", Person.class);
query.addEntity("ph", Phone.class);
List res = query.list();

The problem here is that the list res will not be the expected size. Say the above query should give 1 person with 2 phones. The res list will contain 2 entries, each being an Object[2]. res[0][0] and res[1][0] will both contain the same person, and res[0][1] and res[1][1] will contain the first and 2nd phone number.

A criteria-type query (which of course you can't use for custom joins) has support for result transformations that would help, but this hasn't been added yet for other query types, as far as I know.

But why not just use a regular join and just add extra stuff in the where clause?