[CAY-560] Add support for outer joins

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 3.0M4, 3.0
• Component/s: Core Library
• Assignee: Mike Kienenberger
• Reporter: Øyvind Harboe
• Created: 31/May/06 09:33
• Updated: 01/Jun/11 15:29
• Resolved: 06/May/08 20:43

Description

Currently outer joins are not supported.

See thread:

http://objectstyle.org/cayenne/lists/cayenne-user/2006/05/0165.html

// 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);

Generates =>

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')

But should have generated =>

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')

Issue Links

• is related to: CAY-732 Incorrect query with self joins
• is related to: CAY-929 SelectTranslator support for standard SQL join syntax, including outer joins
• relates to: CAY-802 Split Expressions