You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Mark Stobbe <ma...@gmail.com> on 2014/11/01 15:44:58 UTC
Re: Updating totals in a multi-user environment
Thank you Andrus!
I managed to get most of it into the existing framework. One quick
question, is it possible to use aliases for fields in the EJBQL query, e.g.:
*SELECT *o, *COUNT*(p) *AS *numberOfPackages
*FROM *Order o *JOIN *o.packages p
*WHERE *...
*GROUP BY *s
This would help greatly when getting the metadata of the query, because
above gives an exception and if I leave it out it just gives generated
names:
*QueryMetadata *metaData = query.getMetaData(oc.getEntityResolver());
*List*<*Object*> mapping = metaData.getResultSetMapping();
*for *(*int *i = 1; i < mapping.size(); i++)
{
*ScalarResultSegment *segment = (*ScalarResultSegment*) mapping.get(i);
System.out.println(segment.getColumn());
}
Mark
On Fri, Oct 31, 2014 at 9:50 AM, Andrus Adamchik <an...@objectstyle.org>
wrote:
> > My problem with the solution is just the integration into Cayenne.
>
> EJBQLQuery supports aggregates:
>
> http://cayenne.apache.org/docs/3.1/cayenne-guide/queries.html#ejbqlquery
>
> The problem with it is that the query itself can only be created from
> String using a JPQL-like object query language, so you forfeit the type
> safety of SelectQuery. Still it is a much better abstraction then
> SQLTemplate for instance. (And its integration into SelectQuery is on the
> agenda).
>
> Andrus
>
>
> > On Oct 31, 2014, at 1:38 AM, Mark Stobbe <ma...@gmail.com> wrote:
> >
> > @Andrus, I see. Good to know how to do this in Cayenne.
> >
> > @Michael, You are right. It makes more sense to let the database do the
> calculations. It prevents the race conditions when updating and ensures
> totals to be correct.
> > My problem with the solution is just the integration into Cayenne.
> > Do you use some extra classes to keep the totals? It would be perfect if
> the expression language could be extended to have an easy way to group and
> count associated entities!!
> >
> >
> >> On 30 okt. 2014, at 18:22, Michael Gentry <mg...@masslight.net>
> wrote:
> >>
> >> The utilities I mentioned also do SUM, AVG, MIN, and MAX ...
> >>
> >>
> >>> On Thu, Oct 30, 2014 at 12:35 PM, Mark Stobbe <ma...@gmail.com>
> wrote:
> >>> Oh, I also have the same problem with total cost with different
> >>> currencies...
> >>>
> >>>> On Thu, Oct 30, 2014 at 5:09 PM, Mark Stobbe <ma...@gmail.com>
> wrote:
> >>>>
> >>>> Hi Michael,
> >>>>
> >>>> I would like to display the count in a table for a whole bunch of
> orders.
> >>>> In theory I could use a "group by"-query to get the numbers I need and
> >>>> with proper configured indices this should be fairly quick, I guess.
> >>>>
> >>>> Is there a more transparent way of doing things, e.g. using lifecycle
> >>>> listeners, datachannel filters and such?
> >>>>
> >>>> Mark
> >>>>
> >>>> On Thu, Oct 30, 2014 at 4:15 PM, Michael Gentry <
> mgentry@masslight.net>
> >>>> wrote:
> >>>>
> >>>>> Hi Mark,
> >>>>>
> >>>>> Is there a performance reason why you don't just do a count on the
> >>>>> packages that match the order?
> >>>>>
> >>>>> mrg
> >>>>>
> >>>>>
> >>>>> On Thu, Oct 30, 2014 at 11:00 AM, Mark Stobbe <
> markstobbe85@gmail.com>
> >>>>> wrote:
> >>>>>> Hi all,
> >>>>>>
> >>>>>> I was wondering what is the best way to update totals in a
> multi-user
> >>>>>> environment. For example, let's say we have an Order which can have
> one
> >>>>> or
> >>>>>> more Packages associated and we want to maintain a total package
> count
> >>>>> on
> >>>>>> the Order entity. How would you update this value when the user has
> the
> >>>>>> option to add/remove packages.
> >>>>>>
> >>>>>> So the entities looks like:
> >>>>>>
> >>>>>> *Order*
> >>>>>> --------
> >>>>>> id : bigint
> >>>>>> orderNumber : varchar
> >>>>>> nrOfPackages : int
> >>>>>>
> >>>>>> *Package*
> >>>>>> ------------
> >>>>>> id : bigint
> >>>>>> packageNumber : varchar
> >>>>>> *fk_order : bigint*
> >>>>>>
> >>>>>> What do you guys use to solve this?
> >>>>>> Mark
> >>>>
> >>>>
> >
>
>
Re: Updating totals in a multi-user environment
Posted by Andrus Adamchik <an...@objectstyle.org>.
If you have a relationship over order.orderNumber -> issue.referenceNumber, then you can use this relationship to reference this join. But otherwise you can create something called "theta joins" in JPA spec:
SELECT ... FROM Issue i, Order o WHERE i.referenceNumber = o.orderNumber
Andrus
> On Nov 2, 2014, at 6:51 PM, Mark Stobbe <ma...@gmail.com> wrote:
>
> Actually, I solved it already in another way, but now I want to use joins
> in EJBQL which depend on a discriminator column, e.g.
>
> *Table "ISSUE"*
> issueNumber : varchar
> referenceNumber : varchar
> referenceType : varchar
>
> *Table "ORDER"*
> orderNumber : varchar
>
> Orders can have multiple issues by using its *orderNumber* as a
> *referenceNumber* and *referenceType = "ORDER"* in the ISSUE table.
> I don't think this is possible at the moment with EJBQL, right?
>
> Mark
>
>
> On Sat, Nov 1, 2014 at 3:44 PM, Mark Stobbe <ma...@gmail.com> wrote:
>
>> Thank you Andrus!
>> I managed to get most of it into the existing framework. One quick
>> question, is it possible to use aliases for fields in the EJBQL query, e.g.:
>>
>> *SELECT *o, *COUNT*(p) *AS *numberOfPackages
>> *FROM *Order o *JOIN *o.packages p
>> *WHERE *...
>> *GROUP BY *s
>>
>> This would help greatly when getting the metadata of the query, because
>> above gives an exception and if I leave it out it just gives generated
>> names:
>>
>> *QueryMetadata *metaData = query.getMetaData(oc.getEntityResolver());
>> *List*<*Object*> mapping = metaData.getResultSetMapping();
>> *for *(*int *i = 1; i < mapping.size(); i++)
>> {
>> *ScalarResultSegment *segment = (*ScalarResultSegment*) mapping.get(i);
>> System.out.println(segment.getColumn());
>> }
>>
>> Mark
>>
>>
>> On Fri, Oct 31, 2014 at 9:50 AM, Andrus Adamchik <an...@objectstyle.org>
>> wrote:
>>
>>>> My problem with the solution is just the integration into Cayenne.
>>>
>>> EJBQLQuery supports aggregates:
>>>
>>> http://cayenne.apache.org/docs/3.1/cayenne-guide/queries.html#ejbqlquery
>>>
>>> The problem with it is that the query itself can only be created from
>>> String using a JPQL-like object query language, so you forfeit the type
>>> safety of SelectQuery. Still it is a much better abstraction then
>>> SQLTemplate for instance. (And its integration into SelectQuery is on the
>>> agenda).
>>>
>>> Andrus
>>>
>>>
>>>> On Oct 31, 2014, at 1:38 AM, Mark Stobbe <ma...@gmail.com>
>>> wrote:
>>>>
>>>> @Andrus, I see. Good to know how to do this in Cayenne.
>>>>
>>>> @Michael, You are right. It makes more sense to let the database do the
>>> calculations. It prevents the race conditions when updating and ensures
>>> totals to be correct.
>>>> My problem with the solution is just the integration into Cayenne.
>>>> Do you use some extra classes to keep the totals? It would be perfect
>>> if the expression language could be extended to have an easy way to group
>>> and count associated entities!!
>>>>
>>>>
>>>>> On 30 okt. 2014, at 18:22, Michael Gentry <mg...@masslight.net>
>>> wrote:
>>>>>
>>>>> The utilities I mentioned also do SUM, AVG, MIN, and MAX ...
>>>>>
>>>>>
>>>>>> On Thu, Oct 30, 2014 at 12:35 PM, Mark Stobbe <ma...@gmail.com>
>>> wrote:
>>>>>> Oh, I also have the same problem with total cost with different
>>>>>> currencies...
>>>>>>
>>>>>>> On Thu, Oct 30, 2014 at 5:09 PM, Mark Stobbe <ma...@gmail.com>
>>> wrote:
>>>>>>>
>>>>>>> Hi Michael,
>>>>>>>
>>>>>>> I would like to display the count in a table for a whole bunch of
>>> orders.
>>>>>>> In theory I could use a "group by"-query to get the numbers I need
>>> and
>>>>>>> with proper configured indices this should be fairly quick, I guess.
>>>>>>>
>>>>>>> Is there a more transparent way of doing things, e.g. using lifecycle
>>>>>>> listeners, datachannel filters and such?
>>>>>>>
>>>>>>> Mark
>>>>>>>
>>>>>>> On Thu, Oct 30, 2014 at 4:15 PM, Michael Gentry <
>>> mgentry@masslight.net>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hi Mark,
>>>>>>>>
>>>>>>>> Is there a performance reason why you don't just do a count on the
>>>>>>>> packages that match the order?
>>>>>>>>
>>>>>>>> mrg
>>>>>>>>
>>>>>>>>
>>>>>>>> On Thu, Oct 30, 2014 at 11:00 AM, Mark Stobbe <
>>> markstobbe85@gmail.com>
>>>>>>>> wrote:
>>>>>>>>> Hi all,
>>>>>>>>>
>>>>>>>>> I was wondering what is the best way to update totals in a
>>> multi-user
>>>>>>>>> environment. For example, let's say we have an Order which can
>>> have one
>>>>>>>> or
>>>>>>>>> more Packages associated and we want to maintain a total package
>>> count
>>>>>>>> on
>>>>>>>>> the Order entity. How would you update this value when the user
>>> has the
>>>>>>>>> option to add/remove packages.
>>>>>>>>>
>>>>>>>>> So the entities looks like:
>>>>>>>>>
>>>>>>>>> *Order*
>>>>>>>>> --------
>>>>>>>>> id : bigint
>>>>>>>>> orderNumber : varchar
>>>>>>>>> nrOfPackages : int
>>>>>>>>>
>>>>>>>>> *Package*
>>>>>>>>> ------------
>>>>>>>>> id : bigint
>>>>>>>>> packageNumber : varchar
>>>>>>>>> *fk_order : bigint*
>>>>>>>>>
>>>>>>>>> What do you guys use to solve this?
>>>>>>>>> Mark
>>>>>>>
>>>>>>>
>>>>
>>>
>>>
>>
Re: Updating totals in a multi-user environment
Posted by Mark Stobbe <ma...@gmail.com>.
Actually, I solved it already in another way, but now I want to use joins
in EJBQL which depend on a discriminator column, e.g.
*Table "ISSUE"*
issueNumber : varchar
referenceNumber : varchar
referenceType : varchar
*Table "ORDER"*
orderNumber : varchar
Orders can have multiple issues by using its *orderNumber* as a
*referenceNumber* and *referenceType = "ORDER"* in the ISSUE table.
I don't think this is possible at the moment with EJBQL, right?
Mark
On Sat, Nov 1, 2014 at 3:44 PM, Mark Stobbe <ma...@gmail.com> wrote:
> Thank you Andrus!
> I managed to get most of it into the existing framework. One quick
> question, is it possible to use aliases for fields in the EJBQL query, e.g.:
>
> *SELECT *o, *COUNT*(p) *AS *numberOfPackages
> *FROM *Order o *JOIN *o.packages p
> *WHERE *...
> *GROUP BY *s
>
> This would help greatly when getting the metadata of the query, because
> above gives an exception and if I leave it out it just gives generated
> names:
>
> *QueryMetadata *metaData = query.getMetaData(oc.getEntityResolver());
> *List*<*Object*> mapping = metaData.getResultSetMapping();
> *for *(*int *i = 1; i < mapping.size(); i++)
> {
> *ScalarResultSegment *segment = (*ScalarResultSegment*) mapping.get(i);
> System.out.println(segment.getColumn());
> }
>
> Mark
>
>
> On Fri, Oct 31, 2014 at 9:50 AM, Andrus Adamchik <an...@objectstyle.org>
> wrote:
>
>> > My problem with the solution is just the integration into Cayenne.
>>
>> EJBQLQuery supports aggregates:
>>
>> http://cayenne.apache.org/docs/3.1/cayenne-guide/queries.html#ejbqlquery
>>
>> The problem with it is that the query itself can only be created from
>> String using a JPQL-like object query language, so you forfeit the type
>> safety of SelectQuery. Still it is a much better abstraction then
>> SQLTemplate for instance. (And its integration into SelectQuery is on the
>> agenda).
>>
>> Andrus
>>
>>
>> > On Oct 31, 2014, at 1:38 AM, Mark Stobbe <ma...@gmail.com>
>> wrote:
>> >
>> > @Andrus, I see. Good to know how to do this in Cayenne.
>> >
>> > @Michael, You are right. It makes more sense to let the database do the
>> calculations. It prevents the race conditions when updating and ensures
>> totals to be correct.
>> > My problem with the solution is just the integration into Cayenne.
>> > Do you use some extra classes to keep the totals? It would be perfect
>> if the expression language could be extended to have an easy way to group
>> and count associated entities!!
>> >
>> >
>> >> On 30 okt. 2014, at 18:22, Michael Gentry <mg...@masslight.net>
>> wrote:
>> >>
>> >> The utilities I mentioned also do SUM, AVG, MIN, and MAX ...
>> >>
>> >>
>> >>> On Thu, Oct 30, 2014 at 12:35 PM, Mark Stobbe <ma...@gmail.com>
>> wrote:
>> >>> Oh, I also have the same problem with total cost with different
>> >>> currencies...
>> >>>
>> >>>> On Thu, Oct 30, 2014 at 5:09 PM, Mark Stobbe <ma...@gmail.com>
>> wrote:
>> >>>>
>> >>>> Hi Michael,
>> >>>>
>> >>>> I would like to display the count in a table for a whole bunch of
>> orders.
>> >>>> In theory I could use a "group by"-query to get the numbers I need
>> and
>> >>>> with proper configured indices this should be fairly quick, I guess.
>> >>>>
>> >>>> Is there a more transparent way of doing things, e.g. using lifecycle
>> >>>> listeners, datachannel filters and such?
>> >>>>
>> >>>> Mark
>> >>>>
>> >>>> On Thu, Oct 30, 2014 at 4:15 PM, Michael Gentry <
>> mgentry@masslight.net>
>> >>>> wrote:
>> >>>>
>> >>>>> Hi Mark,
>> >>>>>
>> >>>>> Is there a performance reason why you don't just do a count on the
>> >>>>> packages that match the order?
>> >>>>>
>> >>>>> mrg
>> >>>>>
>> >>>>>
>> >>>>> On Thu, Oct 30, 2014 at 11:00 AM, Mark Stobbe <
>> markstobbe85@gmail.com>
>> >>>>> wrote:
>> >>>>>> Hi all,
>> >>>>>>
>> >>>>>> I was wondering what is the best way to update totals in a
>> multi-user
>> >>>>>> environment. For example, let's say we have an Order which can
>> have one
>> >>>>> or
>> >>>>>> more Packages associated and we want to maintain a total package
>> count
>> >>>>> on
>> >>>>>> the Order entity. How would you update this value when the user
>> has the
>> >>>>>> option to add/remove packages.
>> >>>>>>
>> >>>>>> So the entities looks like:
>> >>>>>>
>> >>>>>> *Order*
>> >>>>>> --------
>> >>>>>> id : bigint
>> >>>>>> orderNumber : varchar
>> >>>>>> nrOfPackages : int
>> >>>>>>
>> >>>>>> *Package*
>> >>>>>> ------------
>> >>>>>> id : bigint
>> >>>>>> packageNumber : varchar
>> >>>>>> *fk_order : bigint*
>> >>>>>>
>> >>>>>> What do you guys use to solve this?
>> >>>>>> Mark
>> >>>>
>> >>>>
>> >
>>
>>
>