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 <[email protected]> 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 <[email protected]> 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 <[email protected]> >> 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 <[email protected]> >>> 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 <[email protected]> >>> wrote: >>>>> >>>>> The utilities I mentioned also do SUM, AVG, MIN, and MAX ... >>>>> >>>>> >>>>>> On Thu, Oct 30, 2014 at 12:35 PM, Mark Stobbe <[email protected]> >>> 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 <[email protected]> >>> 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 < >>> [email protected]> >>>>>>> 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 < >>> [email protected]> >>>>>>>> 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 >>>>>>> >>>>>>> >>>> >>> >>> >>
