Hibernate Community • View topic - exists, elements and subqueries

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

exists, elements and subqueries

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

birju

Post subject: exists, elements and subqueries

Posted: Tue Sep 14, 2004 12:38 pm

Newbie

Joined: Tue Aug 17, 2004 8:19 am
Posts: 8
Location: Paris

Hi, i need to perform an exists with a subquery, but i read in a previous post
http://forum.hibernate.org/viewtopic.php?t=930043&highlight=elements+subquery
that elements only applies to collections.
The SQL query to translate is below, all my entities are mapped in hibernate as you would expect and are working fine. I use bags for all collections.

Code:

select distinct a.*
  from workflow_alarm a,
       core_user u,
       workflow_project wproj
where (u.user_login in ('birju','kirill','discover')
   or wproj.id in (1,2,3,4,5,65,6,7))
and
(
  exists
  (
    select null
      from workflow_user_alarm ua
     where ua.user_id = u.id
       and ua.alarm_id = a.id
  )
  or exists
  (
    select null
      from workflow_user_process up,
           workflow_process p
     where up.user_id = u.id
       and up.process_id = p.id
       and p.id = a.process_id
  )
  or exists
  (
    select null
      from workflow_user_project uproj,
           workflow_project proj,
           workflow_process proc
     where u.id = uproj.user_id
       and uproj.project_id = proj.id
       and proj.id = proc.project_id
       and a.process_id = proc.id
  )
  or exists
  (
    select null
      from workflow_process proc
     where proc.project_id = wproj.id
       and a.process_id = proc.id
  )
);

Are selects supported within exists()? Can you give me any pointers as to go about this?
Any help would be much appreciated.
Thanks,
Birju

Top

birju

Post subject:

Posted: Wed Sep 15, 2004 8:45 am

Newbie

Joined: Tue Aug 17, 2004 8:19 am
Posts: 8
Location: Paris

I figured it out...the sql logic was wrong what it should have been like was this:

Code:

 select a.*
   from workflow_alarm a
  where
      ( 
        exists
        (
            select null
              from workflow_user_alarm ua,
                   core_user u
             where u.user_login in ('birju')
               and ua.user_id = u.id
               and ua.alarm_id = a.id
        )
        or exists
        (
            select null
              from workflow_user_process up,
                   workflow_process p,
                   core_user u
             where u.user_login in ('birju')
               and up.user = u.id
               and up.process = p.id
               and p.id = a.process
        )
        or exists
        (
            select null
              from workflow_user_project uproj,
                   workflow_project proj,
                   workflow_process proc,
                   core_user u
             where u.user_login in ('birju')
               and u.id = uproj.user_id
               and uproj.project_id = proj.id
               and proj.id = proc.project_id
               and a.process = proc.id
        )
      )
   or
    (
        exists
        (
            select null
              from workflow_project proj,
                   workflow_process proc
             where proj.id in (1,2,3)
               and proc.project_id = proj.id
               and a.process = proc.id
             
        )
    )
;

converted to hql it is:

Code:

select a from net.bookingasp.facades.scheduler.Alarm a where
    (
       exists ( 
           from net.bookingasp.data.core.user.pojo.User u
           join u.alarms a2
          where u.login in ('birju')
            and a2=a )
       or exists (  
           from net.bookingasp.data.core.user.pojo.User u 
           join u.processes proc
           join proc.alarms a2
          where u.login in ('birju')
            and a2=a )
       or exists (
          from net.bookingasp.data.core.user.pojo.User u
          join u.projects proj
          join proj.processes proc
          join proc.alarms a2
         where u.login in ('birju')
           and a2=a )
    )
  or exists ( 
          from net.bookingasp.data.workflow.project.Project proj
          join proj.processes proc
          join proc.alarms a2
         where proj.id in (1,2,3)
           and a2=a )

enjoy!

Birju

Top

birju

Post subject:

Posted: Wed Sep 15, 2004 9:02 am

Newbie

Joined: Tue Aug 17, 2004 8:19 am
Posts: 8
Location: Paris

same query, but faster....

Code:

select a from net.bookingasp.facades.scheduler.Alarm a 
where
       exists ( 
           from net.bookingasp.data.core.user.pojo.User u
           join u.alarms a2
          where u.login in ('birju')
            and a2=a )
       or exists (  
           from net.bookingasp.data.core.user.pojo.User u 
           join u.processes proc
           join proc.alarms a2
          where u.login in ('birju')
            and a2=a )
       or exists (
          from net.bookingasp.data.core.user.pojo.User u
          join u.projects proj
          join proj.processes proc
          join proc.alarms a2
         where u.login in ('birju')
           and a2=a )
     or exists ( 
          from net.bookingasp.data.workflow.project.Project proj
          join proj.processes proc
          join proc.alarms a2
         where proj.id in (1,2,3)
           and a2=a )

Top

Page 1 of 1

[ 3 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