with clause on a left join fetch no allowed...

[Schotime]

hey,

Firstly i am new to nhibernate, but consider myself pretty experienced
in writing SQL queries.

i have two tables. one called records and one called userrecords.
records has the relationship that a record contains multiple
userrecords.

Will explain with an example.
if there are 10 records and 10 users then you can expect to see 100
user records. One for each combination. A unique index exists on the
userrecords table constraining this.

So if you were to right the query in sql it would be..assuming you are
after the records and user records for user 2.

select * from records r
left outer join userrecords ur on r.recordid = ur.recordid and
ur.userid = 2

this will join the tables correctly so that it will still give you one
user record for each record. If you included the userid as a filter
and there was only 5 user records for user 2 then you would only get 5
rows returned and this would not be correct.

Therefore i tried to right this in hql.

from records r
left join fetch r.userrecords with r.userid = :userid

However I was given the error.

Semantic Exception -> with-clause not allowed on fetched associations;
use filters

Also if i don't do the fetch the query works, however when it does the
lazy fetch it doesn't include the userid so the results are incorrect.

Is my model not correct or should I be allowed to do this?
Open to suggestions.

Cheers,

Adam

[Schotime]

hey,

i have two tables. one called records and one called userrecords.
records has the relationship that a record contains multiple
userrecords.

table records:
recordid int identity,
details varchar

table userrecords
userrecordid int identity,
recordid int,
userid int,
extradetails varchar

Will explain with an example.
if there are 10 records and 10 users then you can expect to see 100
user records. One for each combination. A unique index exists on the
userrecords table constraining this.


So if you were to right the query in sql it would be..assuming you are
after the records and user records for user 2.

select * from records r
left outer join userrecords ur on r.recordid = ur.recordid and
ur.userid = 2

this will join the tables correctly so that it will still give you one
user record for each record. If you included the userid as a filter
and there was only 5 user records for user 2 then you would only get 5
rows returned and this would not be correct.

Therefore i tried to right this in hql.

from records r
left join fetch r.userrecords with r.userid = :userid

However I was given the error.

Semantic exception in sqlhqlwalker.cs "with-clause not allowed on
fetched associations; use filters"

If i remove the fetch the query works but then the lazy queries don't
include the userid so the data returned is incorrect.

I'm only new to nhibernate but consider myself fairly competent with
sql. So if there is a better way to structure my model etc, or I
should be doing it another way then I'm open to suggestions too. If
you need more info let me know.

Many thanks,
Adam

[cws]

Hi!

Seems it not possible to use the with keyword on fetched associations,
obvisouly :).

See
http://nhforge.org/doc/nh/en/index.html#manipulatingdata-filtering
for using with lazy:

ICollection userrecords = session.CreateFilter(
result.userreocrds, "where this.userid = :userid").setInt
("userid", userId)
);

See http://nhforge.org/doc/nh/en/index.html#filters
for filter when join fetching.

[Fabio Maulo]

classes, domain objects, entities... No Table please.

You can query it without use the "with" clause.

Think on your domain at first, not in its persistent representation.

2009/11/28 Schotime <adamsc...@gmail.com>

--

You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

--
Fabio Maulo

[Schotime]

Ok. I understand, but not sure how to proceed.

I have a User class, Record class and UserRecord class.

Should there be a property on the User class called UserRecords then
that would have a property on it called Record and get back to the
data in reverse?
Any help would be greatly appreciated.

Just trying to get use to it after being so use to querying sql.

Cheers.
Adam

On Nov 29, 10:52 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> classes, domain objects, entities... No Table please.
> You can query it without use the "with" clause.
>
> Think on your domain at first, not in its persistent representation.
>

> 2009/11/28 Schotime <adamschro...@gmail.com>

> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com>
> > .

[Diego Mijelshon]

If you have something like this:
class User
{
    public virtual IList<UserRecord> Records {get;set;}
}

And map that using, for example, a bag:
<class name="User" ...>
    ...
    <bag name="Records">
        <key column="userid">
        <one-to-many class="UserRecord"/>
    </bag>
</class>

(you also need to create the mapping for the UserRecord and Record classes, using <bag> or <set> on the one-to-many side and <many-to-one> on the... many to one side :-))

Then you can just get the user, and the collection will be loaded for you (lazily by default, but you can override it):

var user = session.Get<User>(2);
Assert.AreEqual(10, user.Records.Count);

As Fabio said, FORGET about the tables and the SQL. Just design you domain classes as they are logically related (one user has many records, a record belongs to a user (many-to-one), etc)

Then you can map that to the existing relational model, or even let NHibernate generate the model from your mapping.

   Diego

To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

[Schotime]

Thanks, but I still run into the same issue.

If i do as you say deigo then to get all the reports and the user's
report

select r from User u
join fetch u.UserReports ur with u.userid = :userid
right join fetch ur.Report r

so for each report, there is a one to many to UserReports
with a maximum of one userreport per report per user.

I need to do the right join to get all the reports regardless of
whether they have a userreport for it.
The fetch needs to be there because all the reports need to be listed
regardless of whether they have a userreport for it or not.

Run into the same issue, but in reverse.

Any other suggestions?

Cheers,
Adam

> > <nhusers%2Bunsu...@googlegroups.com<nhusers%252Buns...@googlegroups.com>

[Oskar Berggren]

One option is two queries:

"from Report r"

"from User u join fetch u.UserReports ur"

where u.userid = :userid"

No need to specify join condition everywhere with hql - your mappings
should handle that. The "join fetch" is purely an optimization,
without it the UserReports collection would be fetched when needed
instead.

Do these within one ISession and you will still get only one object
instance for each Report. Perhaps you can combine these with
multiquery as an optimization to avoid an extra round trip to DB.

/Oskar


2009/11/30 Schotime <adamsc...@gmail.com>:

> To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

[Steve Strong]

From what you've described, I'd agree with Oskar.  You want "all the reports and the user's report" - that sounds like two different queries to me.

Steve Strong

Twitter

Blog

NHibernate Professional Support