Hibernate Community • View topic - HQL for related objects

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

HQL for related objects

Page 1 of 1

[ 6 posts ]

Previous topic | Next topic

Author

Message

SteveO

Post subject: HQL for related objects

Posted: Mon Jun 13, 2005 12:24 pm

Hi,

I have an object VehicleJob which has a collection of Vehicle instances. Each Vehicle instance can be related to an Invoice object (but may also be null)

I need a query which gets all VehicleJob instances that have one or more Vehicles where Invoice is null.

I've tried the following but I am starting to get confused!

Code:

IQuery q = session.CreateQuery("from VehicleJob as j where exists (select elements(j.Vehicles) where j.Vehicles.Invoice is null)");

Can anyone help with the HQL?

Many thanks.

Top

devonl

Post subject:

Posted: Mon Jun 13, 2005 2:47 pm

Expert

Joined: Fri May 13, 2005 5:56 pm
Posts: 308
Location: Santa Barbara, California, USA

what about using my favorite interface ICriteria?

not tested but try something like:

Code:

IList vJobs = session.CreateCriteria(typeof(VehicleJob)
  .add(Expression.IsNull(Vehicle.Invoice))
  .list();

HTH,

-devon

Top

sergey

Post subject:

Posted: Mon Jun 13, 2005 4:12 pm

Contributor

Joined: Wed May 11, 2005 4:59 pm
Posts: 1766
Location: Prague, Czech Republic

Hmm, how about this?

Code:

select job
from VehicleJob as job
    join job.Vehicles as vehicle
    left join vehicle.Invoice as invoice
where invoice is null

You may need to distinct-ify the results yourself though.

Top

Guest

Post subject:

Posted: Thu Jun 16, 2005 8:45 am

Sergey and Devonl,

Thanks very much for the help. I went with sergey's suggestion in the end and it worked brilliantly. Now starting to get my head around how HQL works.

Thanks.

Top

harald_m_mueller

Post subject: Re: HQL for related objects

Posted: Mon Mar 26, 2007 12:02 pm

Beginner

Joined: Wed Nov 29, 2006 10:32 am
Posts: 34

Hi - (late coming, I know ...): Is anything wrong with the OP's HQL? IMHO, it should work -

SteveO wrote:

Code:

IQuery q = session.CreateQuery("from VehicleJob as j where exists (select elements(j.Vehicles) where j.Vehicles.Invoice is null)");

....

Regards
Harald M.

Top

harald_m_mueller

Post subject: Re: HQL for related objects

Posted: Mon Mar 26, 2007 12:29 pm

Beginner

Joined: Wed Nov 29, 2006 10:32 am
Posts: 34

harald_m_mueller wrote:

Hi - (late coming, I know ...): Is anything wrong with the OP's HQL? IMHO, it should work -

Steve0 wrote:

IQuery q = session.CreateQuery("from VehicleJob as j where exists (select elements(j.Vehicles) where j.Vehicles.Invoice is null)");
...

...

Answering my own question: The following should work:

Code:

IQuery q = session.CreateQuery("from VehicleJob as j where exists (select elements(j.Vehicles) as v where v.Invoice is null)");

j.Vehicles in the original HQL cannot be continued with .Invoice, because it is a collection!

Harald M.

Top

Page 1 of 1

[ 6 posts ]

Board index » Hibernate & Java Persistence » NHibernate 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