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();
cr.add(
   cr.createSubcriteria('report','subrpt')
   .withProjections(property="report_id")
   .createAlias('subrpt.confirmations','subconf')
   .createAlias('subconf.staffs','substaffs')
   .createAlias('substaffs.teams','subteams')
   .isEq('subteams.teamName','Developers')
   .propertyNotIn("report_id")
);

Notes

  • 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:

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s