Hibernate Criteria - Return parent records that have child records

Question

All,

I'm probably over-analyzing this problem, but...

Given table "A" with two one-to-many relationships "A1" and "A2", return all records in table "A" that have at least one child record in either table "A1" or "A2"...

I'm not necessarily interested in knowing what the child data is, but rather that I just have child data.

Thanks!

Answer 1

You need a subquery, using DetachedCriteria:

There is an example in Ayende's Blog. I don't have currently the time to work it out.

Answer 2

You need a Restrictions.isNotEmpty() criterion:

List<A> r = s.createCriteria(A.class)
    .add(Restrictions.or(
        Restrictions.isNotEmpty("a1"), 
        Restrictions.isNotEmpty("a2"))).list();

Answer 3

I think that this example will help you. It is written in t-sql, but should be easy to follow whatever platform you are using.

/*Create data structures*/
CREATE TABLE Parent (  
  ParentId      INT         NOT NULL    PRIMARY KEY  
  , ParentName  VARCHAR(50) NOT NULL)

CREATE TABLE ChildA (  
  ChildAId      INT         NOT NULL    PRIMARY KEY  
  , ParentId    INT         NOT NULL    CONSTRAINT FK_ChildA_Parent FOREIGN KEY REFERENCES Parent(ParentId)  
  , ChildAName  VARCHAR(50) NOT NULL)

CREATE TABLE ChildB (  
  ChildBId      INT         NOT NULL    PRIMARY KEY  
  , ParentId    INT         NOT NULL    CONSTRAINT FK_ChildB_Parent FOREIGN KEY REFERENCES Parent(ParentId)  
  , ChildBName  VARCHAR(50) NOT NULL)

/* Insert four parents */  
INSERT INTO Parent VALUES (1,'A')  
INSERT INTO Parent VALUES (2,'B')  
INSERT INTO Parent VALUES (3,'C')  
INSERT INTO Parent VALUES (4,'D')  

/* Insert two children for A */  
INSERT INTO ChildA VALUES (1,1,'a')  
INSERT INTO ChildB VALUES (1,1,'a')

/* Insert one child for B */  
INSERT INTO ChildA VALUES (2,2,'b')

/* Insert one child for C */  
INSERT INTO ChildB VALUES (2,3,'c')

/* This select stmt returns A with children in both child tables, B with a child in ChildA, and C with a child in ChildB, but no D. */   
SELECT  *  
FROM    Parent p  
WHERE   EXISTS (select 1 from ChildA a where p.ParentId = a.ParentId)  
OR      EXISTS (select 1 from ChildB b where p.ParentId = b.ParentId)