On Tue, 2006-05-30 at 15:10 +0200, Tore Halset wrote:
> On May 30, 2006, at 10:25, Øyvind Harboe wrote:
>
> > If I manually modify the generated query from the orExp() case to use
> > a LEFT OUTER JOIN instead of INNER JOIN, I get the expected
> > result.
>
> AFAIK Cayenne does not support outer join yet. Google got me to this
> one:
Oooopsss.... The JOIN is a red herring here. See corrected summary of my
problem.
The INNER JOIN is not generated by Cayenne from the Java
code. I copied the SELECT statement into MS SQL manager and it will
rewrite Cayenne's SQL statement to contain an INNER JOIN as part of the
reformatting.
--
Øyvind Harboe
http://www.zylin.com
If I run these two queries seperately, they find
more records than if I "orExp" together the qualifiers.
Tested on SQL server & MS Access, same result.
=================================================
// This Java code will result in the SQL query immediately below...
SelectQuery query1=new SelectQuery(ElcRole.class);
query1.setQualifier(ExpressionFactory.matchExp(ElcRole.ELC_ROLEMEMBER_ARRAY_PROPERTY
+ "." + ElcRolemember.TO_SYS_USER_PROPERTY, getSysUser()));
List l=context.performQuery(query1);
return l;
SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID FROM ELC_ROLE t0,
ELC_ROLEMEMBER t1 WHERE t0.ELCROLE_ID = t1.ELCROLE_ID AND (t1.SYSUSER_ID =
'BD93F348-8C02-4742-BA97-2456E5CD3881')
ELCROLE_NAME ELCROLE_ID
SYSUSER_ID
Prosesseier
E7BE040B-F0E3-4ecf-88A6-4B81BF7AE865
Saksbehandler HMS
0636F7A4-006B-4926-873F-B5CF888F239C
Saksbehandler Innkjøp E5DED96B-5F93-4575-85A4-1C14D94B9744
Saksbehandler Lager
48461B6C-791B-11d4-8792-00508BCE14E0 FF3C97BB-8899-444c-8C6E-91E64D087E86
// This Java code will result in the SQL query immediately below...
SelectQuery query1=new SelectQuery(ElcRole.class);
query1.setQualifier(ExpressionFactory.matchExp(ElcRole.TO_SYS_USER_PROPERTY,
getSysUser()));
List l=context.performQuery(query1);
return l;
SELECT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID FROM ELC_ROLE t0 WHERE
t0.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881'
ELCROLE_NAME ELCROLE_ID
SYSUSER_ID
Saksbehandler Drift/Vedlikehold 04190624-7503-11d4-9078-00508BD89D95
BD93F348-8C02-4742-BA97-2456E5CD3881
Saksbehandler Produksjon 04190625-7503-11d4-9078-00508BD89D95
BD93F348-8C02-4742-BA97-2456E5CD3881
Testrolle 4
07AF23BD-7DCA-41cc-9268-A4A08CF0B2DF BD93F348-8C02-4742-BA97-2456E5CD3881
Testrolle 2
A5E8553F-BB4D-4158-BA53-D8F206817938 BD93F348-8C02-4742-BA97-2456E5CD3881
Saksbehandler Forespørsel A5F7E0D8-E204-478f-A7A4-DCC07C712673
BD93F348-8C02-4742-BA97-2456E5CD3881
Testrolle 1
AEBEDF2E-2656-4733-99A7-2EAC231DE379 BD93F348-8C02-4742-BA97-2456E5CD3881
Testrolle 3
B90B4CCB-43EA-4187-B139-4138041A2F8D BD93F348-8C02-4742-BA97-2456E5CD3881
Saksbehandler Transport E17775E2-897A-41d3-8CC0-D96E24E15DE7
BD93F348-8C02-4742-BA97-2456E5CD3881
Does not find "Testrolle 4"
===========================
// This Java code will result in the SQL query immediately below...
SelectQuery query1=new SelectQuery(ElcRole.class);
query1.setQualifier(ExpressionFactory.matchExp(ElcRole.ELC_ROLEMEMBER_ARRAY_PROPERTY
+ "." + ElcRolemember.TO_SYS_USER_PROPERTY, getSysUser()).
orExp(ExpressionFactory.matchExp(ElcRole.TO_SYS_USER_PROPERTY, getSysUser())));
List l=context.performQuery(query1);
SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID FROM ELC_ROLE t0,
ELC_ROLEMEMBER t1 WHERE t0.ELCROLE_ID = t1.ELCROLE_ID AND ((t1.SYSUSER_ID =
'BD93F348-8C02-4742-BA97-2456E5CD3881') OR (t0.SYSUSER_ID =
'BD93F348-8C02-4742-BA97-2456E5CD3881'))
ELCROLE_NAME ELCROLE_ID
SYSUSER_ID
Prosesseier
E7BE040B-F0E3-4ecf-88A6-4B81BF7AE865
Saksbehandler Forespørsel A5F7E0D8-E204-478f-A7A4-DCC07C712673
BD93F348-8C02-4742-BA97-2456E5CD3881
Saksbehandler HMS 0636F7A4-006B-4926-873F-B5CF888F239C
Saksbehandler Innkjøp E5DED96B-5F93-4575-85A4-1C14D94B9744
Saksbehandler Lager 48461B6C-791B-11d4-8792-00508BCE14E0
FF3C97BB-8899-444c-8C6E-91E64D087E86
Saksbehandler Produksjon 04190625-7503-11d4-9078-00508BD89D95
BD93F348-8C02-4742-BA97-2456E5CD3881
Saksbehandler Transport E17775E2-897A-41d3-8CC0-D96E24E15DE7
BD93F348-8C02-4742-BA97-2456E5CD3881
Hmmm.... I think the SQL should have been a LEFT OUTER JOIN, but Cayenne does
not support
OUTER JOINs. Is OUTER JOIN a red herring here?
SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID
FROM ELC_ROLE t0 LEFT OUTER JOIN
ELC_ROLEMEMBER t1 ON t0.ELCROLE_ID = t1.ELCROLE_ID
WHERE (t1.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881') OR
(t0.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881')
=>
Prosesseier E7BE040B-F0E3-4ecf-88A6-4B81BF7AE865
Saksbehandler Drift/Vedlikehold 04190624-7503-11d4-9078-00508BD89D95
BD93F348-8C02-4742-BA97-2456E5CD3881
Saksbehandler Forespørsel A5F7E0D8-E204-478f-A7A4-DCC07C712673
BD93F348-8C02-4742-BA97-2456E5CD3881
Saksbehandler HMS 0636F7A4-006B-4926-873F-B5CF888F239C
Saksbehandler Innkjøp E5DED96B-5F93-4575-85A4-1C14D94B9744
Saksbehandler Lager 48461B6C-791B-11d4-8792-00508BCE14E0
FF3C97BB-8899-444c-8C6E-91E64D087E86
Saksbehandler Produksjon 04190625-7503-11d4-9078-00508BD89D95
BD93F348-8C02-4742-BA97-2456E5CD3881
Saksbehandler Transport E17775E2-897A-41d3-8CC0-D96E24E15DE7
BD93F348-8C02-4742-BA97-2456E5CD3881
Testrolle 1 AEBEDF2E-2656-4733-99A7-2EAC231DE379
BD93F348-8C02-4742-BA97-2456E5CD3881
Testrolle 2 A5E8553F-BB4D-4158-BA53-D8F206817938
BD93F348-8C02-4742-BA97-2456E5CD3881
Testrolle 3 B90B4CCB-43EA-4187-B139-4138041A2F8D
BD93F348-8C02-4742-BA97-2456E5CD3881
Testrolle 4 07AF23BD-7DCA-41cc-9268-A4A08CF0B2DF
BD93F348-8C02-4742-BA97-2456E5CD3881
