Unless you're making heavy use of fetchmodes in your mapping, that query should not even include turmas... it would be interesting to see what's the hbm mapping generated by those attributes.
Also, where are you capturing the result of c2? and what does it have to do with the cloned criteria anyway? Diego On Wed, Apr 14, 2010 at 15:58, Diego Dias <[email protected]> wrote: > Diego, > > I did a test: > > ICriteria c = session > .CreateCriteria<Matricula>(); > > c.SetFirstResult(0) > .SetMaxResults(10); > > ICriteria c2 = CriteriaTransformer.Clone(c); > c2.SetProjection(Projections.RowCount()) > .UniqueResult<int>(); > > return c.List<Matricula>(); > > The query generates is: > > SELECT top 10 this_.IdMatricula as IdMatric1_38_17_ > FROM tracking.matriculas this_ > inner join 1.turmas turma2_ on this_.IdTurma=turma2_.IdTurma > left outer join 1.cursos curso3_ on turma2_.IdCurso=curso3_.IdCurso > left outer join 1.status statuscurs4_ on > curso3_.idStatus=statuscurs4_.idStatus > left outer join 1.tipos tipocurso5_ on > curso3_.IdTipoCurso=tipocurso5_.IdTipoCurso > left outer join 1.PadraoComunicacao padraocomu6_ on > curso3_.IdPadraoComunicacao=padraocomu6_.IdPadrao > left outer join 2.lms_cp_organizations organizati7_ on > curso3_.IdOrganization=organizati7_.Id_Organization > left outer join 1.CursosPresenciais cursoprese8_ on > curso3_.IdCurso=cursoprese8_.IdCurso > left outer join 1.turmasstatus turmastatu9_ on > turma2_.idTurmaStatus=turmastatu9_.idTurmaStatus > left outer join 1.TurmasPresenciais turmaprese10_ on > turma2_.IdTurma=turmaprese10_.idTurma > inner join 3.matriculasresumo matricular11_ on > this_.IdMatricula=matricular11_.IdMatricula > inner join 4.Usuarios usuario12_ on this_.IdUsuario=usuario12_.IdUsuario > left outer join 5.Usuarios usuario12_1_ on > usuario12_.IdUsuario=usuario12_1_.idUsuario > left outer join 6.Organizacoes organizaca13_ on > usuario12_.idOrganizacao=organizaca13_.idOrganizacao > left outer join 6.Cargos cargo14_ on usuario12_.idCargo=cargo14_.idCargo > left outer join 5.TiposColaboradores tipocolabo15_ on > usuario12_1_.idTipoColaborador=tipocolabo15_.IdTipo > left outer join 5.NiveisHierarquicos nivelhiera16_ on > usuario12_1_.idNivelHierarquico=nivelhiera16_.IdNivel > left outer join 5.EstadosCivis estadocivi17_ on > usuario12_1_.idEstadoCivil=estadocivi17_.IdEstadoCivil > inner join 3.matriculasstatus matriculas18_ on > this_.idMatriculaStatus=matriculas18_.idMatriculaStatus > > I noticed that the "left outer join" are add when the object is a child of > a child of the object parrent. > I have Matricula that has Turma that has Curso. Then between Matricula and > Turma the NH add inner join but between Turma and Curso the NH add left > outer join. > > This is default? something's wrong with my mapping/model? > > 2010/4/14 Diego Mijelshon <[email protected]> > >> Instead of expecting us to go through all that code to isolate the >> problem, why don't you create a SIMPLE* Criteria example that includes your >> classes and results in yor problem happening? >> >> Hint: >> session.CreateCriteria<AnActualClass>().Add(AnActualRestriction).Etc() >> >> Diego >> >> *: Simple is the opposite of complicated >> >> On Wed, Apr 14, 2010 at 12:42, Diego Dias <[email protected]> wrote: >> >>> My domain classes is represented by E in this methods. >>> PaginationFor is: >>> >>> public class PaginationFor<TEntity> >>> where TEntity: class, IEntity >>> { >>> public PaginationFor() >>> { >>> this.Filters = >>> new KeyValueOperationTrio[] { }; >>> } >>> >>> [DataMember] >>> public int PageIndex { get; set; } >>> >>> [DataMember] >>> public int PageSize { get; set; } >>> >>> [DataMember] >>> public KeyValuePair<string, bool>[] Orders { get; set; } >>> >>> [DataMember] >>> public KeyValueOperationTrio[] Filters { get; set; } >>> } >>> >>> I encapsulates the logic to simplify my query. Basically, I add the >>> filter in PaginationFor and in my Extension Method I convert my filters in >>> Pagination for to Expression to Criteria. >>> >>> I´m sorry by my english because yet I´m learning >>> >>> >>> 2010/4/14 Diego Mijelshon <[email protected]> >>> >>>> "All very simple"?? >>>> I don't even see a reference to the domain classes you mentioned in that >>>> code. >>>> >>>> Diego >>>> >>>> >>>> On Wed, Apr 14, 2010 at 11:18, Diego Dias <[email protected]> wrote: >>>> >>>>> This is code that generates the Criteria: >>>>> >>>>> public static PagedList<E> Paginate<E>(this PaginationFor<E> >>>>> pagination, ICriteria criteria) >>>>> where E : class, IEntity >>>>> { >>>>> if (criteria == null) >>>>> { throw new ArgumentNullException("A criteria deve ser >>>>> passada!"); } >>>>> >>>>> //Limitar o numero de resultados >>>>> SetMaxResults<E>(pagination, criteria); >>>>> >>>>> // adicionar os filtros, para limitar a pesquisa >>>>> SetFilters<E>(pagination, criteria); >>>>> >>>>> // adicionar a ordenação, para os resultados >>>>> AddOrders<E>(pagination, criteria); >>>>> >>>>> //obter a contagem de registros >>>>> int count = GetCount(criteria); >>>>> >>>>> var list = criteria.List(); >>>>> >>>>> // obter os resultados >>>>> IList<E> entitiesList = list.Cast<E>().ToList(); >>>>> >>>>> // caso existam, retirar a referencia a coleções supérfluas >>>>> if (pagination.Filters.Length > 0) >>>>> { >>>>> entitiesList = >>>>> entitiesList.LoadWithout<E>(pagination.Filters); >>>>> } >>>>> // retornar a coleção pronta >>>>> return new PagedList<E>(entitiesList, count); >>>>> } >>>>> >>>>> public static void SetMaxResults<E>(PaginationFor<E> >>>>> pagination, ICriteria criteria) >>>>> where E : class, IEntity >>>>> { >>>>> //caso for enviado o tamanho zero, significa que a query >>>>> //deve retornar todos os registros, segundo os filtros >>>>> if (pagination.PageSize == 0) { return; } >>>>> >>>>> criteria >>>>> .SetMaxResults(pagination.PageSize) >>>>> .SetFirstResult(pagination.PageIndex * >>>>> pagination.PageSize); >>>>> } >>>>> >>>>> public static void AddOrders<E>(PaginationFor<E> pagination, >>>>> ICriteria criteria) >>>>> where E : class, IEntity >>>>> { >>>>> pagination.Orders.Foreach( >>>>> co => { >>>>> criteria.AddOrder( >>>>> new Order(criteria.CreatePath(co), co.Value)); >>>>> }); >>>>> } >>>>> >>>>> And to generates the filter of the criteria we are using a object as: >>>>> >>>>> public class filter{ >>>>> public string property{get;set;} >>>>> >>>>> public object value{get;set;} >>>>> >>>>> public Operation operation {get;set;} being that Operation is a >>>>> Enun like the operations: Equals, NotEquals, Like... >>>>> } >>>>> >>>>> All very very simple. >>>>> >>>>> 2010/4/14 Diego Mijelshon <[email protected]> >>>>> >>>>>> Indeed >>>>>> >>>>>> Diego >>>>>> >>>>>> >>>>>> >>>>>> On Wed, Apr 14, 2010 at 11:06, Richard Wilde >>>>>> <[email protected]>wrote: >>>>>> >>>>>>> I suspect Diego wants to see the code (hql, linq etc) that you use >>>>>>> to generate the code, not the SQL itself... >>>>>>> >>>>>>> >>>>>>> >>>>>>> Many Thanks >>>>>>> Richard >>>>>>> >>>>>>> >>>>>>> >>>>>>> *From:* [email protected] [mailto:[email protected]] *On >>>>>>> Behalf Of *Diego Dias >>>>>>> *Sent:* 14 April 2010 14:55 >>>>>>> *To:* [email protected] >>>>>>> *Subject:* Re: [nhusers] Mapping Nhibernate >>>>>>> >>>>>>> >>>>>>> >>>>>>> In previous messages, but here too: >>>>>>> >>>>>>> >>>>>>> >>>>>>> SELECT this_.IdMatricula as IdMatric1_122_14_, >>>>>>> this_.IdTurma as IdTurma122_14_, >>>>>>> this_.IdUsuario as IdUsuario122_14_, >>>>>>> turma3_.IdTurma as IdTurma25_0_, >>>>>>> turma3_.idTurmaStatus as idTurmaS2_25_0_, >>>>>>> turma3_.IdCurso as IdCurso25_0_, >>>>>>> curso4_.IdCurso as IdCurso31_1_, >>>>>>> curso4_.idStatus as idStatus31_1_, >>>>>>> curso4_.IdTipoCurso as IdTipoC10_31_1_, >>>>>>> curso4_.IdOrganization as IdOrgan11_31_1_, >>>>>>> statuscurs5_.idStatus as idStatus54_2_, >>>>>>> statuscurs5_.strStatus as strStatus54_2_, >>>>>>> tipocurso6_.strDescricao as strDescr2_125_3_, >>>>>>> padraocomu7_.strNmPadrao as strNmPad2_103_4_, >>>>>>> organizati8_.Id_Organization as Id1_87_5_, >>>>>>> organizati8_.strTitle as strTitle87_5_, >>>>>>> cursoprese9_.IdCurso as IdCurso6_6_, >>>>>>> turmastatu10_.idTurmaStatus as idTurmaS1_91_7_, >>>>>>> usuario1_.IdUsuario as IdUsuario116_10_, >>>>>>> organizaca14_.idOrganizacao as idOrgani1_83_11_, >>>>>>> cargo15_.idCargo as idCargo7_12_, >>>>>>> matriculas16_.idMatriculaStatus as idMatric1_15_13_ >>>>>>> FROM tracking.matriculas this_ >>>>>>> inner join cursos.turmas turma3_ on >>>>>>> this_.IdTurma=turma3_.IdTurma >>>>>>> left outer join schemadbo.cursos curso4_ on >>>>>>> turma3_.IdCurso=curso4_.IdCurso >>>>>>> left outer join schemadbo.status statuscurs5_ on >>>>>>> curso4_.idStatus=statuscurs5_.idStatus >>>>>>> left outer join schemadbo.tipos tipocurso6_ on >>>>>>> curso4_.IdTipoCurso=tipocurso6_.IdTipoCurso >>>>>>> left outer join schemadbo.PadraoComunicacao padraocomu7_ on >>>>>>> curso4_.IdPadraoComunicacao=padraocomu7_.IdPadrao >>>>>>> left outer join schemadbo.lms_cp_organizations organizati8_ on >>>>>>> curso4_.IdOrganization=organizati8_.Id_Organization >>>>>>> left outer join schemadbo.CursosPresenciais cursoprese9_ on >>>>>>> curso4_.IdCurso=cursoprese9_.IdCurso >>>>>>> left outer join schemadbo.turmasstatus turmastatu10_ on >>>>>>> turma3_.idTurmaStatus=turmastatu10_.idTurmaStatus >>>>>>> left outer join schemadbo.turmaspresenciais turmaprese11_ on >>>>>>> turma3_.IdTurma=turmaprese11_.idTurma >>>>>>> inner join schemadbo.matriculasresumo matricular12_ on >>>>>>> this_.IdMatricula=matricular12_.IdMatricula >>>>>>> inner join schemadbo.usuarios usuario1_ on >>>>>>> this_.IdUsuario=usuario1_.IdUsuario >>>>>>> left outer join schemadbo.Organizacoes organizaca14_ on >>>>>>> usuario1_.idOrganizacao=organizaca14_.idOrganizacao >>>>>>> left outer join schemadbo.cargos cargo15_ on >>>>>>> usuario1_.idCargo=cargo15_.idCargo >>>>>>> inner join schemadbo.matriculasstatus matriculas16_ on >>>>>>> this_.idMatriculaStatus=matriculas16_.idMatriculaStatus >>>>>>> WHERE this_.IdTurma = @p0 >>>>>>> >>>>>>> 2010/4/14 Diego Mijelshon <[email protected]> >>>>>>> >>>>>>> Where's the code that generates the query? >>>>>>> >>>>>>> Diego >>>>>>> >>>>>>> On Wed, Apr 14, 2010 at 10:49, Diego Dias <[email protected]> >>>>>>> wrote: >>>>>>> >>>>>>> Someone? >>>>>>> >>>>>>> 2010/4/13 Diego Dias <[email protected]> >>>>>>> >>>>>>> >>>>>>> >>>>>>> Hi, guys. >>>>>>> >>>>>>> I'm with a problem with the query that NHibernate generates. My >>>>>>> mapping is like bellow and I'm using ActiveRecord to do mapping: >>>>>>> >>>>>>> public class Matricula >>>>>>> { >>>>>>> [BelongsTo("IdTurma", NotNull=True)] >>>>>>> public Turma {get;set;} >>>>>>> } >>>>>>> >>>>>>> public class Turma >>>>>>> { >>>>>>> [BelongsTo("IdCurso", NotNull=True)] >>>>>>> public Curso {get;set;} >>>>>>> } >>>>>>> >>>>>>> public class Curso >>>>>>> { >>>>>>> [PrimaryKey("IdCurso", Generator=PrimaryKeyType.Identity)] >>>>>>> public int IdCurso{get;set;} >>>>>>> } >>>>>>> >>>>>>> The problem is: The Nh generates the query with left where should be >>>>>>> inner. Between Matricula and Turma they makes inner, but between >>>>>>> Turma >>>>>>> and Curso they makes left outer. What's problem? What's wrong? >>>>>>> >>>>>>> My query: >>>>>>> >>>>>>> SELECT this_.IdMatricula as IdMatric1_122_14_, >>>>>>> this_.IdTurma as IdTurma122_14_, >>>>>>> this_.IdUsuario as IdUsuario122_14_, >>>>>>> turma3_.IdTurma as IdTurma25_0_, >>>>>>> turma3_.idTurmaStatus as idTurmaS2_25_0_, >>>>>>> turma3_.IdCurso as IdCurso25_0_, >>>>>>> curso4_.IdCurso as IdCurso31_1_, >>>>>>> curso4_.idStatus as idStatus31_1_, >>>>>>> curso4_.IdTipoCurso as IdTipoC10_31_1_, >>>>>>> curso4_.IdOrganization as IdOrgan11_31_1_, >>>>>>> statuscurs5_.idStatus as idStatus54_2_, >>>>>>> statuscurs5_.strStatus as strStatus54_2_, >>>>>>> tipocurso6_.strDescricao as strDescr2_125_3_, >>>>>>> padraocomu7_.strNmPadrao as strNmPad2_103_4_, >>>>>>> organizati8_.Id_Organization as Id1_87_5_, >>>>>>> organizati8_.strTitle as strTitle87_5_, >>>>>>> cursoprese9_.IdCurso as IdCurso6_6_, >>>>>>> turmastatu10_.idTurmaStatus as idTurmaS1_91_7_, >>>>>>> usuario1_.IdUsuario as IdUsuario116_10_, >>>>>>> organizaca14_.idOrganizacao as idOrgani1_83_11_, >>>>>>> cargo15_.idCargo as idCargo7_12_, >>>>>>> matriculas16_.idMatriculaStatus as idMatric1_15_13_ >>>>>>> FROM tracking.matriculas this_ >>>>>>> inner join cursos.turmas turma3_ on >>>>>>> this_.IdTurma=turma3_.IdTurma >>>>>>> left outer join schemadbo.cursos curso4_ on >>>>>>> turma3_.IdCurso=curso4_.IdCurso >>>>>>> left outer join schemadbo.status statuscurs5_ on >>>>>>> curso4_.idStatus=statuscurs5_.idStatus >>>>>>> left outer join schemadbo.tipos tipocurso6_ on >>>>>>> curso4_.IdTipoCurso=tipocurso6_.IdTipoCurso >>>>>>> left outer join schemadbo.PadraoComunicacao padraocomu7_ on >>>>>>> curso4_.IdPadraoComunicacao=padraocomu7_.IdPadrao >>>>>>> left outer join schemadbo.lms_cp_organizations organizati8_ on >>>>>>> curso4_.IdOrganization=organizati8_.Id_Organization >>>>>>> left outer join schemadbo.CursosPresenciais cursoprese9_ on >>>>>>> curso4_.IdCurso=cursoprese9_.IdCurso >>>>>>> left outer join schemadbo.turmasstatus turmastatu10_ on >>>>>>> turma3_.idTurmaStatus=turmastatu10_.idTurmaStatus >>>>>>> left outer join schemadbo.turmaspresenciais turmaprese11_ on >>>>>>> turma3_.IdTurma=turmaprese11_.idTurma >>>>>>> inner join schemadbo.matriculasresumo matricular12_ on >>>>>>> this_.IdMatricula=matricular12_.IdMatricula >>>>>>> inner join schemadbo.usuarios usuario1_ on >>>>>>> this_.IdUsuario=usuario1_.IdUsuario >>>>>>> left outer join schemadbo.Organizacoes organizaca14_ on >>>>>>> usuario1_.idOrganizacao=organizaca14_.idOrganizacao >>>>>>> left outer join schemadbo.cargos cargo15_ on >>>>>>> usuario1_.idCargo=cargo15_.idCargo >>>>>>> inner join schemadbo.matriculasstatus matriculas16_ on >>>>>>> this_.idMatriculaStatus=matriculas16_.idMatriculaStatus >>>>>>> WHERE this_.IdTurma = @p0 >>>>>>> >>>>>>> -- >>>>>>> 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. >>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> >>>>>>> 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. >>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> >>>>>>> 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. >>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> 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. >>>>>>> >>>>>>> -- >>>>>>> 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. >>>>>>> >>>>>> >>>>>> -- >>>>>> 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. >>>>>> >>>>> >>>>> -- >>>>> 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. >>>>> >>>> >>>> -- >>>> 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. >>>> >>> >>> -- >>> 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. >>> >> >> -- >> 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. >> > > -- > 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. > -- 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.
