The property mapped to the formula is always 0. If I run the query
manually it's correct (2).
I see the disadvantage you're mentioning, but it is practically
irrelevant as the post count isn't displayed in the view adding
posts.
I already thought about an extra column for the post count, but I
don't really like it, since is redundant. I think I'll go for it if I
can't get the formula to work.
Btw: Thanks a lot for all your help!
On Oct 13, 2:45 pm, Hudson Akridge <hudson.akri...@gmail.com> wrote:
> If I'm not mistaken, the Formula is SQL based, not HQL, so HQL won't work in
> a formula.
> Everything looks correct, what do you get back in the model as a result of
> that formula? In your original email it was cut off before you said what the
> post count was always... If I get a chance tonight I'll try and recreate
> your scenario and see if it can't be determined what's up.
>
> One disadvantage to doing the post count this way that I can see: In the
> domain model, if you add another post count to the collection, before
> persisting, I don't believe your post count property will reflect that.
> You'd have to save the collection first back to the database, then retrieve
> the Topic object again in another session. At least, I'm pretty sure that's
> how it'll work and can find out for sure if I recreate the scenario.
>
> Another (third option) from even doing a read only formula, is to get the
> best of all worlds, and that is to map an int column, without it being a
> formula. It's just a standard int.
>
> Then create an Add/Remove Post method to manipulate the collection of Posts.
> Every time you Add or Remove a post, in your method, you increment/decrease
> the post count after manipulating the collection. Now that property is just
> a property to map back to the database, you can lazyload the collection, and
> you can get the count without having to load the entire list of posts, and
> the count will be updated during domain model manipulation in a session,
> before you persist back to the database.
>
> On Tue, Oct 13, 2009 at 6:15 AM, Schlaefisch
> <schlaefi...@googlemail.com>wrote:
>
>
>
>
>
> > Session.CreateQuery("select count(*) from Post p WHERE p.Topic.Id=" +
> > topic.Id)
>
> > works fine, but
>
> > mapping.Map(t => t.PostCount).Formula("(select count(*) from Post p
> > WHERE p.Topic.Id = Id)");
>
> > results in an SqlException (Invalid object name 'Post')
>
> > On Oct 13, 12:49 pm, Mikael Henriksson <mik...@zoolutions.se> wrote:
> > > I know exactly what you mean. It just feels wrong having to load the list
> > to
> > > get the count.Have you tried with HQL?
>
> > > On Tue, Oct 13, 2009 at 12:43 PM, Schlaefisch <
> > schlaefi...@googlemail.com>wrote:
>
> > > > No, the posts are not a child collection of the topic to avoid
> > > > performance issues. I don't want to load all posts of all topic just
> > > > to get the post count.
>
> > > > On Oct 12, 3:14 pm, Hudson Akridge <hudson.akri...@gmail.com> wrote:
> > > > > Are posts a child collection on your object being mapped? I ask,
> > because
> > > > > there might be an alternative to getting you that number. If you map
> > a
> > > > > Posts.Count() to a property, then map that with a read only accessor,
> > it
> > > > > should give you a similar solution.
>
> > > > > On Mon, Oct 12, 2009 at 6:41 AM, Paul Batum <paul.ba...@gmail.com>
> > > > wrote:
> > > > > > Can you post your domain object and the mapping override for it?
> > > > > > I figure something else has to be screwing up the result of the
> > > > formula.
>
> > > > > > On Mon, Oct 12, 2009 at 4:06 PM, Schlaefisch <
> > > > schlaefi...@googlemail.com>wrote:
>
> > > > > >> The exact query results in a syntax error, when I replace
> > "this_.Id =
> > > > > >> @p0;@p0 = 1" with "this_.Id = 1" the result is correct.
>
> > > > > >> But even when I use "SELECT 5" as formula, the PostCount is still
> > 0.
>
> > > > > >> On Oct 6, 1:16 pm, Paul Batum <paul.ba...@gmail.com> wrote:
> > > > > >> > Off the top of my head, I can't see anything wrong with your
> > > > mapping.
> > > > > >> I've
> > > > > >> > used formula columns with automapping for similar purposes
> > without
> > > > > >> problems.
> > > > > >> > Have you executed that generated query against the db and
> > confirmed
> > > > that
> > > > > >> it
> > > > > >> > returned the correct results?
>
> > > > > >> > On Fri, Oct 2, 2009 at 9:06 AM, Schlaefisch <
> > > > schlaefi...@googlemail.com
> > > > > >> >wrote:
>
> > > > > >> > > Update: The Console Output.
>
> > > > > >> > > Manual SQL:
> > > > > >> > > NHibernate: (SELECT COUNT(*) FROM forumPosts p where p.TopicID
> > =
> > > > 1)
> > > > > >> > > Mapping SQL:
> > > > > >> > > NHibernate: SELECT top 2 this_.Id as Id0_0_, this_.Title as
> > > > Title0_0_,
> > > > > >> > > this_.Created as Created0_0_, this_.DisplayPriority as
> > > > DisplayP4_0_0_,
> > > > > >> > > this_.ForumID as ForumID0_0_, (SELECT COUNT(*) FROM forumPosts
> > p
> > > > where
> > > > > >> > > p.TopicID = this_.Id) as formula0_0_ FROM forumTopics this_
> > WHERE
> > > > > >> > > this_.Id = @p0;@p0 = 1
>
> > > > > --
> > > > > - Hudsonhttp://www.bestguesstheory.comhttp://
> > twitter.com/HudsonAkridge
>
> --
> - Hudsonhttp://www.bestguesstheory.comhttp://twitter.com/HudsonAkridge
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Fluent NHibernate" group.
To post to this group, send email to fluent-nhibernate@googlegroups.com
To unsubscribe from this group, send email to
fluent-nhibernate+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/fluent-nhibernate?hl=en
-~----------~----~----~----~------~----~------~--~---
