Relationship: View from Table that references other than PK?

fdiaz · **Posted:** Fri May 05, 2006 8:55 pm

Can I have a relationship to a view from a table - but this relationship references a property and not the FK?

If so, can you please explain where and how I can go about making this possible in the mapping file?

For example:

1) Table #1 - Address (parent table in one-to-many with Employer)

ADDRESS_ID (primary key)
ADDRESS_LINE1
ADDRESS_LINE2

2) Table #2 - Employer (child table in one-to-many with Address)

EMPLOYER_ID (primary key)
ADDRESS_ID (reference column to ADDRESS table - NOT a foreign key)
EMPLOYER_NAME

3) View - ResultingEmployees (parent table in one-to-one with Employer and takes child's ADDRESS_ID as primary key/foreign key)

ADDRESS_ID (primary key/foreign key that references ADDRESS_ID in Employer table)
COLUMN1
COLUMN2

**When I try to run an inner join query on ADDRESS_ID, Hibernate tries to use the primary key but it assumes the ADDRESS_ID is the primary key of the Employer table.

So, the question is - can I create a one-to-one relationship between two tables where I use a column/property other than the primary key (on the table sending the FK) as the column the other table references a the foreign key?

I know this is a pretty obscure example but I couldnt find anything in both the manual or the book that points to how to do this?

Please let me know if you have any questions and I will expound.

Thanks in advance.
F Diaz

LaLiLuna · **Posted:** Sat May 06, 2006 6:48 pm

property-ref should help you -> Hibernate reference explains it.

Best Regards Sebastian

fdiaz · **Posted:** Mon May 08, 2006 7:43 pm

I have tried both "property-ref" and the "properties" tag but I wasnt able to get the inner join (below) to work either.

HQL

Code:

select emp.addressId 
from Employer as emp
inner join
emp.resultingEmployees
where
emp.addressId = 0001

Resulting SQL

Code:

select employer.address_id
from employer
inner join 
resulting_employees
on employer.employer_id = resulting_employees.address_id
where
employer.address_id = 0001

***It seems that hibernate keeps trying to do the inner join on the primary key for the employer table instead of 'address_id' (bold/italics above) as indicated in the property-ref/properties tag.

I could be doing something wrong but everything seemed to be compiling fine when I tried loading the modified files.

Thanks