>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 On 30 Nov 2009, at 10:15, Oskar Berggren wrote: > 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 <[email protected]>: >> 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 >> >> On Nov 30, 10:53 am, Diego Mijelshon <[email protected]> wrote: >>> 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 >>> >>> On Sun, Nov 29, 2009 at 20:03, Schotime <[email protected]> wrote: >>>> 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 <[email protected]> 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 <[email protected]> >>> >>>>>> 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 >>> >>>>>> -- >>> >>>>>> You received this message because you are subscribed to the Google >>>> Groups >>>>>> "nhusers" group. >>>>>> To post to this group, send email to [email protected]. >>>>>> To unsubscribe from this group, send email to >>>>>> [email protected]<nhusers%[email protected]> >>>> <nhusers%[email protected]<nhusers%[email protected]> >>> >>>>>> . >>>>>> For more options, visit this group at >>>>>> http://groups.google.com/group/nhusers?hl=en. >>> >>>>> -- >>>>> Fabio Maulo >>> >>>> -- >>> >>>> You received this message because you are subscribed to the Google Groups >>>> "nhusers" group. >>>> To post to this group, send email to [email protected]. >>>> To unsubscribe from this group, send email to >>>> [email protected]<nhusers%[email protected]> >>>> . >>>> For more options, visit this group at >>>> http://groups.google.com/group/nhusers?hl=en. >> >> -- >> >> You received this message because you are subscribed to the Google Groups >> "nhusers" group. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> For more options, visit this group at >> http://groups.google.com/group/nhusers?hl=en. >> >> >> > > -- > > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
