Fetching children by criteria

[Phillip]

Given the example of a Post with many Comments.

Comments has a rating against it.

If I wanted to query for a Post + all Comments with a rating greater
than 3, I can generate the correct query like:

var result = session.CreateCriteria(typeof(Post))
.Add(Restrictions.Eq("id", id))
.CreateCriteria("Comments", "c")
.Add(Restrictions.Gt("c.Rating", 5))
.SetResultTransformer(new
RootEntityResultTransformer())
.List<Post>()[0];

or

var result = session.QueryOver<Post>()
.Where(x => x.Id == id)
.JoinQueryOver(x => x.Comments, () => comment)
.Where(x => x.Rating > 3)
.SingleOrDefault();

These produce SQL like so:

SELECT this_.Id as Id3_1_,
this_.Title as Title3_1_,
comment1_.Id as Id2_0_,
comment1_.Rating as Rating2_0_,
comment1_.PostId as PostId2_0_
FROM [Post] this_
inner join [Comment] comment1_
on this_.Id = comment1_.PostId
WHERE this_.Id = 'ca6c944d-ca94-49e2-a79c-9f2001687076' /* @p0 */
and comment1_.Rating > 5 /* @p1 */

However, the results from Comment are not added to Post, so when
looking at the Comment's property, it lazy-loads the Comments
resulting in ALL comments to be loaded.

Now I realize that I could use a Filter on the mapping to achieve
this. However that limits me from doing more advanced queries on
'Comment' such as 'WHERE Status IN (1, 3, 5)'

Is this a bug? Or is the only way to achieve this sort of
functionality by separately querying for Comments.

[brendan richards]

From your exmaple you already know the post id so you gain nothing by
effectively doing a complex query incolving both posts and comments.
A separate query for comments is definately the way I'd approach this.

Queries generally return a list of objects of a particular type
matching the query criteria - not a whole pre-filtered object graphs
setup to match the details from the query criteria.
Once you access an object returned by a query, you'll get whatever the
mapping for that object is setup to give you. As you say, you could
use a filter on the mapping but that filter would be applied every
time you load a post and then access its comments.

Hope this helps,

Brendan

[Phillip]

I understand what you're saying. But I still think it should be able
to filter the graph, it generates the correct SQL, it should just
create the object graph with what it has :(

Phill

[Mohamed Meligy]

Fetching doesn't seem to work with inner joins...

So, this will work:

Comment comment = null;
var posts = session.QueryOver<Post>()
    .Fetch(p => p.Comments).Eager
    .Left.JoinAlias(p => p.Comments, () => comment)
    .Where(() => comment.Rating > 5)
    .List();

while this will not:

Comment comment = null;
var posts = session.QueryOver<Post>()
    .Fetch(p => p.Comments).Eager
    .JoinAlias(p => p.Comments, () => comment)
    .Where(() => comment.Rating > 5)
    .List();

Hopefully this helps.

Regards,

 

Mohamed Meligy
Readify | Senior Developer

M:+61 451 835006 | W: readify.net

    

--
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.