Hibernate Community • View topic - How to generate discriminator condition in join not in where

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

How to generate discriminator condition in join not in where

Page 1 of 1

[ 4 posts ]

Previous topic | Next topic

Author

Message

pepelnm

Post subject: How to generate discriminator condition in join not in where

Posted: Thu Mar 30, 2006 7:23 am

Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster

Hibernate version: 3.1.*

RequisitoZona is a subclass of Requisito with discriminator tipo = 'Z'
Requisito has a TipoDocumento property and TipoDocumento has Set<Requisito> property.

HQL

Code:

select
        d,
        r 
    from
        RequisitoZona as r  
        right outer join
        r.tipoDocumento as d
            with r.zona = :zona

Generated SQL

Code:

select
            ...
        from
            requisito requisitoz0_ 
        right outer join
            tipos_documento tipodocume1_ 
                on requisitoz0_.idTipodocumento=tipodocume1_.id 
                and (
                    requisitoz0_.idAsignado=?
                ) 
        where
            requisitoz0_.tipo='Z'

SQL I want

Code:

select
            ...
        from
            requisito requisitoz0_ 
        right outer join
            tipos_documento tipodocume1_ 
                on requisitoz0_.idTipodocumento=tipodocume1_.id 
                and (
                    requisitoz0_.idAsignado=?
                ) 
                and
                requisitoz0_.tipo='Z'

Top

tenwit

Post subject:

Posted: Thu Mar 30, 2006 10:46 pm

Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008

You can't, and you don't need to. If you generate an execution plan for those two queries, you'll see that they're identical. Or at least, they are on SQLServer.

Top

pepelnm

Post subject:

Posted: Fri Mar 31, 2006 5:39 am

Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster

With MySQL 5 the results are differents.

I don't know if it is a bug of MySQL or SQLServer.

Any comment from a standard SQL expert?

Top

pepelnm

Post subject:

Posted: Fri Mar 31, 2006 5:43 am

Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster

I found this workaround but I dislike it.

I declare in the map file a read only property for the discriminator column. Then I can ask for the superclass instead of the subclass and filter it explicitly with the discriminator column.

HQL code

Code:

select
        d,
        r
    from
        Requisito as r 
        right outer join
        r.tipoDocumento as d
            with
                    r.zona = :zona 
                    and
                    r.tipo = 'Z'

Top

Page 1 of 1

[ 4 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum