> I am building out an object model and services for an
> existing relational model and the table structure is a
> little strange and I am having trouble with the object
> mapping.
> Here are the details
>
> We have 2 tables
> company with primary key
> id
>
> 12M_company_venue with columns
>
> parent_company_id
> venue_company_id
>
> The relational model works like this, a row in the
> company table can represent both a company and a venue
> given the keys to the (one to many) 12M_company_venue
> join table you can get
> the venues of the company or just the company in cases
> were the parent_company_id = venue_company_id.
>
> here is an example to get all the companies
>
> SELECT company.id, company.name as name
> FROM 12M_company_venue
> INNER JOIN company ON
> 12M_company_venue.venue_company_id = company.id
> WHERE company.id =
> 12M_company_venue.parent_company_id
>
> Here is an example on how to get all the venues
>
> SELECT company.id as id, company.name as venue_name
> FROM 12M_company_venue
> INNER JOIN company ON
> 12M_company_venue.venue_company_id = company.id
> WHERE company.id !=
> 12M_company_venue.parent_company_id
>
> I know this is a little nasty but its what I have and
> another app (PHP) is working with this dataset and we
> just can't update it right now. Is it possible to map
> this with JPA ? Any help would be appreciated.
>
> -Matt
How about rethinking it as
SELECT parent.id as parent_id,
parent.name as parent_name,
venue.id as venue_id,
venue.name as venue_name
FROM 12M_company_venue
INNER JOIN company as parent ON 12_company_parent_id = parent.id
INNER JOIN company as venue ON 12_company_venue_id = venue.id ;
However, that SQL is of course not needed in entity, just add parentCompany and
venueCompany into your 12MCompanyVenue entity bean.
