Ignore:
Timestamp:
May 6, 2011, 5:41:49 PM (9 years ago)
Author:
robert@…
Message:

Adjusted querying process in order to improve speed with large amounts of data. The querying is now performed directly in HQL, instead of fetching all possible objects from the database and filtering them.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/groovy/dbnp/query/SampleSearch.groovy

    r1800 r1820  
    3030
    3131                this.entity = "Sample";
    32         }
    33 
    34         /**
    35          * Searches for samples based on the given criteria. All criteria have to be satisfied and
    36          * criteria for the different entities are satisfied as follows:
    37          *
    38          *              Sample.title = 'abc'           
    39          *                              Only samples are returned from studies with title 'abc'
    40          *             
    41          *              Subject.species = 'human'
    42          *                              Only samples are returned from subjects with species = 'human' 
    43          *
    44          *              Sample.name = 'sample 1'
    45          *                              Only samples are returned with name = 'sample 1'
    46          *
    47          *              Event.startTime = '0s'
    48          *                              Only samples are returned from subjects that have had an event with start time = '0s' 
    49          *
    50          *              SamplingEvent.startTime = '0s'
    51          *                              Only samples are returned that have originated from a sampling event with start time = '0s' 
    52          *
    53          *              Assay.module = 'metagenomics'
    54          *                              Only samples are returned that have been processed in an assay with module = metagenomics 
    55          *
    56          * When searching for more than one criterion per entity, these are taken combined. Searching for
    57          *
    58          *              Subject.species = 'human'
    59          *              Subject.name = 'Jan'
    60          *
    61          *  will result in all samples from a human subject named 'Jan'. Samples from a mouse subject
    62          *  named 'Jan' or a human subject named 'Kees' won't satisfy the criteria.
    63          *     
    64          */
    65         @Override
    66         void executeAnd() {
    67                 // If no criteria are found, return all samples
    68                 if( !criteria || criteria.size() == 0 ) {
    69                         results = Sample.list().findAll { it.parent?.canRead( this.user ) };
    70                         return;
    71                 }
    72 
    73                 // We expect the study criteria to be the most discriminative, and discard
    74                 // the most samples. (e.g. by searching on study title or study type). For
    75                 // that reason we first look through the list of studies. However, when the
    76                 // user didn't enter any study criteria, this will be an extra step, but doesn't
    77                 // cost much time to process.
    78                 def samples = []
    79                 if( getEntityCriteria( 'Study' ).size() > 0 ) {
    80                         def studies = Study.list()
    81                         if( studies )
    82                                 studies = studies.findAll { it.canRead( this.user ) };
    83 
    84                         studies = filterStudiesOnStudyCriteria( studies );
    85 
    86                         if( studies.size() == 0 ) {
    87                                 results = [];
    88                                 return;
    89                         }
    90 
    91                         def c = Sample.createCriteria()
    92                         samples = c.list {
    93                                 'in'( 'parent', studies )
    94                         }
    95 
    96                         // Save data about the resulting studies in the
    97                         // result fields array. The data that is now in the array
    98                         // is saved based on the study id, not based on the sample id
    99                         clearResultFields();
    100                         saveResultFields( samples, getEntityCriteria( "Study" ), { sample, criterion ->
    101                                 return criterion.getFieldValue( sample.parent );
    102                         });
    103                 } else {
    104                         samples = Sample.list()
    105                         if( samples )
    106                                 samples = samples.findAll { it.parent?.canRead( this.user ) }
    107                 }
    108 
    109                 samples = filterOnSubjectCriteria( samples );
    110                 samples = filterOnSampleCriteria( samples );
    111                 samples = filterOnEventCriteria( samples );
    112                 samples = filterOnSamplingEventCriteria( samples );
    113                 samples = filterOnAssayCriteria( samples );
    114                
    115                 // Filter on criteria for which the entity is unknown
    116                 samples = filterOnAllFieldsCriteria( samples );
    117                
    118                 // Filter on module criteria
    119                 samples = filterOnModuleCriteria( samples );
    120 
    121                 // Save matches
    122                 results = samples;
    123         }
    124 
    125         /**
    126          * Searches for samples based on the given criteria. Only one of the criteria have to be satisfied and
    127          * criteria for the different entities are satisfied as follows:
    128          *
    129          *              Sample.title = 'abc'
    130          *                              Samples are returned from studies with title 'abc'
    131          *
    132          *              Subject.species = 'human'
    133          *                              Samples are returned from subjects with species = 'human'
    134          *
    135          *              Sample.name = 'sample 1'
    136          *                              Samples are returned with name = 'sample 1'
    137          *
    138          *              Event.startTime = '0s'
    139          *                              Samples are returned from subjects that have had an event with start time = '0s'
    140          *
    141          *              SamplingEvent.startTime = '0s'
    142          *                              Samples are returned that have originated from a sampling event with start time = '0s'
    143          *
    144          *              Assay.module = 'metagenomics'
    145          *                              Samples are returned that have been processed in an assay with module = metagenomics
    146          *
    147          * When searching for more than one criterion per entity, these are taken separately. Searching for
    148          *
    149          *              Subject.species = 'human'
    150          *              Subject.name = 'Jan'
    151          *
    152          *  will result in all samples from a human subject or a subject named 'Jan'. Samples from a mouse subject
    153          *  named 'Jan' or a human subject named 'Kees' will also satisfy the criteria.
    154          *
    155          */
    156         @Override
    157         void executeOr() {
    158                 def allSamples = Sample.list().findAll { it.parent?.canRead( this.user ) }.toList();
    159                 executeOr( allSamples );
    16032        }
    16133
     
    20274
    20375        /**
    204          * Filters the given list of studies on the study criteria
    205          * @param studies       Original list of studies
    206          * @return                      List with all samples that match the Study-criteria
     76         * Returns the HQL name for the element or collections to be searched in, for the given entity name
     77         * For example: when searching for Subject.age > 50 with Study results, the system must search in all study.subjects for age > 50.
     78         * But when searching for Sample results, the system must search in sample.parentSubject for age > 50
     79         *
     80         * @param entity        Name of the entity of the criterion
     81         * @return                      HQL name for this element or collection of elements
    20782         */
    208         protected List filterStudiesOnStudyCriteria( List studies ) {
    209                 return filterOnTemplateEntityCriteria(studies, "Study", { study, criterion -> return criterion.getFieldValue( study ) })
     83        protected String elementName( String entity ) {
     84                switch( entity ) {
     85                        case "Sample":                  return "sample"
     86                        case "Study":                   return "sample.parent"
     87                        case "Subject":                 return "sample.parentSubject"
     88                        case "SamplingEvent":   return "sample.parentEvent"
     89                        case "Event":                   return "sample.parentEventGroup.events"
     90                        case "Assay":                   return "sample.assays"                  // Will not be used, since entityClause() is overridden
     91                        default:                                return null;
     92                }
    21093        }
    21194
    21295        /**
    213          * Filters the given list of samples on the assay criteria
    214          * @param samples       Original list of samples
    215          * @return                      List with all samples that match the assay-criteria
     96         * Returns the a where clause for the given entity name
     97         * For example: when searching for Subject.age > 50 with Study results, the system must search
     98         *
     99         *      WHERE EXISTS( FROM study.subjects subject WHERE subject IN (...)
     100         *
     101         * The returned string is fed to sprintf with 3 string parameters:
     102         *              from (in this case 'study.subjects'
     103         *              alias (in this case 'subject'
     104         *              paramName (in this case '...')
     105         *
     106         * @param entity                Name of the entity of the criterion
     107         * @return                      HQL where clause for this element or collection of elements
    216108         */
    217         @Override
    218         protected List filterOnAssayCriteria( List samples ) {
    219                 if( !samples?.size() )
    220                         return [];
     109        protected String entityClause( String entity ) {
     110                switch( entity ) {
     111                        case "Assay":
     112                                return 'EXISTS( FROM Assay assay WHERE assay IN (:%3$s) AND EXISTS( FROM assay.samples assaySample WHERE assaySample = sample ) ) '
     113                        default:
     114                                return super.entityClause( entity );
     115                }
     116        }
    221117
    222                 // There is no sample.assays property, so we have to look for assays another way: just find
    223                 // all assays that match the criteria
    224                 def criteria = getEntityCriteria( 'Assay' );
    225 
    226                 if( getEntityCriteria( 'Assay' ).size() == 0 ) {
    227                         if( searchMode == SearchMode.and )
    228                                 return samples
    229                         else if( searchMode == SearchMode.or )
    230                                 return [];
    231                 }
    232 
    233                 def assays = filterEntityList( Assay.list(), criteria, { assay, criterion ->
    234                         if( !assay )
    235                                 return false
    236 
    237                         return criterion.matchOneEntity( assay );
    238                 });
    239 
    240                 println "Matching assays: " + assays
    241        
    242                 // If no assays match these criteria, then no samples will match either
    243                 if( assays.size() == 0 )
    244                         return [];
    245 
    246                 // Now filter the samples on whether they are attached to the filtered assays
    247                 def matchingSamples = samples.findAll { sample ->
    248                         if( !sample.parent )
    249                                 return false;
    250 
    251                         def studyAssays = assays.findAll { it.parent.equals( sample.parent ); }
    252                        
    253                         println "Assays for " + sample + " (based on study): " + studyAssays
    254                        
    255                         // See if this sample is present in any of the matching assays. If so,
    256                         // this sample matches the criteria
    257                         for( def assay in studyAssays ) {
    258                                 if( assay.samples?.contains( sample ) )
    259                                         return true;
    260                                
    261                                 println "Assay " + assay + " with samples " + assay.samples + " doesn't contain " + sample;
    262                         }
    263 
    264                         return false;
    265                 }
    266 
    267                 return matchingSamples;
     118        /**
     119         * Returns true iff the given entity is accessible by the user currently logged in
     120         *
     121         * @param entity                Study to determine accessibility for.
     122         * @return                      True iff the user is allowed to access this study
     123         */
     124        protected boolean isAccessible( def entity ) {
     125                return entity?.parent?.canRead( this.user );
    268126        }
    269127
Note: See TracChangeset for help on using the changeset viewer.