jdoql queries with 'group by' clause

[Stephen Cameron <st...@gmail.com>]

Hi,

I want to use such a query and I notice that in Estatio such queries are
implemented as a views, I wonder why it is done this way?

It does seem to be a way to avoid a table having to be created to avoid an
Isis validation error.

The only issue I have with this is that its going back to the SQL level.

I have the following query presently:

    public List<ParticipantDateLastAttendedResult>
ParticipantsByMonthsInactiveReport(@Parameter(optionality =
Optionality.OPTIONAL) RecurringActivity activity) {
        Query query = isisJdoSupport.getJdoPersistenceManager().newQuery(
                "SELECT this.participant, max(this.startDateTime) " + "
INTO au.com.scds.chats.dom.module.reports.ParticipantDateLastAttendedResult
"
                        + " FROM
au.com.scds.chats.dom.module.attendance.Attended " + " GROUP BY
this.participant ");
        List<ParticipantDateLastAttendedResult> results =
(List<ParticipantDateLastAttendedResult>) query.execute();
        return results;
    }

Re: jdoql queries with 'group by' clause

[Dan Haywood <da...@haywood-associates.co.uk>]

On 28 September 2015 at 23:39, Stephen Cameron <st...@gmail.com>
wrote:

> Hi,
>
> I want to use such a query and I notice that in Estatio such queries are
> implemented as a views, I wonder why it is done this way?
>

Performance... it's always going to be faster to do a join in the database
than within Java code.

However, our philosophy is "make it right, then make it fast".  So we would
only add views and rely on other DBMS features once we're clear what a
"naive" design would be like.

I think of using a view as a form of denormalization... however, it's not
data that's being repeated by logic.  So, "logic denormalization", I
suppose.

HTH
Dan



>
> It does seem to be a way to avoid a table having to be created to avoid an
> Isis validation error.
>
> The only issue I have with this is that its going back to the SQL level.
>
> I have the following query presently:
>
>     public List<ParticipantDateLastAttendedResult>
> ParticipantsByMonthsInactiveReport(@Parameter(optionality =
> Optionality.OPTIONAL) RecurringActivity activity) {
>         Query query = isisJdoSupport.getJdoPersistenceManager().newQuery(
>                 "SELECT this.participant, max(this.startDateTime) " + "
> INTO au.com.scds.chats.dom.module.reports.ParticipantDateLastAttendedResult
> "
>                         + " FROM
> au.com.scds.chats.dom.module.attendance.Attended " + " GROUP BY
> this.participant ");
>         List<ParticipantDateLastAttendedResult> results =
> (List<ParticipantDateLastAttendedResult>) query.execute();
>         return results;
>     }
>