You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Matt Higgins <ma...@gmail.com> on 2009/05/11 17:59:10 UTC
Need to map a strange table structure
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
Re: Need to map a strange table structure
Posted by Matt Higgins <ma...@gmail.com>.
This query works great and we use it in a few places in our PHP as well when
we need everything. I guess I am not seeing through to your solution. How
would I use a OneTooManyCopmanyVenue entity to get these results? In an
ideal world I would have
@Enity
@Table(name="company")
public class Company {
List<Venue> venues;
@OneToMany(targetEntity = VenueImpl.class)
public List<Venue> getVenues() {
return Venues;
}
public void setVenues(List<Venue> venues) {
this. venues = venues;
}
}
@Entity
@Table(name="company")
public class Venue{
Company parent
@ManyToOne(targetEntity = CompanyImpl.class)
public Company getCompany(){
On Tue, May 12, 2009 at 3:26 AM, Jari Fredriksson <ja...@iki.fi> wrote:
> > 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.
>
>
>
>
Re: Need to map a strange table structure
Posted by Jari Fredriksson <ja...@iki.fi>.
> 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.