Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Multiple Detached Criteria Builders in the same query generate repetitive SQL

Description

Since CBORM 3.8.0, criteria queries using multiple DetachedCriteriaBuilders appear to reuse criteria in the generated SQL:

For example:

var documentSub = c.createSubcriteria( 'Document', 'document2' ); var documentSub2 = c.createSubcriteria( 'Document', 'document3' ); var documentSub3 = c.createSubcriteria( 'Document', 'document4' ); documentSub.createAlias( 'document2.editors', 'editors2' ) .withProjections( property='document2.id' ); documentSub2.createAlias( 'document3.reviewers', 'reviewers2' ) .withProjections( property='document3.id' ); documentSub3.createAlias( 'document4.finalReviewers', 'finalReviewers2' ) .withProjections( property='document4.id' ); documentSub.add( r.eq( 'editors2.id' , "user_id_123" ) ); documentSub2.add( r.eq( 'reviewers2.id' , "user_id_123" ) ); documentSub3.add( r.eq( 'finalReviewers2.id' , "user_id_123" ) ); c.add( r.or( documentSub.propertyIn( 'id' ), documentSub2.propertyIn( 'id' ), documentSub3.propertyIn( 'id' ) ) );

This generates SQL:

and ( this_.id in ( select document4_.id as y0_ from Documents document4_ ... where finalReviewers1_.id='user_id_123' ) or this_.id in ( select document4_.id as y0_ from Documents document4_ ... where finalReviewers1_.id='user_id_123' ) or this_.id in ( select document4_.id as y0_ from Documents document4_ ... where finalReviewers1_.id='user_id_123' ) )

Activity

Show:

Jon ClausenJune 14, 2022 at 5:44 PM

Jon ClausenJune 14, 2022 at 1:30 PM
Edited

This issue is caused by order in which Wirebox resolved the metadata attributes when auto wiring, as noted here. Because Restrictions is a singleton, wirebox was creating it as a singleton and ignoring the scope=“noscope” attribute on SubQueries.cfc

https://ortussolutions.atlassian.net/browse/WIREBOX-126

Michael Born @ OrtusJune 13, 2022 at 7:17 PM

I originally thought this was due to the JavaProxyBuilder caching and reusing Hibernate objects, however, I am no longer certain.

Michael Born @ OrtusJune 13, 2022 at 7:16 PM

You and me both, !

I spent some time on a test case for this (see below), but I am very confused as I cannot replicate it outside of our client project. However, I definitely confirmed that rolling back to CBORM 3.7.0 resolves the issue for our client. So it is a CBORM issue, but I can’t (yet) create a test case that shows the issue.

https://github.com/coldbox-modules/cborm/compare/development...michaelborn:patch/test-multiple-detached-criteria-builders#diff-751cec08c2486d4e1829eb4d30784deabbc36f6bb86e0bafab8962640e52bc37R27-R60

Luis MajanoJune 13, 2022 at 7:04 PM

I will need your help on this . I always have headaches dealing with detached criterias.

Declined
Pinned fields
Click on the next to a field label to start pinning.

Details

Assignee

Reporter

Priority

Sentry

Created June 13, 2022 at 6:23 PM
Updated June 14, 2022 at 5:44 PM
Resolved June 14, 2022 at 5:44 PM

Flag notifications