Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » RIGHT OUTER JOIN(How to use right outer join with eclipse link JPA.. )
RIGHT OUTER JOIN [message #898643] Thu, 26 July 2012 22:06 Go to next message
Nik s is currently offline Nik s
Messages: 3
Registered: July 2012
Junior Member
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]

Report message to a moderator

Re: RIGHT OUTER JOIN [message #898770 is a reply to message #898643] Fri, 27 July 2012 09:52 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 995
Registered: July 2009
Senior Member
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 Go to previous messageGo to next message
Nik s is currently offline Nik s
Messages: 3
Registered: July 2012
Junior Member
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 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 995
Registered: July 2009
Senior Member
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 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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'


James : Wiki : Book : Blog : Twitter
Re: RIGHT OUTER JOIN [message #900390 is a reply to message #899106] Mon, 06 August 2012 16:30 Go to previous messageGo to next message
Nik s is currently offline Nik s
Messages: 3
Registered: July 2012
Junior Member
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 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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


James : Wiki : Book : Blog : Twitter
Previous Topic:Entity Isolation Level
Next Topic:Extensible Entities with extensions of type other than string
Goto Forum:
  


Current Time: Mon Apr 21 05:55:41 EDT 2014

Powered by FUDForum. Page generated in 0.03300 seconds