and the HQL or Criteria is ? On Fri, Jun 11, 2010 at 11:45 AM, Steve <[email protected]> wrote:
> I have a standrd Contact table which contains names, addresses, > emails, telephones etc. It's a pretty big, general purpose table that > get's used to store loads of different entities: > CREATE TABLE Contact > ( > Contact_Ref INTEGER > PRIMARY KEY CLUSTERED, > Title INTEGER, > Initials NVARCHAR(4), > Surname NVARCHAR(30), > Etc... > > Each Contact row has a ContactLink row which is basically links a > specific type of Contact, such as a Sales Rep to another entity: > > CREATE TABLE ContactLink > ( > cnta_det_ref INTEGER PRIMARY KEY CLUSTERED, > contact_ref INTEGER NOT NULL, <--- Foreign key to link > to the > Contact table > reltype INTEGER NULL <-- Type of entity e.g. > sales rep, > employer etc > CONSTRAINT FK_ContactDt_ContactHd > FOREIGN KEY (Contact_Ref) > REFERENCES Contact (Contact_Ref) > ); > > Basically the problem is that the Contact table is very wide so to > make searching more efficient I have a ContactInfoDAO which is a lite > object with just a few attibutes. To restrict results to retrieve only > sales reps I have to include the link to the ContactLink table as > well. > > My problem is that when I check the sql, I am still bringing back the > *entire* contact row. Here are the HBMs: > > The ContactInfo: > <?xml version="1.0" encoding="utf-8" ?> > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" > namespace="Coldharbour.Framework.Model" > assembly="Coldharbour.Framework.Model"> > <class name="Coldharbour.Framework.Model.ConactInfoDAO, > Coldharbour.Framework.Model" table="Contact" lazy="false"> > <id name="ContactRef" column="Contact_Ref"> > <generator class="assigned" /> > </id> > <property name="Forename" column="Forenames" /> > <property name="Surname" column="Surname" /> > <property name="IsDeleted" column="IsDeleted" /> > > <!-- One-To-Many Associations--> > <bag name="ContactLinks" lazy="true" cascade="all-delete-orphan" > inverse="true" > > <key column="contact_ref"></key> > <one-to-many class="ContactLinkDAO"></one-to-many> > </bag> </class> > </hibernate-mapping> > > The ContactLink Mapping: > <?xml version="1.0" encoding="utf-8" ?> > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" > namespace="Coldharbour.Framework.Model" > assembly="Coldharbour.Framework.Model"> > <class name="Coldharbour.Framework.Model.ContactLinkDAO, > Coldharbour.Framework.Model" table="Contact" lazy="true"> > <id name="CntaDetRef" column="cnta_det_ref"> > <generator class="assigned" /> > </id> > <property name="RefType" column="ref_type" /> > <property name="Reference" column="reference" /> > <property name="ContSubstype" column="cont_substype" /> > <property name="Reltype" column="reltype" /> > > <!-- Many-To-One Associations--> > <many-to-one name="Contact" column="contact_ref" > class="ContactDAO" inverse="false" /> > > </class> > </hibernate-mapping> > > I think that problem is that the ContactLink has an association to the > full-fat ContactDAO object. However I have lazy load set to TRUE. > > This is the SQL that get's generated: > > SELECT top 100 this_.Contact_Ref as Contact1_3_2_, > this_.Forenames as Forenames3_2_, > this_.Surname as Surname3_2_, > this_.IsDeleted as IsDeleted3_2_, > links1_.cnta_det_ref as cnta1_5_0_, > links1_.ref_type as ref2_5_0_, > links1_.reference as reference5_0_, > links1_.cont_substype as cont4_5_0_, > links1_.reltype as reltype5_0_, > links1_.contact_ref as contact6_5_0_, > contactdao4_.Contact_Ref as Contact1_3_1_, > contactdao4_.Title as Title3_1_, > contactdao4_.Initials as Initials3_1_, > contactdao4_.Surname as Surname3_1_, > contactdao4_.Forenames as Forenames3_1_, > contactdao4_.Address1 as Address6_3_1_, > contactdao4_.Address2 as Address7_3_1_, > contactdao4_.Address3 as Address8_3_1_, > contactdao4_.Address4 as Address9_3_1_, > contactdao4_.Postcode as Postcode3_1_, > contactdao4_.Tel_No1 as Tel11_3_1_, > contactdao4_.Tel_No2 as Tel12_3_1_, > contactdao4_.Tel_No3 as Tel13_3_1_, > contactdao4_.Account_No as Account14_3_1_, > contactdao4_.Analysis as Analysis3_1_, > contactdao4_.Invoice_Type as Invoice16_3_1_, > contactdao4_.Gen_Comments as Gen17_3_1_, > contactdao4_.BkSortCode as BkSortCode3_1_, > contactdao4_.BkAccNo as BkAccNo3_1_, > contactdao4_.RecptGroup as RecptGroup3_1_, > contactdao4_.FsType as FsType3_1_, > contactdao4_.InvPrint as InvPrint3_1_, > contactdao4_.InvDoc as InvDoc3_1_, > contactdao4_.Email as Email3_1_, > contactdao4_.Other_Memo as Other25_3_1_, > contactdao4_.Birth_Date as Birth26_3_1_, > contactdao4_.Sex as Sex3_1_, > contactdao4_.Picture as Picture3_1_, > contactdao4_.VerbPass as VerbPass3_1_, > contactdao4_.PayDays as PayDays3_1_, > contactdao4_.Salut_Inv as Salut31_3_1_, > contactdao4_.PayDom as PayDom3_1_, > contactdao4_.PayDomPrev as PayDomPrev3_1_, > contactdao4_.BankAnal as BankAnal3_1_, > contactdao4_.DebtAnal as DebtAnal3_1_, > contactdao4_.DebtCC as DebtCC3_1_, > contactdao4_.InvBackDoc as InvBackDoc3_1_, > contactdao4_.ShowAccBal as ShowAccBal3_1_, > contactdao4_.IsDeleted as IsDeleted3_1_, > contactdao4_.CreatedBy as CreatedBy3_1_, > contactdao4_.CreatedDate as Created41_3_1_, > contactdao4_.UpdatedBy as UpdatedBy3_1_, > contactdao4_.UpdatedDate as Updated43_3_1_ > FROM Contact this_ > inner join ContactLink links1_ > on this_.Contact_Ref = links1_.contact_ref > left outer join Contact contactdao4_ > on links1_.contact_ref = contactdao4_.Contact_Ref > WHERE links1_.ref_type = 654 /* @p0 */ > and this_.IsDeleted = 0 /* @p1 */ > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]<nhusers%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > > -- Fabio Maulo -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
