This is without running the queries, but the following should be about what you need to get the list if items already mapped to a user via rating.
select distinct i from User u join u.UserRatings r join r.Item i where u = :user order by r. You could get the remainder of the items by running the above as a subquery in a not exists type query. However, that won't get you your mock zero rating entities for those items, and I'm not sure you want to spin those up for this, and anyway, it would be kind of ... inaccurate unless you're planning to save things. I have the feeling you're planning on adding in all these zero-rated items for a use like reporting or screen display, not for altering the records themselves (at least not at the time of query). In that case, you might want to look creating some lightweight DTOs on the fly. here's how the first query might be using a dto: select distinct New ItemAndRatingDTO(i.Name, r.RatingValue) from User u join u.UserRatings r join r.Item i where u = :user order by r and here's a second one (probably a way to write nicer, but maybe this will give you some ideas): select New ItemAndRatingDTO(i.Name, 0) from Item i where not exists (select ii from User u join u.UserRatings r join r.Item ii where i = ii and u = :user) One this with this is you'll have to let the NH know this DTO class exists. Here's one way: make an hbm file just like your "normal"map files, maybe call this one ImportClasses.htm.xml Here would be the contents: <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> <import class="Your.Domain.ItemAndRatingDTO, Your.Domain"/> <import class="Your.Domain.SomeOtherDTO, Your.Domain"/> </hibernate-mapping> The queries will come back with your DTOs, so it's worth noting that your List call on your query will be typed to your DTO, like this: .List(Of ItemAndRatingDTO)() The DTO class itself (yes, you'd need to write the class)would be very plain getters, setters if you want, and a constructor matching the signature in the HQL queries. Done! On Mon, Nov 16, 2009 at 9:20 AM, Sosh <[email protected]> wrote: > Hi, > > I'm still v new to NHibernate, and many concepts are still not at all > natural to me. I have a particular situation which is causing me much > frustration. I have 3 entities: > > User, Item, and UserRating > > UserRating holds a reference to both User and Item, and contains an > integter 'rating'; > > Item Holds a collection of UserRatings > > User Holds a collection of UserRatings > > I currently struggling to understand the best way of query NHibernate > so that it will bring me, for one particular User, a list of all item > objects, ordered by the userrating 'rating'. There may be items that > the user has not scored (hence there is no UserRating linking them), > but I would like these to be included with the 0 ratings. > > How do I best go about this? > > I tried things like: "from Item i left join i.UserRatings r with > r.User.UserId = :userId" for a start, but this was bringing back an > array with items in the first element and userratings in the second, > which is not exactly what I had in mind. > > At the core of the problem is how best to deal with results that > relate only to the current user, and ignore everything else - perhaps > there is a better way to do this. > > If you need more information please let me know - thanks in advance > for your help > > S > > -- > > 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=. > > > -- 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=.
