CODEWITHSUNDEEP
c#oracle-databasenhibernatefluent-nhibernate
Dr_klo
Dr_klo

Reputation: 469

Fluent NHibernate Join mapping to Joined table

I have entity like:

class Doc 
{
public virtual int Id {get;set;}
public virtual int Code {get;set;}
}

where Id is identity.
And Code is placed in another table. to get Code I use next SQL statement:

Select Distinct A.CODE from DOCLIST D Left Join DOCSLINKS  DL On DL.TODOC_ID=D.DOC_ID
Left Join ARTICLES A On DL.ART_ID=A.ART_ID  Where D.DOC_ID=*ourid*

where ourid is Id of our entity

The map of Doc class is: 

public class DocMap : ClassMap<Doc>
    {
        public DocMap()
        {
            Table("DOCLIST");
            Id(x =>x.Id).Column("DOC_ID").GeneratedBy.Custom<NHibernate.Id.TriggerIdentityGenerator>(); 

            //HOW TO MAP CODE?
        }
     }

I try to use Formula: 

Map(x => x.Code).Formula("(Select Distinct A.CODE from DOCLIST D Left Join DOCSLINKS  DL On DL.TODOC_ID=D.DOC_ID
Left Join ARTICLES A On DL.ART_ID=A.ART_ID  Where D.DOC_ID=Id)").Not.Update();

But I handle an exception ORA-00904 : "DOC0_"."ID": invalid identifier with message: 

could not load an entity: [Doc#1562][SQL: SELECT doc0_.DOC_ID as DOC1_3_0_, (Select Distinct A.OKP_CODE from DOCLIST D  Left join  DOCSLINKS DL ON DL.TODOC_ID=D.DOC_ID Left join ARTICLES A ON DL.ART_ID=A.ART_ID  Where D.DOC_ID=doc0_.Id) as formula0_0_ FROM DOCLIST doc0_ WHERE doc0_.DOC_ID=?]

Can anybody help me to map Code?

Upvotes: 2

Views: 433

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

I guess that we should change the select. It should not be using the current table DOCLIST (which is alreaedy used for mapping the root class Doc .. Table("DOCLIST"))

// current select
(SELECT Distinct A.CODE 
 FROM DOCLIST D 
 Left Join DOCSLINKS DL On DL.TODOC_ID=D.DOC_ID
 Left Join ARTICLES  A  On DL.ART_ID=A.ART_ID  
 Where D.DOC_ID=Id)

This should do the job (or with some adjustments)

(SELECT Distinct A.CODE 
 FROM DOCSLINKS DL
 Left Join ARTICLES  A  On DL.ART_ID=A.ART_ID  
 Where DL.TODOC_ID = DOC_ID) // DOC_ID should be column on current table

We are already having access to table DOCLIST (the current table). We just have to SELECT from other tables, while using our ID in the wwhere clause to filter DOCSLINKS:

Where DL.TODOC_ID = DOC_ID // DOC_ID will be used from current table

Upvotes: 1

Related Questions