A Subquery Using ColdBox’s Detached Criteria Builder

Let’s say we want to create the following SQL query with a subquery using ColdBox’s Detached Criteria Builder:

select *
from reports r
where r.report_id not in (
   select report_id
   from confirmations c
   inner join staffs s on s.staff_id = c.staff_id
   inner join teams t on t.team_id = s.team_id
   where t.teamName = 'Developers'

In our objects world we have the following corresponding entities:

  • report
  • confirmation
  • staff
  • team

And the following relationships between them:

  • A report can have one to many confirmations submitted by staff
  • A confirmation belongs to a staff
  • A staff belongs to a team

Then our ORM query for the above SQL query using ColdBox’s Detached Criteria Builder would be as follows:

cr = reportService.newCriteria();


  • confirmations is the relationship between the report and confirmation entities
  • staffs is the relationship between the confirmation and staff entities
  • teams is the relationship between the staff and team entities

Further Learning

For more information on ColdBox Criteria Builder and Detached Criteria Builder, please visit these links:


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s