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 <[email protected]> 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.
>
>
>
>
