Hibernate Community • View topic - outer join query help

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

outer join query help

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

jedilowe

Post subject: outer join query help

Posted: Fri Dec 05, 2003 12:52 am

Newbie

Joined: Fri Dec 05, 2003 12:42 am
Posts: 3

I have a table of data, and another table that defines which users have permissions to see the rows in the table. Basically what I want is for the events, if no permissions are setup it returns to all users. If it has any permissions setup, it should check the user's permission list against the permission resources in the IncludedResource table...

Table Event
-------------------
id | .....

Table Included resource (used for security access)
------------------------------------------------------------------------
toObject (points to event) | fromResource (points to permission)

I have the following HQL query:

Code:

SELECT event FROM d131.calendar.event.Event as event left outer join event.includedResources includedresource where event.id != includedresource.toObject or (event.id = includedresource.toObject and event.id = includedresource.toObject and includedresource.fromResource in (?, ?)

I want it to look like the SQL:

Code:

SELECT events.* FROM events right outer JOIN includedresource on events.id where events.id != includedresource.objectid or events.id = includedresource.objectid  and includedresource.resourceId in (98, 102)

Basically the main difference is after the join in the on clause, the HCL automatically make the on clause be the id links (

Code:

includedResouce.objectId = event.id

) and for me to get the results I want, I must have simply

Code:

event.id

Any ideas? It works well for checking to see that users can only see events they have permission to see, but it will not return events with no permission setup!

Thanks

Tony

Top

jedilowe

Post subject: Updated!

Posted: Fri Dec 05, 2003 2:00 am

Newbie

Joined: Fri Dec 05, 2003 12:42 am
Posts: 3

I realized after much circling I had extra fields and a simple way to make the HQL work.

Code:

SELECT event FROM d131.calendar.event.Event as event left outer join event.includedResources includedresource where includedresource is null or includedresource.fromResource in (?, ?)

I am not all that SQL savy, so I may have missed something obvious here, but my style is more try and see...

My issue now, is making the above statement into a Criteria based query. I have the framework of it, except for the null check...

Code:

includedresource is null

I have the line

Code:

Criterion emptyCheck = Expression.isNull("includedResources");

but when I look at the ouput window it translates into

Code:

and this.id is null

where I would think it would do something more like

Code:

(included1_.id is null )

like the HQL query above

Thanks for giving me a place to think!

Tony

Top

Page 1 of 1

[ 2 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum