ansaurus

Question

HQL Join Fetch question: aliasing association

Answer 1

+1 A:

Second query won't work due to invalid syntax. First one will work, but (I'm guessing here) not the way you want it to.

I take it you want to select some As and populate their collection of Bs only with Bs satisfying specific criteria? You can't. Entire collection of owned Bs will be fetched for each A instance (putting aside technical implications dealing with batch sizes). This is done in order to prevent accidental orphaning of Bs should you choose to save your A back with incomplete collection of Bs.

If I misunderstood and that's not what you meant, please clarify your question.

ChssPly76 2009-10-12 23:40:19

Yup exactly correct. But on pg.651 of JPA with Hibernate the first bullet point led me to believe that something like this could work. So in essence what you are saying is that I have to pull the entire collection of Bs and then iterate over to collection to look for the criteria I am looking for. If you have the book maybe you can provide an example of what was meant in that first bullet point.

non sequitor 2009-10-13 00:13:29

I don't have the book. If you post the appropriate quote (with needed context) into your question, I can take a look. Note that if your association (A - B) is bi-directional, you can select just the Bs you want (if that makes sense for your scenario) but the As they're linked to would still have all appropriate Bs in their collections.

ChssPly76 2009-10-13 00:25:04

@ChssPly76: So just let me ask you this let's say I have domain object A which has a set of Bs, the association is bi-directional. Now what I want to do is select a particular A according to id and also all Bs that meet a criteria e.g. select A a where a.id=1 and a.setofB where name in setofB = 'query'

non sequitor 2009-10-13 02:49:54

You can, but said Bs are not going to be stored in A.bs collection. If you only have a single A, you can write a query like "from B b where b.a.id = :id and b.name = 'query'" and you'll get back your set of Bs; each will point to the same A instance. That A instance will have ALL of its Bs (not just the ones returned from the query) in its 'bs' collection.

ChssPly76 2009-10-13 03:26:32

@ChssPly76: I just saw what i wanted in an answer u gave http://stackoverflow.com/questions/1167630/hql-and-one-to-many-queries the only difference being that I want 1 particular Player so in the where clause can I add 'and p.id=1'?

non sequitor 2009-10-13 03:36:20

@ChssPly76: I think this is what you are trying to explain to me http://stackoverflow.com/questions/592825/jpa-please-help-understanding-join-fetch

non sequitor 2009-10-13 04:46:44

No, neither of those questions is related to your case. They're not concerned with filtering collection elements themselves; only base entities **based** on collection elements' properties.

ChssPly76 2009-10-13 05:14:59

Hmmm ok so how do you suggest I go about getting a collection of Bs that belong to A, in a bi-directional association, remove the Bs from that collection that meet a certain criteria like(b.name= 'query') and then persist that change?

non sequitor 2009-10-13 14:54:42

Maybe I can use "from B b where b.a.id = :id and b.name = 'query'" and then remove the association to A where "b.name = 'query'" in my service layer by iterating and remove on that condition then persist the collection of Bs, only this time some or all will not have a reference to A. How does that sound? If you know of a more efficient way of doing it let me know, thanks.

non sequitor 2009-10-13 15:00:14

I'm not sure anymore what you're trying to do here. Are you querying or deleting? If we're talking about just a few instances of B within a single A, just load it and filter in code (and save it back if you want to delete Bs). If we're talking about hundreds (or more) of records, consider using batch updates with "DELETE" statement

ChssPly76 2009-10-13 16:50:30

Yup a few instances of B within a single A, I forgot to mention that this is a many-to-many relation. What would the code look like in this instance. Would it be something like this 'session.createFiler(a.getBs(),"where this.name = :query").list()'

non sequitor 2009-10-13 19:15:41

For a few instances just load A directly: session.get(A.class, id). Iterate over Bs, remove what you don't want, save A back

ChssPly76 2009-10-13 20:10:56

what's a few 10 or 100? And if I had like a few hundred and I used executeUpdate() to delete, should I always flush() the session after to keep the session cache in sync?

non sequitor 2009-10-13 20:20:47

You won't be able to delete just the association from many-to-many via HQL; it'll have to be a SQL query. So yes, in that case you'd want to both flush() the session and evict your A instance prior to deleting.

ChssPly76 2009-10-13 20:37:16

What about the "what's a few 10 or 100?"

non sequitor 2009-10-13 20:41:56

That depends on your tables really and how often you need to do this. If you're doing it once in a while for a single A then even a 100 might be OK. Run it and see; refactoring to "delete" should not be a huge deal if you see inadequate performance.

ChssPly76 2009-10-13 21:37:37

ansaurus

tags:

views:

answers:

HQL Join Fetch question: aliasing association

related questions