Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Content-Type: text/plain; charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\))
Subject: Re: EJBQL: Arithmetic operations
From: Andrus Adamchik <andrus@objectstyle.org>
In-Reply-To: <B189D443-46EC-4211-AA89-8B9AD138113E@karlmenn.is>
Date: Tue, 16 Aug 2016 14:25:06 +0300
Content-Transfer-Encoding: quoted-printable
Message-Id: <0D5F800F-7267-4D89-8729-D0E64AA36FA4@objectstyle.org>
References: <43B9FC97-D422-479D-A745-9FF7AB702092@karlmenn.is> <95DA1376-6BF9-4890-AA00-842F72BED149@objectstyle.org> <5A8CC9BA-C43C-4A1B-9A6A-E9A4650F5BCF@karlmenn.is> <D5F085FD-1B76-44E1-95D3-047EFB8E8CF8@objectstyle.org> <2C2E010B-EAD5-4108-BDCC-F66C1183EE07@karlmenn.is> <4B81A3AD-7AD8-4F06-AE6C-C2AC1254EED4@objectstyle.org> <7EA8AF13-2E5D-454D-B876-0CC1AAFBAC81@karlmenn.is> <4883AA53-1493-498A-9016-EEE457DEF4A0@objectstyle.org> <B189D443-46EC-4211-AA89-8B9AD138113E@karlmenn.is>
To: user@cayenne.apache.org
archived-at: Tue, 16 Aug 2016 11:25:31 -0000

Cool. Appreciate the bug reports!

> On Aug 16, 2016, at 2:22 PM, Hugi Thordarson <hugi@karlmenn.is> wrote:
>=20
> Thanks Andrus :).
>=20
> It works for the most part but I=E2=80=99m still running into some =
problems. I think I=E2=80=99ll switch to SQL for now for the methods =
where this matters. I=E2=80=99m filing JIRAs for the issues I=E2=80=99ve =
encountered but FYI, this is where it fails:
>=20
> Failure 1:
> Wrong SQL generated for a DISTINCT count using a DB path. Example:
>=20
> Running EJBQL query:
> select count(distinct db:e.receipt_id) from Entry e
>=20
> Generates SQL:
> SELECT COUNT(DISTINCT *) AS sc0 FROM `strimillinn`.`fd_entry` t0
>=20
> Should generate SQL:
> SELECT COUNT(DISTINCT receipt_id) AS sc0 FROM `strimillinn`.`fd_entry` =
t0
>=20
> =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94
>=20
> Failure 2:
> EJBQL fails to find joins for relationships when DB-paths are used in =
the where-clause.
>=20
> Running EJBQL query:
> select r.entries from Receipt r where db:r.shop.name=3D'Holtagar=C3=B0ar=
'
>=20
> Results in a EJBQLException with the message:
> "No join configured for id r.shop"
>=20
> Running the same query using a regular path (not a DB path) works =
fine:
> select r.entries from Receipt r where r.shop.name=3D'Holtagar=C3=B0ar'
>=20
> Cheers,
> - hugi
>=20
>=20
>> On 16. =C3=A1g=C3=BA. 2016, at 07:26, Andrus Adamchik =
<andrus@objectstyle.org> wrote:
>>=20
>> Looks like DB path prefix was ignored everywhere except in =
conditions. E.g. here it would work, but nowhere else:
>>=20
>> select p.estimatedPrice from Painting p WHERE  db:p.ESTIMATED_PRICE > =
2
>>=20
>> Since expanding it to SELECT clause didn't require a parser change, =
the fix was actually pretty easy. I just committed it. See =
5869e40f61e547cb33285803eede286a780cb0f3 and CAY-2102.
>>=20
>> Andrus
>>=20
>>> On Aug 15, 2016, at 9:53 PM, Hugi Thordarson <hugi@karlmenn.is> =
wrote:
>>>=20
>>> I=E2=80=99m not quite getting this to work=E2=80=A6 In this case I =
have a list of Receipts linked to Shops, and I want to select the shop =
IDs (simplified example). If I try:
>>>=20
>>> select db:r.shop_id from Receipt r
>>>=20
>>> I=E2=80=99ll receive an error that =E2=80=9Cshop_id=E2=80=9D is an =
invalid path component.
>>>=20
>>>=20
>>>=20
>>>=20
>>>> On 15. =C3=A1g=C3=BA. 2016, at 17:18, Andrus Adamchik =
<andrus@objectstyle.org> wrote:
>>>>=20
>>>>> One last question: Can I use db:-paths in EJBQL queries?
>>>>=20
>>>> Yes, db path should be supported everywhere.=20
>>>>=20
>>>>> Particularly, I=E2=80=99m attempting to use FK-columns (that =
aren=E2=80=99t exposed in the ObjEntity) in my queries, perhaps =
there=E2=80=99s another way?
>>>>=20
>>>>=20
>>>> If possible, use a relationship name instead of FK. E.g.=20
>>>>=20
>>>> "SELECT p FROM Painting p WHERE p.artist =3D 1"
>>>>=20
>>>> but if not, db path is an option.
>>>>=20
>>>> Andrus
>>>>=20
>>>>=20
>>>>> On Aug 15, 2016, at 7:02 PM, Hugi Thordarson <hugi@karlmenn.is> =
wrote:
>>>>>=20
>>>>> Thanks for this Andrus. One last question: Can I use db:-paths in =
EJBQL queries? Particularly, I=E2=80=99m attempting to use FK-columns =
(that aren=E2=80=99t exposed in the ObjEntity) in my queries, perhaps =
there=E2=80=99s another way?
>>>>>=20
>>>>> Cheers,
>>>>> - hugi
>>>>>=20
>>>>>=20
>>>>>=20
>>>>>> On 15. =C3=A1g=C3=BA. 2016, at 15:31, Andrus Adamchik =
<andrus@objectstyle.org> wrote:
>>>>>>=20
>>>>>> Ah yeah, now checking the code I am starting to remember. =
Arithmetic expressions are not allowed in SELECT clause, only in WHERE. =
There's no real reason for it other then us initially basing EJBQL =
parser on some early JPA spec that probably didn't support it. We need =
to add it to the parser. For now it won't work.
>>>>>>=20
>>>>>> Andrus
>>>>>>=20
>>>>>>=20
>>>>>>> On Aug 15, 2016, at 5:49 PM, Hugi Thordarson <hugi@karlmenn.is> =
wrote:
>>>>>>>=20
>>>>>>> Hi Andrus,
>>>>>>>=20
>>>>>>>> On 15. =C3=A1g=C3=BA. 2016, at 06:30, Andrus Adamchik =
<andrus@objectstyle.org> wrote:
>>>>>>>>=20
>>>>>>>> Hi Hugi,
>>>>>>>>=20
>>>>>>>> Sorry for delayed reply. I swear we had the BNF for EJBQL in =
the docs, but apparently we don't. Here is the closest thing to it - a =
JavaCC grammar:
>>>>>>>>=20
>>>>>>>> =
https://github.com/apache/cayenne/blob/master/cayenne-server/src/main/jjtr=
ee/org/apache/cayenne/ejbql/EJBQLParser.jjt
>>>>>>>>=20
>>>>>>>> We do support simple arithmetic operations. Search for =
'arithmetic_' clauses in the .jjt.
>>>>>>>=20
>>>>>>> I=E2=80=99m not as smart as you think=E2=80=94I haven=E2=80=99t =
touched JavaCC grammar before, though I could figure it out but I =
don=E2=80=99t see how to do this :). Can you tell me how I can do =
something equivalent to =E2=80=9Cselect e.price*e.quantity from Entry =
e=E2=80=9D?
>>>>>>>=20
>>>>>>> Cheers,
>>>>>>> - hugi
>>>>>>=20
>>>>>=20
>>>>=20
>>>=20
>>=20
>=20