Actually the solution was @Formula Hibernate fields.
For example:
@XmlTransient
@Formula("(select sum(invoices.amountdue) from invoices where
invoices.event_id = event_id and invoices.participant_id = participant_id)")
private Double amountInvoiced;
PostgreSQL is able to optimize it pretty well, when you have necessary
indexes (the whole query with sub-selects takes about 10-14 ms).
When I'm saying about the solution that is 'far from elegant', I mean the
size of Criteria creation code (about 30 lines) and its lack of reusability
(because every component have slightly different conditions of using it).
But overall performance is very, very fast as soon as you eliminate "N+1"
Hibernate problem.
On Tue, Jan 6, 2015 at 5:30 PM, Jonathan Barker <jonathan.theit...@gmail.com
> wrote:
> Ilya,
>
> I haven't faced that situation… yet. I guess the complication is in
> specifying the grouping conditions.
>
> My stats listings are restricted in size, so I don't worry about a
> paged grid source for those, and I get the results from stored
> procedures. Some of the *counts* in that list are large. I need
> someone to be able to click on 86000 and go to a listing that doesn't
> bring down the application.
>
> Let me know if you find an adequate solution. I see your other post
> about code that looks far from elegant - if that is it, I would be
> curious to know how you handled the projections.
>
> Regards,
> Jonathan
>
>
>
> On Sat, Jan 3, 2015 at 3:21 AM, Ilya Obshadko <ilya.obsha...@gmail.com>
> wrote:
> > Johnathan,
> >
> > How do you handle situations when several of the properties you need to
> > display are aggregates?
> > For example: select ..., sum(amount_invoiced), sum(amount_paid) from
> orders
> > group by order_id.
> >
> > This won't work nice with Projections.rowCount() (unless you use
> > sub-select, which might be sub-optimal, and I'm not sure if even possible
> > using Criteria API).
> >
> >
> > On Sat, Jan 3, 2015 at 5:57 AM, Jonathan Barker <
> jonathan.theit...@gmail.com
> >> wrote:
> >
> >> Ilya,
> >>
> >> Our strategy is to use a class specifically to hold the query results,
> >> so the sort criteria are simple properties. The Criteria query and
> >> Projection are supplied separately to our GridDataSource. The
> >> Projection maps any child properties onto aliases (useful for the
> >> sorting), and an optional ResultTransformer as well. If none is
> >> provided, the Transformers.aliasToBean is used. The count is achieved
> >> by using the Projections.rowCount().
> >>
> >> On Fri, Jan 2, 2015 at 3:45 AM, Ilya Obshadko <ilya.obsha...@gmail.com>
> >> wrote:
> >> > I'm trying to implement a Grid data source optimized for database
> >> > retrievals (with paging support). Basically it's derived from
> >> > HibernateGridDataSource from Tapestry distribution, and provides
> support
> >> > for complex Criteria queries:
> >> > https://gist.github.com/xfyre/ecb36a9173aed6a37f14
> >> >
> >> > However, there is a problem with sorting.
> >> >
> >> > Provided BeanModel implementation doesn't support nested properties;
> that
> >> > is, I cannot use properties of child Hibernate entities: <t:grid
> >> > include="property1, child1.property1, child2.property2" .../> results
> in
> >> > error. Initially I had a workaround of having helper getter methods in
> >> > parent entity class, so it looked like <t:grid include="property1,
> >> > child1Property1, child2Property2" .../>. It worked, but when I'm
> trying
> >> to
> >> > use database-backed Grid data source, there's an error with sorting
> >> > (because sorting is performed on database level and obviously root
> entity
> >> > of the query doesn't contain database properties specified in Grid.
> >> >
> >> > Is there any reasonable solution for that, besides manually providing
> all
> >> > required mappings between Grid property names and database property
> >> names?
> >> >
> >> > --
> >> > Ilya Obshadko
> >>
> >>
> >>
> >> --
> >> Jonathan Barker
> >> ITStrategic
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: users-unsubscr...@tapestry.apache.org
> >> For additional commands, e-mail: users-h...@tapestry.apache.org
> >>
> >>
> >
> >
> > --
> > Ilya Obshadko
>
>
>
> --
> Jonathan Barker
> ITStrategic
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscr...@tapestry.apache.org
> For additional commands, e-mail: users-h...@tapestry.apache.org
>
>
--
Ilya Obshadko
