Question about the tutorial

clam61 · **Joined:** Thu Aug 30, 2007 5:19 pm **Posts:** 5

Referring to http://www.hibernate.org/hib_docs/reference/en/html/tutorial.html

Refer to section "1.3.2. A unidirectional Set-based association"

I understand that calling Person.getEvents() will return a Set of Event objects for a single Person.

However, if one wanted to acquire a Set of Events for a single person where the Event had an EVENT_ID > 3...or some other arbitrary restriction.

How would one impose this restriction?

mooritze · **Joined:** Tue Oct 10, 2006 3:23 am **Posts:** 33

Either adding @Filter to the collection mapping:

@Filter(name="bla",
condition="EVENT_ID>3")

so that you can enable or disable it,
or do @Where:

@OneToMany(...)
@Where(clause="1=1")

so that it's always on.

Or do it in hql:

From Person p left join fetch p.event e where e.id>3

your pick!!

* rate if this helps *

christian · **Posted:** Thu Aug 30, 2007 7:32 pm

This query is invalid and the mappings I don't recommend. The question makes no sense. You can't obtain a "set of events" and then restrict the set to one event.

mooritze · **Joined:** Tue Oct 10, 2006 3:23 am **Posts:** 33

Christian,
He was trying to restrict to event_id>3 that could be more than 1.

I don't see were the query is invalid except from some typos and the fact that I made the assumption that he had an event reference in Person.

Are you not recommending using filter? I used those since I figured them out from the book p.544

christian wrote:

This query is invalid and the mappings I don't recommend. The question makes no sense. You can't obtain a "set of events" and then restrict the set to one event.

clam61 · **Joined:** Thu Aug 30, 2007 5:19 pm **Posts:** 5

actually...looking at my own situation, it's not a many to many relationship. i have two one to many associations based on foreign keys

Code:

create table A( aId bigint not null primary key )
create table B( bId bigint not null primary key, AId bigint not null )
create table C( cId bigint not null primary key, BId bigint not null, otherField bigint not null )

i have set up my associations a unidirectional one-to-many association on a foreign key from tables A to B and B to C. I have Set in A which is a collection of B objects and a Set in B which is a collection of C objects

What I would like to accomplish in HQL is this:

Code:

select c.* 
from A a,B b,C c 
where a.aId = 1 and 
a.aId = b.aId and
b.bId = c.bId and 
c.otherField = 2

I tried lots of different queries using join fetch but I'm not able to succeed.

mooritze · **Joined:** Tue Oct 10, 2006 3:23 am **Posts:** 33

So I think you could do something like:

From C c where c.otherField=2 and c.id in( Select c1.id From A a left join a.b b left join b.c c1)

Haven't tried that though.

* rate if this helps *

clam61 wrote:

actually...looking at my own situation, it's not a many to many relationship. i have two one to many associations based on foreign keys

Code:

create table A( aId bigint not null primary key )
create table B( bId bigint not null primary key, AId bigint not null )
create table C( cId bigint not null primary key, BId bigint not null, otherField bigint not null )

i have set up my associations a unidirectional one-to-many association on a foreign key from tables A to B and B to C. I have Set in A which is a collection of B objects and a Set in B which is a collection of C objects

What I would like to accomplish in HQL is this:

Code:

select c.* 
from A a,B b,C c 
where a.aId = 1 and 
a.aId = b.aId and
b.bId = c.bId and 
c.otherField = 2

I tried lots of different queries using join fetch but I'm not able to succeed.

christian · **Posted:** Thu Aug 30, 2007 9:47 pm

mooritze wrote:

He was trying to restrict to event_id>3 that could be more than 1.

I don't see were the query is invalid

Of course it is invalid. You can't say "fetch the collection" and then "but only certain elements".

clam61 · **Joined:** Thu Aug 30, 2007 5:19 pm **Posts:** 5

ok, so i wrote a query and it returns the correct # of rows...so i think things seem to be working correctly.

i have something like this

Code:

from A a left join fetch ... left join fetch ... left join fetch ... where ...

so right now when i use Query.list(), hibernate returns a list of A objects.

However, what I really need is the corresponding fields in table C. How do I retrieve it?

clam61 wrote:

actually...looking at my own situation, it's not a many to many relationship. i have two one to many associations based on foreign keys

Code:

create table A( aId bigint not null primary key )
create table B( bId bigint not null primary key, AId bigint not null )
create table C( cId bigint not null primary key, BId bigint not null, otherField bigint not null )

i have set up my associations a unidirectional one-to-many association on a foreign key from tables A to B and B to C. I have Set in A which is a collection of B objects and a Set in B which is a collection of C objects

What I would like to accomplish in HQL is this:

Code:

select c.* 
from A a,B b,C c 
where a.aId = 1 and 
a.aId = b.aId and
b.bId = c.bId and 
c.otherField = 2

I tried lots of different queries using join fetch but I'm not able to succeed.

[/code]

mooritze · **Joined:** Tue Oct 10, 2006 3:23 am **Posts:** 33

christian wrote:

mooritze wrote:

He was trying to restrict to event_id>3 that could be more than 1.

I don't see were the query is invalid

Of course it is invalid. You can't say "fetch the collection" and then "but only certain elements".

Well not trying to argue here. If by invalid you mean 'makes no sense', I agree. I was kind of trying to guess what he wanted to do. However you could always say : retrieve person and fetch event for which event.id>1
I don't think I would run this but hib wouldn't complain...

clam61 · **Joined:** Thu Aug 30, 2007 5:19 pm **Posts:** 5

im new to HQL, so its a bit confusing right now.

looking at my post, two posts up, does my question make sense?

mooritze · **Joined:** Tue Oct 10, 2006 3:23 am **Posts:** 33

clam61 wrote:

im new to HQL, so its a bit confusing right now.

looking at my post, two posts up, does my question make sense?

I think it does and I would think that you could try what I suggested:

From C c where c.otherField=2 and c.id in( Select c1.id From A a left join a.b b left join b.c c1)

for what you want there is no point in fetching since you want C at the end

* rate if this helps *