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/10/30 16:00:10 UTC

Updating totals in a multi-user environment

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>.

> Is there a more transparent way of doing things, e.g. using lifecycle
> listeners, datachannel filters and such?

If you want the update to happen on commit, you can use a PostPersist/PostRemove listener on Package. And perhaps update the count with raw SQL to avoid a race condition between multiple processes updating the same order (or fetch and update Order object, if you don't expect this to happen).

Andrus



> On Oct 30, 2014, at 7: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 <mg...@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 <ma...@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 Michael Gentry <mg...@masslight.net>.

Hi Mark,

Here is the code I mentioned if you want to try it (again, 3.0.2 --
might need tweaked for 3.1):

Actual utility code:

https://github.com/mrg/cbe/blob/master/FetchingObjects/Aggregates/src/main/java/cbe/fetching/utilities/AggregateUtils.java
https://github.com/mrg/cbe/blob/master/FetchingObjects/Aggregates/src/main/java/cbe/fetching/utilities/AggregateTranslator.java


Example that uses it:

https://github.com/mrg/cbe/blob/master/FetchingObjects/Aggregates/src/main/java/cbe/fetching/Aggregates.java


In your Order class, you could create a method:

public int getPackageCount()
{
  // Use AggregateUtils to perform a COUNT on Packages matching this Order.
  // query = new SelectQuery(Package.class);
  // query.setQualifier(ExpressionFactory.matcExp(Package.ORDER_PROPERTY,
this));
  // return AggregateUtils.count(getDataContext(), query);
}

This might be too slow if you need to do a LOT of Orders' Package
counts at once, but if nothing else, maybe it'll prove helpful/useful
somewhere.

mrg

PS. I'm sure there are typos above...didn't test any of it.  :-)


On Thu, Oct 30, 2014 at 6:38 PM, 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 <mg...@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 <ma...@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 Andrew Lindesay <ap...@lindesay.co.nz>.

Hello Hugi;

You should be able to use EJBQL for this purpose as well.  Here is a 
(nicely re-formatted) example;

   SELECT
     DISTINCT ur.user.nickname
   FROM
     UserRating ur
   WHERE
     ur.user.active=true
     AND ur.active=true

Have a go and see if it fits your needs.

As Andrus has pointed out, using EJBQL has the disadvantage that the 
query is assembled as a string.

Regards;

On 31/10/14 10:58 pm, Hugi Thordarson wrote:
> Once again, I’m looking into migrating to Cayenne from EOF :). One question though: Our code depends heavily on raw row fetching (data rows) that traverses relationships. I haven’t looked much at the Cayenne code, but do you believe adding support for this to Cayenne would be a huge undertaking? Or does Cayenne perhaps provide an alternative method to achieve the same results?

-- 
Andrew Lindesay

Re: Updating totals in a multi-user environment

Posted by Aristedes Maniatis <ar...@maniatis.org>.

On 31/10/2014 8:58pm, Hugi Thordarson wrote:
> Once again, I’m looking into migrating to Cayenne from EOF :). One question though: Our code depends heavily on raw row fetching (data rows) that traverses relationships. I haven’t looked much at the Cayenne code, but do you believe adding support for this to Cayenne would be a huge undertaking? Or does Cayenne perhaps provide an alternative method to achieve the same results?

I'm not sure if I'm understanding correctly, but do you want to prefetch joins and create regular Java objects with all the joins pulled in for performance?

Or are you talking about raw data rows which are just giant maps of key-values with data pulled from across joins?

If that later, perhaps you can create a database view and map that to a Cayenne entity for read-only access. But I don't quite see the point (other than supporting your legacy code).

Ari


-- 
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A

Re: Updating totals in a multi-user environment

Posted by Hugi Thordarson <hu...@karlmenn.is>.

Hi all.

Once again, I’m looking into migrating to Cayenne from EOF :). One question though: Our code depends heavily on raw row fetching (data rows) that traverses relationships. I haven’t looked much at the Cayenne code, but do you believe adding support for this to Cayenne would be a huge undertaking? Or does Cayenne perhaps provide an alternative method to achieve the same results?

Cheers,
- hugi

// Hugi Thordarson
// http://www.loftfar.is/ <http://www.loftfar.is/>
// s. 895-6688



> On 31. okt. 2014, at 08:50, 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 <mg...@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 <ma...@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
>>>>> 
>>>>> 
>> 
>