mapping via <join /> and where clause (bug in NHibernate?)

[Krzysztof Koźmic]

So I have a one-to-many tables in database that looks like the following
(I'm on NHibernate 3.1):

blog
id
title
author

comments
id
blog_Id
languageid
comment

and I want to map it to a *single* class with *single* comment property
that maps to text column in comments table for current language.

Basically the idea is identical to one ayende had few years back:
http://ayende.com/Blog/archive/2006/12/26/localizingnhibernatecontextualparameters.aspx

The difference is I try to accomplish this without subselect but using
<join /> instead (mostly because the table with translations has more
columns than just the text that I may need to include in my joined class).

So I created a solution for that and I'm also using filter to pass
language id to queries. I also have a noop property in my mapping for
the languageid as well as where clause in my mapping that does the
filtering.

And here's the SQL that NHibernate generates:

SELECT this_.Id as Id0_0_,
this_.Author as Author0_0_,
this_.Title as Title0_0_,
this_1_.Comment as Comment1_0_,
this_1_.LanguageId as LanguageId1_0_
FROM Blog this_
inner join Comment this_1_
on this_.Id = this_1_.Blog_id
WHERE (this_.LanguageId = 2 /* @p0 */)

The SQL is invalid as the where should be on this_1_.LanguageId as the
value comes from the joined table, not the main one.

Also the LanguageId column is mapped as access="noop" which TTBOMK
should mean it won't be queried for so I'm surprised to see NHibernate
is trying to select it as well. To me it is just wasting bandwith.

So I have two questions now.

1. How can I accomplish what I'm trying to get to.
2. Are those issues I mentioned (invalid where clause and ignoring noop
access) bugs in NHibernate or am I looking at it from the wrong angle?

Reproduction demo app (along with database dump) available here if
someone wants to play with it: http://ge.tt/5xoiqYY

cheers,
Krzysztof

[Fabio Maulo]

If you use that solution, please have a look to WARNING.

2011/3/25 Krzysztof Koźmic <krzyszto...@gmail.com>

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

--
Fabio Maulo

[José F. Romaniello]

It is not the same, he is using a filter inside the "where" attribute
of the <class> to filter by a column in a <join>. It is different to
the formula of Ayende.... But im not sure if it might work

2011/3/25, Fabio Maulo <fabio...@gmail.com>:

--
Enviado desde mi dispositivo móvil

[Anne Epstein]

The <join> mapping is problematic in that various features in
NHibernate will assume that fields, keys, etc are in the primary table
that may actually be in the secondary table-resulting in errors.
There are a number of bugs in jira related to this kind of problem
with the <join> element. At this point, until there is a real
solution that consistently makes NHibernate deal with this
relationship properly in all NHibernate features (and I wouldn't hold
my breath), I'd avoid this mapping strategy unless you absolutely have
to- you'll almost certainly hit some weirdness if you try anything
complex/interesting.

[xtoff]

@Anne - I'm open to ideas how to solve the underlying problem (having
multilingual app that in C# model is single-language) differently.
That's more important to me short term than having the eforementioned
bug fixed.

@Fabio - WRT the warning, I'm not sure what Ayende meant. I tried his
solution and it works on 3.1. That's actually my fallback solution for
now.

On Mar 26, 5:28 am, Anne Epstein <aje...@gmail.com> wrote:
> The <join> mapping is problematic in that various features in
> NHibernate will assume that fields, keys, etc are in the primary table
> that may actually be in the secondary table-resulting in errors.
> There are a number of bugs in jira related to this kind of problem
> with the <join> element.  At this point, until there is a real
> solution that consistently makes NHibernate deal with this
> relationship properly in all NHibernate features (and I wouldn't hold
> my breath), I'd avoid this mapping strategy unless you absolutely have
> to- you'll almost certainly hit some weirdness if you try anything
> complex/interesting.
>
> On Fri, Mar 25, 2011 at 1:25 PM, José F. Romaniello
>
>
>
>
>
>
>
> <jfromanie...@gmail.com> wrote:
> > It is not the same, he is using a filter inside the "where" attribute
> > of the <class> to filter by a column in a <join>. It is different to
> > the formula of Ayende.... But im not sure if it might work
>

> > 2011/3/25, Fabio Maulo <fabioma...@gmail.com>:

> >> If you use that solution, please have a look to WARNING.
>

> >> 2011/3/25 Krzysztof Koźmic <krzysztof.koz...@gmail.com>

>
> >>> So I have a one-to-many tables in database that looks like the following
> >>> (I'm on NHibernate 3.1):
>
> >>> blog
> >>>    id
> >>>    title
> >>>    author
>
> >>> comments
> >>>    id
> >>>    blog_Id
> >>>    languageid
> >>>    comment
>
> >>> and I want to map it to a *single* class with *single* comment property
> >>> that maps to text column in comments table for current language.
>
> >>> Basically the idea is identical to one ayende had few years back:

> >>>http://ayende.com/Blog/archive/2006/12/26/localizingnhibernatecontext...

[Mohamed Meligy]

The problem with the above SQL actually is that you 

1- Cannot add 'where' clause to the <join /> mapping tag

2- Cannot alternatively refer to the <join /> part in the 'where' of the <class /> itself. There is no alias or something that you can use inside that 'where' to refeer to the <join /> part.

I think this is the case even independent from filters completely. Regardless of them, you still cannot have a 'where' based on columns in the <join /> part (or can you?).

 

Mohamed Meligy
Readify | Senior Developer

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

    

[Krzysztof Koźmic]

Mohamed,

Correct - as Anne pointed out, it seems NHibernate just doesn't handle joined mappings correctly and seems to always (or in some cases) assume every reference points to "primary" table.

[Fabio Maulo]

The log say:

ERROR Configuration:0 - filter-def for filter named 'blogcomment' was never used to filter classes nor collections.

This may result in unexpected behavior during queries

Starting from that we can talk about a solution.

2011/3/26 Krzysztof Koźmic <krzyszto...@gmail.com>

--
Fabio Maulo

[Fabio Maulo]

Krzys,

How you are adding those comments in the DB ?

2011/3/26 Krzysztof Koźmic <krzyszto...@gmail.com>

--
Fabio Maulo

[José F. Romaniello]

he told me that he want a read-only model...

2011/3/26 Fabio Maulo <fabio...@gmail.com>

[Fabio Maulo]

ah... I was confused by that: mutable="true"

2011/3/26 José F. Romaniello <jfroma...@gmail.com>

--
Fabio Maulo

[Fabio Maulo]

  <class name="Blog" mutable="false">

    <subselect>

      SELECT Blog.Id, Blog.Author, Blog.Title, Comment.Comment

      FROM Blog INNER JOIN Comment ON Blog.Id = Comment.Blog_id

      WHERE Comment.LanguageId = :blogcomment.languageId

    </subselect>

    <id name="Id">

      <generator class="assigned" />

    </id>

    <property name="Author" />

    <property name="Title" />

    <property name="Comment" />

  </class>

  <filter-def name="blogcomment">

    <filter-param name="languageId" type="int"/>

  </filter-def>

2011/3/25 Krzysztof Koźmic <krzyszto...@gmail.com>

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

--
Fabio Maulo

[Fabio Maulo]

Anne,

can you point me to the list of actual open issues, about the problems you are talking about, in our JIRA ?

where no available in the ticket, can you provide a failing test ?

Thanks.

[José F. Romaniello]

wow! didn't know about <subselect /> ... I learn something new about NH every day :)

2011/3/26 Fabio Maulo <fabio...@gmail.com>

[Fabio Maulo]

That log ERROR was an exception. Ayende have changed it to log ERROR with the problems it may cause.

That mean that who will use that solution have to be aware and can't report a BUG when that solution does not work.

[Fabio Maulo]

it is new in NH3.2

2011/3/26 José F. Romaniello <jfroma...@gmail.com>

wow! didn't know about <subselect /> ... I learn something new about NH every day :)

--
Fabio Maulo

[José F. Romaniello]

awesome

2011/3/26 Fabio Maulo <fabio...@gmail.com>

[Fabio Maulo]

bah?!? perhaps....

The matter is that you can do the same with NH3.1 with an AuxiliaryDataBaseObject creating a view and then mapping the same class to the view.

[José F. Romaniello]

Unless you work with a EliotNess-ed db schema ,
"awesome" was exaggerated though

2011/3/26 Fabio Maulo <fabio...@gmail.com>

[Anne Epstein]

Fabio,
There is some chance this is fixed in NHibernate 3.X as I haven't
needed to use the <join> feature in a while, but Krzysztof's issue
sounded like a sister issue to one I ran across, researched and
reported, and that bug report is still unresolved:
http://216.121.112.228/browse/NH-1747

Also, another related issue that I'd made note of was this:
http://216.121.112.228/browse/NH-2009
which was patched and closed, but with a patch that assumes all keys
are either in the primary table OR the join table, but doesn't really
handle a mix. The patch there is an improvement for that case, but
doesn't cover all situations.

I've seen a number of other weird issues like Krzysztof's show up on
the mailing list that were related to wrong-table FK errors on join.
Unfortunately I didn't bookmark them, but I've seen a few-enough that
I'd think twice about putting anything but plain value properties in
the secondary table of a join.

Anne

[Fabio Maulo]

mix ?

[Fabio Maulo]

Issues in this list are not covered by the team.

[Fabio Maulo]

The usage of <join> mean a split of the root-entity in more than one table.

It was introduced only to give support to legacy-db that was designed without think in ORM; that is the real issue.

[Krzysztof Kozmic]

Hey all,

@Fabio - the translations are currently inserter as part of database
setup script (not generated by NHibernate).

We're still on NHibernate 3.1 so the solution you mentioned is added
in 3.2 can't be used. Also the way I reported it I only showed you a
part of the picture. Reality is that you may have graph like this
(entities with * next to their names have associated translations
table):

foo*
many-to-one: bar*
many-to-one: baz*

one-to-many: aaa*
many-to-one: bbb*
many-to-one: ccc

So you may have to fetch multiple types which makes the matter a bit
more complex.
Also since it's oracle, I'd prefer to avoid having too much SQL in my
mappings, that's why I like the solution with <join /> and where
clasuse in mapping.

regarding the error generated by NHibernate - well it does filter the
class, just not entire class, as it squashes a one-to-many
relationship between the <join />ed tables to a one-to-one. I know
that's usually not intended use, but can you see any real problem with
it if I use it that way?


Can you think of any other solution that would work in 3.1?

Thanks,
Krzysztof

On Mar 27, 6:09 am, Fabio Maulo <fabioma...@gmail.com> wrote:
> The usage of <join> mean a split of the root-entity in more than one table.
> It was introduced only to give support to legacy-db that was designed
> without think in ORM; that is the real issue.
>
>
>
>
>
>
>
>
>
> On Sat, Mar 26, 2011 at 4:03 PM, Anne Epstein <aje...@gmail.com> wrote:
> > Fabio,
> > There is some chance this is fixed in NHibernate 3.X as I haven't
> > needed to use the <join> feature in a while, but Krzysztof's issue
> > sounded like a sister issue to one I ran across, researched and
> > reported, and that bug report is still unresolved:
> >http://216.121.112.228/browse/NH-1747
>
> > Also, another related issue that I'd made note of was this:
> >http://216.121.112.228/browse/NH-2009
> > which was patched and closed, but with a patch that assumes all keys
> > are either in the primary table OR the join table, but doesn't really
> > handle a mix. The patch there is an improvement for that case, but
> > doesn't cover all situations.
>
> > I've seen a number of other weird issues like Krzysztof's show up on
> > the mailing list that were related to wrong-table FK errors on join.
> > Unfortunately I didn't bookmark them, but I've seen a few-enough that
> > I'd think twice about putting anything but plain value properties in
> > the secondary table of a join.
>
> > Anne
>

> > On Sat, Mar 26, 2011 at 1:07 PM, Fabio Maulo <fabioma...@gmail.com> wrote:
> > > Anne,
> > > can you point me to the list of actual open issues, about the problems
> > you
> > > are talking about, in our JIRA ?
> > > where no available in the ticket, can you provide a failing test ?
> > > Thanks.
>
> > > On Fri, Mar 25, 2011 at 4:28 PM, Anne Epstein <aje...@gmail.com> wrote:
>
> > >> The <join> mapping is problematic in that various features in
> > >> NHibernate will assume that fields, keys, etc are in the primary table
> > >> that may actually be in the secondary table-resulting in errors.
> > >> There are a number of bugs in jira related to this kind of problem
> > >> with the <join> element.  At this point, until there is a real
> > >> solution that consistently makes NHibernate deal with this
> > >> relationship properly in all NHibernate features (and I wouldn't hold
> > >> my breath), I'd avoid this mapping strategy unless you absolutely have
> > >> to- you'll almost certainly hit some weirdness if you try anything
> > >> complex/interesting.
>
> > >> On Fri, Mar 25, 2011 at 1:25 PM, José F. Romaniello
> > >> <jfromanie...@gmail.com> wrote:
> > >> > It is not the same, he is using a filter inside the "where" attribute
> > >> > of the <class> to filter by a column in a <join>. It is different to
> > >> > the formula of Ayende.... But im not sure if it might work
>

> > >> > 2011/3/25, Fabio Maulo <fabioma...@gmail.com>:

> > >> >> If you use that solution, please have a look to WARNING.
>

> > >> >> 2011/3/25 Krzysztof Koźmic <krzysztof.koz...@gmail.com>

>
> > >> >>> So I have a one-to-many tables in database that looks like the
> > >> >>> following
> > >> >>> (I'm on NHibernate 3.1):
>
> > >> >>> blog
> > >> >>>    id
> > >> >>>    title
> > >> >>>    author
>
> > >> >>> comments
> > >> >>>    id
> > >> >>>    blog_Id
> > >> >>>    languageid
> > >> >>>    comment
>
> > >> >>> and I want to map it to a *single* class with *single* comment
> > >> >>> property
> > >> >>> that maps to text column in comments table for current language.
>
> > >> >>> Basically the idea is identical to one ayende had few years back:
>

> >http://ayende.com/Blog/archive/2006/12/26/localizingnhibernatecontext...

[Fabio Maulo]

a view

[Krzysztof Kozmic]

View sounds like a good idea if I was only reading.

But for every Foo having Foo_translations table I need to be able to
add and update Foos.

First of all let me stress I'm on top of Oracle here, and I don't know
how well updateable views are supported there.
Moreover updating via view seems very dirty to begin with.

Honestly, the more I think about it the more I feel <join /> mapping
with a filter is the way to go here.

> ...
>
> read more >>

[Krzysztof Kozmic]

...when the <join /> bug is fixed obviuosly :)

> ...
>
> read more >>

[Fabio Maulo]

Somebody said that you need a read-only model, if it is not the case, can you please answer my question ? (see in the thread)

Thanks.

2011/3/27 Krzysztof Kozmic <krzyszto...@gmail.com>

--
Fabio Maulo

[José F. Romaniello]

2011/3/28 Fabio Maulo <fabio...@gmail.com>

Somebody said that you need a read-only model,

I said so :( ... maybe i missunderstand he.

[Krzysztof Kozmic]

@Fabio

What I said to @Jose was not that entire model has to be read only,
but just the part that lives in the translations table.

The things that live in primary table are going to be read and
written.

The things that live in translations table are going to be read only.
Or more precisely there is going to be a different model for them that
maps one to one with the table so it's not going to be a problem to
work with them. So for this scenario I want to squash them to just few
properties (so that they behave like a component) and I want to have a
different model mapping to the translation table elsewhere which will
fully expose what is in it.

Hope that makes it clearer.



On Mar 29, 6:15 am, José F. Romaniello <jfromanie...@gmail.com> wrote:
> 2011/3/28 Fabio Maulo <fabioma...@gmail.com>

[Ricardo Peres]

Fabio,

Is there a JIRA for <subselect/>? If not, where can we see more
information?

Thanks,

RP

> ...
>
> read more >>

[Krzysztof Kozmic]

So it seems the discussion has stalled.

I ended up using the solution Ayende showed on his blog and while it's
less than ideal it gets the job done for now.

About join and the two issues related to it should I log them to the
JIRA?

Any chance this gets fixed in 3.2 timeframe?

cheers,
Krzysztof

> > > > > > To unsubscribe from this group, send...
>
> read more >>

[David McClelland]

@Krzysztof - any updates on this?  I would love to be able to apply a <filter> inside the <join> element so I don't have to write the SQL for a <subselect> by hand.

- David McClelland

[icam...@gmail.com]

Just for completion's sake, I wanted to link to a pull request I created a long time ago for this. The Jira issue is here: https://nhibernate.jira.com/browse/NH-2861. The pull request is here: https://github.com/nhibernate/nhibernate-core/pull/10. People having issues with this should push for the patch to be merged.