Hibernation criteria - return parent records that have child records

Question

All,

I probably know too much about this problem, but ...

The table "A" with two one-to-many relationships "A1" and "A2" returns all records in table "A" that contain at least one child record in table "A1" or "A2" ...

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

Thank!

Answer 1

You need a criterion Restrictions.isNotEmpty():

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

Answer 2

You need a subquery using DetachedCriteria:

Ayende . .

Answer 3

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

/*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)