JPQL select relation traversal

[quincy.mitchell]

The following reference:

10.1.2. Relation Traversal
http://download.oracle.com/docs/cd/E11035_01/kodo41/full/html/ejb3_overview_query.html

states that

SELECT x FROM Magazine x, IN(x.articles) y WHERE y.authorName = 'John Doe'

will select all magazines that have any articles written by John Doe.

However I don't want to select the magazine with all it's associations. Rather, I want the same magazines returned with their articles collections filtered to only have the articles written by John Doe.

Is this possible in JPQL? If not, what would be a way of what I want with low network traffic. (assume database is in different server)  Maybe I can do this with HQL?

For elaboration:

SELECT x FROM Magazine x, IN(x.articles) y WHERE x.name='times' and y.authorName = 'John Doe'

I want Time magazines to be returned with only the associations to articles written by john doe. If a Time magazine (with 2 articles) has article1 which is written by Bob and article2 which is written by John Doe then this magazine will be returned with only the association to article2.

Thanks,
Quincy Mitchell

p.s> due to the importance / urgency I double posted:  http://forums.oracle.com/forums/thread.jspa?messageID=9167990&#9167990

[wdfink]

As I understand you right,

you will have the magazine with the loaded relation to article where only the article from 'John Doe' are included!?

The JPA select itself will not load the articles (except EAGER loading), only a SQL select the correct magazin.

If you access the articles you will get all articles of the magazin, nevertheless whether you use EAGER or LAZY loading.

If you want to have the article you might revert your select and use the relation to magazin (with eager loading) or do two selects.

SELECT y FROM Articles WHERE y.authorName = 'John Doe' and y.magazin.name='times'

The select to get the magazin for the 'two select' aproach will be the same as yours.

It depends to your requirements what you should use, two selects might be generate different SQL's over all.

But it will be a difficult and unstable optimization (for EJB2 I did such).

[quincy.mitchell]

Wolf-Dieter Fink wrote:

If you access the articles you will get all articles of the magazin, nevertheless whether you use EAGER or LAZY loading.

Correct.  This is the issue.  I don't want all articles of the magazine only those which hold to the given condition.

Wolf-Dieter Fink wrote:

If you want to have the article you might revert your select and use the relation to magazin (with eager loading) or do two selects.

Yes.  This is the only way I have thought of to get the correct output.  The cons of doing this are needing to filter out (intersect) results of multiple queries in the business code as well as the possibly high network traffic due to the individual results of a query could be large, but their intersection being small.

Does that make sense?  Maybe I should elaborate?

[quincy.mitchell]

The simplest answer that I have found is to make a database view and create a jpa entity for this view.  So now in the same entity you'll have magazine names and their article authors.