Subqueries

If you are using Detached Criteria Builder for a criteria subquery, you will also need to use one of the methods from the ColdBox subqueries class. This is what will ultimately bind the subquery result to the root entity.

The most important thing to remember is that this subquery is what needs to be added as a criterion to your criteria query. So whether you are doing a simple subquery or building up a complex detached criteria, the result of one of the methods below should be what is added as a criterion to the criteria query (see example below).

Examples

// wrong way...will fail because the subquery method “propertyIn()” is not what is added
c.add(
   c.createSubcriteria( ‘Car’, ‘CarSub’ )
    .withProjections( property=’CarID’ )
    .propertyIn( ‘CarID’ )
    .isEq( ‘Make’, ‘Ford’ )
).list();

// right way...since propertyIn() is last in the chain, it’s value will be what is ultimately added as a criteria
c.add(
   c.createSubcriteria( ‘Car’, ‘CarSub’ )
    .withProjections( property=’CarID’ )
    .isEq( ‘Make’, ‘Ford’ )
    .propertyIn( ‘CarID’ )
).list();

// right way--split up
dc = c.createSubcriteria( ‘Car’, ‘CarSub’ )
  .withProjections( property=’CarID’ )
  .isEq( ‘Make’, ‘Ford’ );
c.add( dc.propertyIn( ‘CarID’ ) ).list();

Method	Description
detachedSQLProjection	A single or array of DetachedCriteriaBuilders which will return the projected value
subEq(value)	Where the result of the subquery is eq to a specific value
subEqAll(value)	Where all values in the subquery result are equal to a specific value
subGe(value)	Where values in the subquery result are greater than or equal to a specific value
subGeAll(value)	Where all values in the subquery result are equal to a specific value
subGeSome(value)	Where some values in the subquery result are greater than a specific value
subGt(value)	Where values in the subquery result are greater than a specific value
subGtAll(required string value)	Where all values in the subquery result are greater than a specific value
subGtSome(required string value)	Where some values in the subquery result are greater than a specific value
subIn(required string value)	Where values in the subquery result are contained within the specified list of values
subLe(required string value)	Where values in the subquery result are less than or equal to a specific value
subLeAll(required string value)	Where all values in the subquery result are less than or equal to a specific value
subLeSome(required string value)	Where some values in the subquery result are less than or equal to a specific value
subLt(required string value)	Where values in the subquery result are less than a specific value
subLtAll(required string value)	Where all values in the subquery result are less than a specific value
subLtSome(required string value)	Where some values in the subquery result are less than a specific value
subNe(required string value)	Where values in the subquery result are not equal to a specific value
subNotIn(required string value)	Where values in the subquery result are not contained within the specified list of values
exists	Where the subquery returns some result
notExists	Where the subquery does not return a result
propertyEq(required string property)	Where values in the subquery result are equal to the the specified property value
propertyEqAll(required string property)	Where all values in the subquery result are equal to the the specified property value
propertyGe(required string property)	Where values in the subquery result are greater than or equal to the the specified property value
propertyGeAll(required string property)	Where all values in the subquery result are greater than or equal to the the specified property value
propertyGeSome(required string property)	Where some values in the subquery result are greater than or equal to the the specified property value
propertyGt(required string property)	Where values in the subquery result are greater than the the specified property value
propertyGtAll(required string property)	Where all values in the subquery result are greater than the the specified property value
propertyGtSome(required string property)	Where some values in the subquery result are greater than the the specified property value
propertyIn(required string property)	Where values in the subquery result are contained in within the list of values for the specified property
propertyLe(required string property)	Where values in the subquery result are less than or equal to the the specified property value
propertyLeAll(required string property)	Where all values in the subquery result are less than or equal to the the specified property value
propertyLeSome(required string property)	Where some values in the subquery result are less than or equal to the the specified property value
propertyLt(required string property)	Where values in the subquery result are less than the the specified property value
propertyLtAll(required string property)	Where all values in the subquery result are less than the the specified property value
propertyLtSome(required string property)	Where some values in the subquery result are less than the the specified property value
propertyNe(required string property)	Where values in the subquery result are not equal to the the specified property value
propertyNotIn(required string property)	Where values in the subquery result are not contained in within the list of values for the specified property