Re: [eclipselink-users] Conditional LEFT JOIN in JPQL

[Frank Schwarz <fs5@xxxxxxx>]

The additional fields can also be mapped by a java.util.Map-structure, i.e.

public class Country {
...
  @ElementCollection
  @CollectionTable(name="ExtCharFields",
joinColumns=@JoinColumn(name="country_id"))
  @MapKeyColumn(name="fieldName")
  @Column(name="fieldValue")
  private Map<String, String> extChatFields;
...
}

In this case, no column "id" would be required for table ExtCharFields.

With this mapping, you would even have transparent "write access" to
ExtCharFields.

-- Frank


vide wrote:
> 
> Hi, Tom,
> [...]
>   Say application has the ability to dynamicaly extend table structure,
> i.e.
> virtualy describe additional columns in a table. For example we have a
> table:
> [Country]
> String name
> Integer id
> 
> Customer wants to extend this table to add some extra fields, say
> "language"
> and "nationality". Such extra data is stored in a table:
> [ExtCharFields]
> Integer country_id
> String fieldName
> String fieldValue
> Integer id
> 
> So, to present a table for viewing/editing, native SQL would look like
> this:
> SELECT c.name, e1.fieldValue AS language, e2.fieldValue AS nationality
>   FROM Country c
>   LEFT JOIN ExtCharFields e1 ON c.id=e1.country_id  AND e1.fieldName =
> "language"
>   LEFT JOIN ExtCharFields e2 ON c.id=e2.country_id  AND e2.fieldName =
> "nationality"
> 
> With this single SQL I get all I need and also can do any kind of sorting,
> paging, filtering results and etc., but I don't have any object/relational
> mapping, even can't use @SqlResultSetMapping, because query is dynamic. I
> could do this in JPQL, if it will support "ON" clause.
> 
> Hope I was clear enough to illustrate the problem ;-)
> 
> -- 
> Sincerely,
> Vidas
> 
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> 
> 

-- 
View this message in context: http://old.nabble.com/Conditional-LEFT-JOIN-in-JPQL-tp28432051p28484782.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.