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.
