Eclipse Community Forums: EclipseLink

Home » Eclipse Projects » EclipseLink » RIGHT OUTER JOIN(How to use right outer join with eclipse link JPA.. )

Thu, 26 July 2012 22:06

Eclipse User

I am new to JPA/Eclipse link -
i have created entity and mapping ManyToOne from issue to queue and OneToMany from Queue to Issue. i am able to fire a query with left join but not right join ...

Please can some one help how to achieve this.?

I am trying to do simple query test which is
select count(c.issue_id), q.queue_name from issue i RIGHT OUTER JOIN queue q
on
q.queue_id = i.issue_queue_id group by q.queue_name having i.issue_type = 'local'

----------
issue:
----------
issue_id int (PK)
issue_queue_id (FK ref to queue table queue_id )
issue_name varchar2(50),
issue_type varchar2(10)

----------
queue:
----------
queue_id int (PK)
queue_name varchar2(10)

[Updated on: Thu, 26 July 2012 22:07] by Moderator

Re: RIGHT OUTER JOIN [message #898770 is a reply to message #898643]

Fri, 27 July 2012 09:52

Eclipse User

The JPA spec only defines a Left Outer Join. Feel free to file an EclipseLink enhancement or request its inclusion in the JPA specification.

In the mean time, you will need to rework your query to use a left join instead of a right:
"select count(<i?>.issue_id), q.queue_name from queue q LEFT OUTER JOIN issue i
on
i.issue_queue_id = q.queue_id group by q.queue_name having i.issue_type = 'local'"

Best Regards,
Chris

Re: RIGHT OUTER JOIN [message #898806 is a reply to message #898770]

Fri, 27 July 2012 13:03

Eclipse User

Thanks but i am getting error.. Please check below is my entity configuration

Caused by: Exception [EclipseLink-8023] (Eclipse Persistence Services - 2.1.3.v2
0110304-r9073): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [select count(i._issueKey), q from Queue q
LEFT OUTER JOIN Issue i on i.queueId._queuekey = q._queuekey ].
Internal Exception: org.eclipse.persistence.internal.libraries.antlr.runtime.Ear
lyExitException

--------------
Queue
--------------
...
..
@Id
@Column(name="queue_key")
private int _queuekey;
..

@ManyToOne
@JoinColumn(name="queue_id" )
private Queue queueId;

public Queue getQueueId() {
return queueId;
}
...

---------
Issue
---------

@Id
@Column(name="issue_key")
private int _issueKey;
....
@OneToMany(mappedBy="queueId",targetEntity=TestIssue.class, fetch=FetchType.EAGER)
private List<TestIssue> issues;
....
..

query = entityManager.createQuery("select count(i._issueKey) q from Queue q LEFT OUTER JOIN Issue i "
+ "on i.queueId._queuekey = q._queuekey ");
List<Object> obj = query.getResultList();

Re: RIGHT OUTER JOIN [message #899106 is a reply to message #898806]

Mon, 30 July 2012 11:05

Eclipse User

Support for using 'ON' in the from clause was only added in EclipseLink 2.4 through feature: http://wiki.eclipse.org/EclipseLink/DesignDocs/312146

Best Regards,
Chris

Re: RIGHT OUTER JOIN [message #899668 is a reply to message #899106]

Wed, 01 August 2012 13:58

Eclipse User

JPQL is normally defined in terms of objects, not data, so no ON clause is normally required,

select count(c.issue_id), q.queue_name from Issue i LEFT JOIN i.queue q group by q.queue_name having i.issue_type = 'local'

or,

select count(c.issue_id), q.queue_name from Queue q LEFT JOIN q.issues i group by q.queue_name having i.issue_type = 'local'

Re: RIGHT OUTER JOIN [message #900390 is a reply to message #899106]

Mon, 06 August 2012 16:30

Eclipse User

Thank you very much Chris... i got that and its working

select queue, count(issue) as total_count from QueueState queue LEFT JOIN queue.issueRef iss where iss.callType in :issueType
group by queue order by queue.queueSequence

Other queustion i have - how can i add the subquery to it.. like below

SELECT q.queue_name, COUNT(c.issue_key) as TOTAL_COUNT
FROM queue_state q LEFT JOIN (select * from issue where issue_type = 'xyz') iss ON (iss.queue_state_key = q.queue_state_key) GROUP BY q.queue_name order by q.queueSequence asc

-----

Re: RIGHT OUTER JOIN [message #900785 is a reply to message #900390]

Wed, 08 August 2012 09:17

Eclipse User

You are probably better off using a native SQL query for a query this complex.

JPA does not support subselects in the FROM clause, but EclipseLink does have some support for this in 2.4. You would need to define the subselects as valid JPQL.

FROM queue_state q LEFT JOIN (Select i.queueStateKey from Issues i where i.type = 'xyz') iss ON (iss.queueStateKey = q.queueStateKey) GROUP BY q.queueName order by q.queueSequence asc

Previous Topic:	Entity Isolation Level
Next Topic:	Extensible Entities with extensions of type other than string

Goto Forum:

-=] Back to Top [=-

Current Time: Sat Jul 26 02:54:31 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter