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.
