0

Hi I have a case where I need to do this select statement

   SELECT c.*, count(r.competitorid) as num_comp, num_event.num_events
from competition c left join regcomp r on c.competitionid = r.competitionid 
left join 
(
select competition.competitionid, count(e.competitionid) as num_events
from competition left join `event` e on competition.competitionid = e.competitionid
group by competition.competitionid
) as num_event on c.competitionid = num_event.competitionid
                 )    
   AS winners ON winners.competitionid = c.competitionid;

My problem is that I do not know what pattern follow, or if there's a set of methods that I need to call to create the datasource for this table. I can create an IndexedContainer and add container properties, then add that to the Vaadin table, which is what I'm doing - but the problem is when I try to persist data, I am not able to use JPA later if I don't use it at the start.

JPA lets you access referenced tables via foreign keys very conveniently by doing setVisibleColumns("parent.child") and so it is possible in theory to show any information about a single row by picking the correct entry-entity so to speak.

But what do I do if I want to create a table that shows counts in one of the columns, obviously the count is not part of the entity - but if it isn't part of the entity how can I use the benefits of JPA on tables that include data generated by stuff like avg(), count() etc.

P.S. the query retrieves a table showing all the competitions and how many competitors and events are in that competition.

boris dzhelali
  • 197
  • 1
  • 10

2 Answers2

1

This depends on what JPA provider you use. When using Hibernate you can use calculated properties as mentioned in this post.

It then gives such annotations:

@Formula("PRICE*1.155")
private float finalPrice;

or even more complex

@Formula("(select min(o.creation_date) from Orders o where o.customer_id = id)")
private Date firstOrderDate;

Look at the other post for more details on this Hibernate feature.

For EclipseLink/Toplink I know of no solution to the problem

Community
  • 1
  • 1
André Schild
  • 4,592
  • 5
  • 28
  • 42
0

Persistence means you have strong link between your classes and database tables, which are "hard, durable" things. In your queston you are fetching data from a query, not a table, so talking about persistence in this context is not correct.

What you can do is to add in your Table a custom column where you make your custom things,

or make a database view and create an Entity on it (could autogenerate with JPATools) if you want to have the full power of JPAContainer.

Cheers.

MarcelloGarini
  • 599
  • 2
  • 13