-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: How to constrain collection to exactly one match?
PostPosted: Sun Apr 04, 2004 3:47 pm 
Newbie

Joined: Sat Apr 03, 2004 10:29 pm
Posts: 3
Hibernate 2.1, Oracle 9.2. Relevant slice of mapping file follows:

Code:
<class name="com.foo.AccountCharge" table="AccountCharge">
  <id name="id" type="long" column="id">
    <generator class="sequence">
      <param name="sequence">objectid_sequence</param>
    </generator>
  </id>

  <!-- ... -->

  <many-to-one name="invoice" class="com.foo.Invoice" column="invoice_id" cascade="none" />
</class>

<class name="com.foo.Invoice" table="Invoice">
  <id name="id" type="long" column="id">
    <generator class="sequence">
      <param name="sequence">objectid_sequence</param>
    </generator>
  </id>

  <!-- ... -->

  <set name="accountCharges" access="field" table="AccountCharge" cascade="none" lazy="true">
    <key column="invoice_id" />
    <one-to-many class="com.foo.AccountCharge" />
  </set>
</class>


I understand from reading the FAQ that the HQL way to query for Invoices which have associated AccountCharges that meet certain criteria would be:

Code:
  select distinct inv from Invoice inv
    join inv.accountCharges ac
    where ac.property1 = :value1 and ac.property2 = :value2


How can I retrieve only those invoices with exactly one match that meets the given criteria?


Top
 Profile  
 
 Post subject:
PostPosted: Sun Apr 04, 2004 4:19 pm 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
can you reformulate your question?


maybe add
where size(inv.accountCharges) = 1 ??


Top
 Profile  
 
 Post subject:
PostPosted: Sun Apr 04, 2004 6:47 pm 
Newbie

Joined: Sat Apr 03, 2004 10:29 pm
Posts: 3
delpouve wrote:
can you reformulate your question?
maybe add where size(inv.accountCharges) = 1 ??


i'll experiment with that. my hunch is that merely adding the size constraint on inv.accountCharges will yield invoices with exactly one account charge, and that account charge happens to meet the other criteria. for my purposes, it's ok if an invoice has any number of account charges; i'm interested in the invoices for which only one of those charges meets the criteria.

then again, i'm new to hibernate and not familiar with the ins and outs of HQL.

i hope this clarified my question.

cheers,
p


Top
 Profile  
 
 Post subject:
PostPosted: Sun Apr 04, 2004 8:24 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Use a subquery.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Apr 05, 2004 11:37 am 
Newbie

Joined: Sat Apr 03, 2004 10:29 pm
Posts: 3
gavin wrote:
Use a subquery.


thanks for the hint. the following achieved the desired results:

Code:
select inv from Invoice as inv
  where (
    select count(*) from inv.accounts.elements as acc
    where acc.property1 = ? and acc.property2 = ?
  ) = 1


cheers,
p


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

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

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.