NH -> Left outer Join ON (a.pk = b.fk and this_my_problem = value)

[Gabriel Mancini de Campos]

Hello to all, I am new to list, and sorry if my question is very
primary.

Guys, I found something very bad. see this:
if you need to create a channel find a model to generate a
SQL type: LEFT
OUTER JOIN ON (AND A.pk B.fk Some Field == Valo) <- this field is not
generated in the ON clause, but in WHERE, and I think this is a
limitation of NHibernate!
Does anyone have any idea how I can work around this problem?

[Fabio Maulo]

Limitation? I know some RDBMS where put the clause in the WHERE of an implicit JOIN or in the ON of an explicit JOIN don't cause any kind of problems.

BTW you can use an explicit join, in your query, if you want the ON clause for FKs.

2008/12/16 Gabriel Mancini de Campos <gabriel...@gmail.com>

--
Fabio Maulo

[Gabriel Mancini de Campos]

Hi Fabio

thanks for your answer, how to i put this explicit in my criteria ??
this is the SQL generate from NH.

see in left outer join SalaTemp my problem..


select
sala0_.Id as Id13_0_
, salatemp3_.Id as Id20_1_
, sala0_.Numero as Numero13_0_
, sala0_.Digitando as Digitando13_0_
, sala0_.Inicio as Inicio13_0_
, sala0_.Fim as Fim13_0_
, sala0_.Atendente_Id as Atendente6_13_0_
, sala0_.Supervisor_Id as Supervisor7_13_0_
, sala0_.Protocolo as Protocolo13_0_
, sala0_.ProtocoloInterno as Protocol9_13_0_
, sala0_.Comentario as Comentario13_0_
, sala0_.chat_id as chat11_13_0_
, sala0_.statusSala_id as statusSala12_13_0_
, sala0_.atendido_id as atendido13_13_0_
, sala0_.grauSatisfacao_id as grauSat14_13_0_
, salatemp3_.Autor as Autor20_1_
, salatemp3_.Frase as Frase20_1_
, salatemp3_.Hora as Hora20_1_
, salatemp3_.Reservado as Reservado20_1_
, salatemp3_.sala_id as sala6_20_1_
, salatemp3_.sala_id as sala6_0__
, salatemp3_.Id as Id0__
from
Sala sala0_
inner join Chat chat1_ on sala0_.chat_id=chat1_.Id
inner join StatusSala statussala2_ on
sala0_.statusSala_id=statussala2_.Id
left outer join SalaTemp salatemp3_ on sala0_.Id=salatemp3_.sala_id
[ and salatemp3_.hora < 63365027317266 ] <- i need put this in my
criteria
where
((statussala2_.Id=3 )or(statussala2_.Id=2 ))
and(chat1_.Id=742 )

On 16 dez, 15:33, "Fabio Maulo" <fabioma...@gmail.com> wrote:
> Limitation? I know some RDBMS where put the clause in the WHERE of an
> implicit JOIN or in the ON of an explicit JOIN don't cause any kind of

> problems.BTW you can use an explicit join, in your query, if you want the ON
> clause for FKs.
>
> 2008/12/16 Gabriel Mancini de Campos <gabriel.manc...@gmail.com>

[Gustavo Ringel]

if you really really need to have an exact sql-squery then write an sql query and use it with the tag <sql-query> or CreateSqlQuery.

This kind of things should be optimizations after stress testing, the kind of things you're doing here should not be part of a premature stage in development.

Gustavo.

[Fabio Maulo]

aahh now is more clear; you don't need only a ON clause with FK, you want add an AND clause to the JOIN ON.

It is an unsupported feature and we have an open issue about it 

http://jira.nhibernate.org/browse/NH-514

BTW, as Gustavo said, you can use an SQL in NH-style

http://nhforge.org/doc/nh/en/index.html#manipulatingdata-nativesql

until we don't have the HQL-AST working.

 

2008/12/16 Gabriel Mancini de Campos <gabriel...@gmail.com>

--
Fabio Maulo

[Gabriel Mancini de Campos]

certainly tried something

but when I make use of ILIST <Sala>
and I try to access the property should be zero, I think he's on base
and retrieves the data using the ID of the room.

ahh I'm using HR


try
{
iQuery = new SimpleQuery<Sala>(QueryLanguage.Sql,
@"
select
sal.*
from Sala as sal
join Chat as cha on (sal.chat_id =
cha.id)
join StatusSala as sta on
(sal.statusSala_id = sta.id)
left outer join SalaTemp as salTmp on
(sal.id = salTmp.sala_id and salTmp.Hora > ?)
where
(sta.Id = ? or sta.Id = ?)
and cha.Id = ?
order by sal.Atendente_Id, salTmp.Hora
", hora, StatusID.Ocupado, StatusID.Pausando,
chat.Id);

iQuery.AddSqlReturnDefinition(typeof (Sala), "sala");
}
catch (Exception ex)
{
throw ex;
}
return iQuery.Execute();

On 16 dez, 16:04, "Fabio Maulo" <fabioma...@gmail.com> wrote:
> aahh now is more clear; you don't need only a ON clause with FK, you want

> add an AND clause to the JOIN ON.It is an unsupported feature and we have an
> open issue about ithttp://jira.nhibernate.org/browse/NH-514
>
> BTW, as Gustavo said, you can use an SQL in NH-stylehttp://nhforge.org/doc/nh/en/index.html#manipulatingdata-nativesql

> until we don't have the HQL-AST working.
>

> 2008/12/16 Gabriel Mancini de Campos <gabriel.manc...@gmail.com>

[Fabio Maulo]

HR ?

2008/12/16 Gabriel Mancini de Campos <gabriel...@gmail.com>

--
Fabio Maulo

[Fabio Maulo]

please check your sintax...

the use of '{' '}' is fundamental to allow NH recognize it as OO reference and not a DDL 

2008/12/16 Gabriel Mancini de Campos <gabriel...@gmail.com>

--
Fabio Maulo

[Gabriel Mancini de Campos]

Sorry AR ActiveRecord

and how use { } ??

On 16 dez, 17:35, "Fabio Maulo" <fabioma...@gmail.com> wrote:
> please check your sintax...the use of '{' '}' is fundamental to allow NH

> recognize it as OO reference and not a DDL
>

> 2008/12/16 Gabriel Mancini de Campos <gabriel.manc...@gmail.com>

[Fabio Maulo]

2008/12/16 Gabriel Mancini de Campos <gabriel...@gmail.com>

Sorry AR ActiveRecord

and how use { } ??

 

http://nhforge.org/doc/nh/en/index.html#manipulatingdata-nativesql

It is the same link sent before.

--
Fabio Maulo

P.S. Se podría tambien escribir en criollo per en NH-Hispano

[Gabriel Mancini de Campos]

I think I know why is not working.

Even if the implementation of the query is correct, when I access the
property is related to the field of result that should come Null is
the AR access to DB and retrieves the data using the key, and populate
the Object.

Therefore I think has no solution.


On Dec 16, 6:26 pm, "Fabio Maulo" <fabioma...@gmail.com> wrote:
> 2008/12/16 Gabriel Mancini de Campos <gabriel.manc...@gmail.com>