Hibernate version: 1.2
Full stack trace of any exception that occurs:outer or full join must be followed by path expression [...query (see below)...]
Name and version of the database you are using: MSSQL Server 2005
The generated SQL (show_sql=true): none
Hi there!
I am using NHibernate together with Spring.NET, using the HibernateTemplate, Spring.NET supplies me with.
Everything always worked fine for me, besides the typical problems one encounters when using these two for the first time...
Well, now I have come to the point where I just can't help me myself and have to ask.
I am trying to do quite a huge query including joins over 7 tables...
The problem is, that I have this linear structure of seven tables and want to get some data from table number 7 depending on a value found in table number 1.
As I am not allowed to post table names and stuff here, I will name them table_1 to table_7 to obfuscate names, hope that's ok!?
Alright, first the tables:
table_1 has a value I get from somewhere (an id).
table_2 has a FK pointing to this id, as well as a FK pointing to table_3.
In table_3 there's nothing interesting by now.
table_4 contains a FK pointing to table_3, as well as a FK pointing to table_5.
table_6 has a FK pointing to table_5, as well as a FK pointing to table_7 where I want to get the data from.
In the query below, something like "table_6.five_id.id" means: In Table 6, there is a foreign key pointing to table 5.
In objects, this would be something like: A person has a phone number which has an id (where person would be table_6, five_id the FK to phone_number and id the number's id).
So this is my HQL (hope so) query:
Code:
from Table_1 table_1, Table_7 table_7, Table_5 table_5
inner join Table_6 as table_6 on table_5.Id = table_6.five_id.Id
inner join Table_4 as table_4 on table_5.Id = table_4.five_id.Id
inner join table_7 on table_6.seven_id.Id = table_7.id
inner join Table_3 as table_3 on table_4.three_id.Id = table_3.Id
inner join Table_2 as table_2 on table_3.Id = table_2.three_id.Id
inner join table_1 on table_2.one_id.Id = table_1.Id
where table_1.Id = :OneIDWhichIsPassedByMe
which I derived from MSSQL Server's generated query:
Code:
SELECT
dbo.table_1.interesting_value, dbo.table_7.interesting_value
FROM
dbo.table_5
INNER JOIN
dbo.table_6 ON dbo.table_5.id = dbo.table_6.five_id
INNER JOIN
dbo.table_4 ON dbo.table_5.id = dbo.table_4.five_id
INNER JOIN
dbo.table_7 ON dbo.table_6.seven_id = dbo.table_7.id
INNER JOIN
dbo.table_3 ON dbo.table_4.three_id = dbo.table_3.id
INNER JOIN
dbo.table_2 ON dbo.table_3.id = dbo.table_2.three_id
INNER JOIN
dbo.table_1 ON dbo.table_2.one_id = dbo.table_1.id
WHERE
dbo.table_1.id = @myRequestedId
This actually is the first time I am working with HQL and JOINs, so please be patient if the above stuff is simply stupid...or something...
The problem: I am getting the exception I mentioned above. I do not get any generated SQL.
I tried to get all the tables manually (from NHibernate/Spring --> HibernateTemplate.LoadAll<MyType>()) which worked well...
What am I doing wrong in here?
There does not have to be a direct connection (via FKs e.g.) between Table 1 and Table 7, right?
Any suggestions?
Greets - Der Steps
Login
FAQ
Search
