001/*-
002 * #%L
003 * HAPI FHIR JPA Server
004 * %%
005 * Copyright (C) 2014 - 2024 Smile CDR, Inc.
006 * %%
007 * Licensed under the Apache License, Version 2.0 (the "License");
008 * you may not use this file except in compliance with the License.
009 * You may obtain a copy of the License at
010 *
011 *      http://www.apache.org/licenses/LICENSE-2.0
012 *
013 * Unless required by applicable law or agreed to in writing, software
014 * distributed under the License is distributed on an "AS IS" BASIS,
015 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
016 * See the License for the specific language governing permissions and
017 * limitations under the License.
018 * #L%
019 */
020package ca.uhn.fhir.jpa.search.builder.sql;
021
022import ca.uhn.fhir.context.FhirContext;
023import ca.uhn.fhir.i18n.Msg;
024import ca.uhn.fhir.interceptor.model.RequestPartitionId;
025import ca.uhn.fhir.jpa.config.HibernatePropertiesProvider;
026import ca.uhn.fhir.jpa.model.config.PartitionSettings;
027import ca.uhn.fhir.jpa.model.dao.JpaPid;
028import ca.uhn.fhir.jpa.model.entity.StorageSettings;
029import ca.uhn.fhir.jpa.search.builder.QueryStack;
030import ca.uhn.fhir.jpa.search.builder.predicate.BaseJoiningPredicateBuilder;
031import ca.uhn.fhir.jpa.search.builder.predicate.ComboNonUniqueSearchParameterPredicateBuilder;
032import ca.uhn.fhir.jpa.search.builder.predicate.ComboUniqueSearchParameterPredicateBuilder;
033import ca.uhn.fhir.jpa.search.builder.predicate.CoordsPredicateBuilder;
034import ca.uhn.fhir.jpa.search.builder.predicate.DatePredicateBuilder;
035import ca.uhn.fhir.jpa.search.builder.predicate.NumberPredicateBuilder;
036import ca.uhn.fhir.jpa.search.builder.predicate.QuantityNormalizedPredicateBuilder;
037import ca.uhn.fhir.jpa.search.builder.predicate.QuantityPredicateBuilder;
038import ca.uhn.fhir.jpa.search.builder.predicate.ResourceHistoryPredicateBuilder;
039import ca.uhn.fhir.jpa.search.builder.predicate.ResourceHistoryProvenancePredicateBuilder;
040import ca.uhn.fhir.jpa.search.builder.predicate.ResourceIdPredicateBuilder;
041import ca.uhn.fhir.jpa.search.builder.predicate.ResourceLinkPredicateBuilder;
042import ca.uhn.fhir.jpa.search.builder.predicate.ResourceTablePredicateBuilder;
043import ca.uhn.fhir.jpa.search.builder.predicate.SearchParamPresentPredicateBuilder;
044import ca.uhn.fhir.jpa.search.builder.predicate.StringPredicateBuilder;
045import ca.uhn.fhir.jpa.search.builder.predicate.TagPredicateBuilder;
046import ca.uhn.fhir.jpa.search.builder.predicate.TokenPredicateBuilder;
047import ca.uhn.fhir.jpa.search.builder.predicate.UriPredicateBuilder;
048import ca.uhn.fhir.rest.param.DateParam;
049import ca.uhn.fhir.rest.param.DateRangeParam;
050import ca.uhn.fhir.rest.param.ParamPrefixEnum;
051import com.healthmarketscience.sqlbuilder.BinaryCondition;
052import com.healthmarketscience.sqlbuilder.ComboCondition;
053import com.healthmarketscience.sqlbuilder.ComboExpression;
054import com.healthmarketscience.sqlbuilder.Condition;
055import com.healthmarketscience.sqlbuilder.FunctionCall;
056import com.healthmarketscience.sqlbuilder.InCondition;
057import com.healthmarketscience.sqlbuilder.OrderObject;
058import com.healthmarketscience.sqlbuilder.SelectQuery;
059import com.healthmarketscience.sqlbuilder.dbspec.Join;
060import com.healthmarketscience.sqlbuilder.dbspec.basic.DbColumn;
061import com.healthmarketscience.sqlbuilder.dbspec.basic.DbJoin;
062import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSchema;
063import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSpec;
064import com.healthmarketscience.sqlbuilder.dbspec.basic.DbTable;
065import jakarta.annotation.Nonnull;
066import jakarta.annotation.Nullable;
067import org.hibernate.dialect.Dialect;
068import org.hibernate.dialect.SQLServerDialect;
069import org.hibernate.dialect.pagination.AbstractLimitHandler;
070import org.hibernate.query.internal.QueryOptionsImpl;
071import org.hibernate.query.spi.Limit;
072import org.hibernate.query.spi.QueryOptions;
073import org.slf4j.Logger;
074import org.slf4j.LoggerFactory;
075
076import java.util.ArrayList;
077import java.util.Collection;
078import java.util.List;
079import java.util.Set;
080import java.util.UUID;
081import java.util.stream.Collectors;
082
083import static ca.uhn.fhir.rest.param.ParamPrefixEnum.GREATERTHAN;
084import static ca.uhn.fhir.rest.param.ParamPrefixEnum.GREATERTHAN_OR_EQUALS;
085import static ca.uhn.fhir.rest.param.ParamPrefixEnum.LESSTHAN;
086import static ca.uhn.fhir.rest.param.ParamPrefixEnum.LESSTHAN_OR_EQUALS;
087import static ca.uhn.fhir.rest.param.ParamPrefixEnum.NOT_EQUAL;
088import static org.apache.commons.lang3.ObjectUtils.defaultIfNull;
089
090public class SearchQueryBuilder {
091
092        private static final Logger ourLog = LoggerFactory.getLogger(SearchQueryBuilder.class);
093        private final String myBindVariableSubstitutionBase;
094        private final ArrayList<Object> myBindVariableValues;
095        private final DbSpec mySpec;
096        private final DbSchema mySchema;
097        private final SelectQuery mySelect;
098        private final PartitionSettings myPartitionSettings;
099        private final RequestPartitionId myRequestPartitionId;
100        private final String myResourceType;
101        private final StorageSettings myStorageSettings;
102        private final FhirContext myFhirContext;
103        private final SqlObjectFactory mySqlBuilderFactory;
104        private final boolean myCountQuery;
105        private final Dialect myDialect;
106        private final boolean mySelectPartitionId;
107        private boolean myMatchNothing;
108        private ResourceTablePredicateBuilder myResourceTableRoot;
109        private boolean myHaveAtLeastOnePredicate;
110        private BaseJoiningPredicateBuilder myFirstPredicateBuilder;
111        private boolean dialectIsMsSql;
112        private boolean dialectIsMySql;
113        private boolean myNeedResourceTableRoot;
114        private int myNextNearnessColumnId = 0;
115        private DbColumn mySelectedResourceIdColumn;
116        private DbColumn mySelectedPartitionIdColumn;
117
118        /**
119         * Constructor
120         */
121        public SearchQueryBuilder(
122                        FhirContext theFhirContext,
123                        StorageSettings theStorageSettings,
124                        PartitionSettings thePartitionSettings,
125                        RequestPartitionId theRequestPartitionId,
126                        String theResourceType,
127                        SqlObjectFactory theSqlBuilderFactory,
128                        HibernatePropertiesProvider theDialectProvider,
129                        boolean theCountQuery) {
130                this(
131                                theFhirContext,
132                                theStorageSettings,
133                                thePartitionSettings,
134                                theRequestPartitionId,
135                                theResourceType,
136                                theSqlBuilderFactory,
137                                UUID.randomUUID() + "-",
138                                theDialectProvider.getDialect(),
139                                theCountQuery,
140                                new ArrayList<>(),
141                                thePartitionSettings.isPartitioningEnabled());
142        }
143
144        /**
145         * Constructor for child SQL Builders
146         */
147        private SearchQueryBuilder(
148                        FhirContext theFhirContext,
149                        StorageSettings theStorageSettings,
150                        PartitionSettings thePartitionSettings,
151                        RequestPartitionId theRequestPartitionId,
152                        String theResourceType,
153                        SqlObjectFactory theSqlBuilderFactory,
154                        String theBindVariableSubstitutionBase,
155                        Dialect theDialect,
156                        boolean theCountQuery,
157                        ArrayList<Object> theBindVariableValues,
158                        boolean theSelectPartitionId) {
159                myFhirContext = theFhirContext;
160                myStorageSettings = theStorageSettings;
161                myPartitionSettings = thePartitionSettings;
162                myRequestPartitionId = theRequestPartitionId;
163                myResourceType = theResourceType;
164                mySqlBuilderFactory = theSqlBuilderFactory;
165                myCountQuery = theCountQuery;
166                myDialect = theDialect;
167                if (myDialect instanceof org.hibernate.dialect.MySQLDialect) {
168                        dialectIsMySql = true;
169                }
170                if (myDialect instanceof org.hibernate.dialect.SQLServerDialect) {
171                        dialectIsMsSql = true;
172                }
173
174                mySpec = new DbSpec();
175                mySchema = mySpec.addDefaultSchema();
176                mySelect = new SelectQuery();
177
178                myBindVariableSubstitutionBase = theBindVariableSubstitutionBase;
179                myBindVariableValues = theBindVariableValues;
180                mySelectPartitionId = theSelectPartitionId;
181        }
182
183        public FhirContext getFhirContext() {
184                return myFhirContext;
185        }
186
187        /**
188         * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a Composite Unique search parameter
189         */
190        public ComboUniqueSearchParameterPredicateBuilder addComboUniquePredicateBuilder() {
191                ComboUniqueSearchParameterPredicateBuilder retVal =
192                                mySqlBuilderFactory.newComboUniqueSearchParameterPredicateBuilder(this);
193                addTable(retVal, null);
194                return retVal;
195        }
196
197        /**
198         * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a Composite Unique search parameter
199         */
200        public ComboNonUniqueSearchParameterPredicateBuilder addComboNonUniquePredicateBuilder() {
201                ComboNonUniqueSearchParameterPredicateBuilder retVal =
202                                mySqlBuilderFactory.newComboNonUniqueSearchParameterPredicateBuilder(this);
203                addTable(retVal, null);
204                return retVal;
205        }
206
207        /**
208         * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a COORDS search parameter
209         */
210        public CoordsPredicateBuilder addCoordsPredicateBuilder(@Nullable DbColumn[] theSourceJoinColumn) {
211                CoordsPredicateBuilder retVal = mySqlBuilderFactory.coordsPredicateBuilder(this);
212                addTable(retVal, theSourceJoinColumn);
213                return retVal;
214        }
215
216        /**
217         * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a DATE search parameter
218         */
219        public DatePredicateBuilder addDatePredicateBuilder(@Nullable DbColumn[] theSourceJoinColumn) {
220                DatePredicateBuilder retVal = mySqlBuilderFactory.dateIndexTable(this);
221                addTable(retVal, theSourceJoinColumn);
222                return retVal;
223        }
224
225        /**
226         * Create a predicate builder for selecting on a DATE search parameter
227         */
228        public DatePredicateBuilder createDatePredicateBuilder() {
229                return mySqlBuilderFactory.dateIndexTable(this);
230        }
231
232        /**
233         * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a NUMBER search parameter
234         */
235        public NumberPredicateBuilder addNumberPredicateBuilder(@Nullable DbColumn[] theSourceJoinColumn) {
236                NumberPredicateBuilder retVal = createNumberPredicateBuilder();
237                addTable(retVal, theSourceJoinColumn);
238                return retVal;
239        }
240
241        /**
242         * Create a predicate builder for selecting on a NUMBER search parameter
243         */
244        public NumberPredicateBuilder createNumberPredicateBuilder() {
245                return mySqlBuilderFactory.numberIndexTable(this);
246        }
247
248        /**
249         * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on the Resource table
250         */
251        public ResourceTablePredicateBuilder addResourceTablePredicateBuilder(@Nullable DbColumn[] theSourceJoinColumn) {
252                ResourceTablePredicateBuilder retVal = mySqlBuilderFactory.resourceTable(this);
253                addTable(retVal, theSourceJoinColumn);
254                return retVal;
255        }
256
257        /**
258         * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a QUANTITY search parameter
259         */
260        public QuantityPredicateBuilder addQuantityPredicateBuilder(@Nullable DbColumn[] theSourceJoinColumn) {
261                QuantityPredicateBuilder retVal = createQuantityPredicateBuilder();
262                addTable(retVal, theSourceJoinColumn);
263
264                return retVal;
265        }
266
267        /**
268         * Create a predicate builder for selecting on a QUANTITY search parameter
269         */
270        public QuantityPredicateBuilder createQuantityPredicateBuilder() {
271                return mySqlBuilderFactory.quantityIndexTable(this);
272        }
273
274        public QuantityNormalizedPredicateBuilder addQuantityNormalizedPredicateBuilder(
275                        @Nullable DbColumn[] theSourceJoinColumn) {
276
277                QuantityNormalizedPredicateBuilder retVal = mySqlBuilderFactory.quantityNormalizedIndexTable(this);
278                addTable(retVal, theSourceJoinColumn);
279
280                return retVal;
281        }
282
283        /**
284         * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a <code>_source</code> search parameter
285         */
286        public ResourceHistoryProvenancePredicateBuilder addResourceHistoryProvenancePredicateBuilder(
287                        @Nullable DbColumn[] theSourceJoinColumn, SelectQuery.JoinType theJoinType) {
288                ResourceHistoryProvenancePredicateBuilder retVal =
289                                mySqlBuilderFactory.newResourceHistoryProvenancePredicateBuilder(this);
290                addTable(retVal, theSourceJoinColumn, theJoinType);
291                return retVal;
292        }
293
294        /**
295         * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a <code>_source</code> search parameter
296         */
297        public ResourceHistoryPredicateBuilder addResourceHistoryPredicateBuilder(
298                        @Nullable DbColumn[] theSourceJoinColumn, SelectQuery.JoinType theJoinType) {
299                ResourceHistoryPredicateBuilder retVal = mySqlBuilderFactory.newResourceHistoryPredicateBuilder(this);
300                addTable(retVal, theSourceJoinColumn, theJoinType);
301                return retVal;
302        }
303
304        /**
305         * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a REFERENCE search parameter
306         */
307        public ResourceLinkPredicateBuilder addReferencePredicateBuilder(
308                        QueryStack theQueryStack, @Nullable DbColumn[] theSourceJoinColumn) {
309                ResourceLinkPredicateBuilder retVal = createReferencePredicateBuilder(theQueryStack);
310                addTable(retVal, theSourceJoinColumn);
311                return retVal;
312        }
313
314        /**
315         * Create a predicate builder for selecting on a REFERENCE search parameter
316         */
317        public ResourceLinkPredicateBuilder createReferencePredicateBuilder(QueryStack theQueryStack) {
318                return mySqlBuilderFactory.referenceIndexTable(theQueryStack, this, false);
319        }
320
321        /**
322         * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a resource link where the
323         * source and target are reversed. This is used for _has queries.
324         */
325        public ResourceLinkPredicateBuilder addReferencePredicateBuilderReversed(
326                        QueryStack theQueryStack, DbColumn[] theSourceJoinColumn) {
327                ResourceLinkPredicateBuilder retVal = mySqlBuilderFactory.referenceIndexTable(theQueryStack, this, true);
328                addTable(retVal, theSourceJoinColumn);
329                return retVal;
330        }
331
332        /**
333         * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a STRING search parameter
334         */
335        public StringPredicateBuilder addStringPredicateBuilder(@Nullable DbColumn[] theSourceJoinColumn) {
336                StringPredicateBuilder retVal = createStringPredicateBuilder();
337                addTable(retVal, theSourceJoinColumn);
338                return retVal;
339        }
340
341        /**
342         * Create a predicate builder for selecting on a STRING search parameter
343         */
344        public StringPredicateBuilder createStringPredicateBuilder() {
345                return mySqlBuilderFactory.stringIndexTable(this);
346        }
347
348        /**
349         * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a <code>_tag</code> search parameter
350         */
351        public TagPredicateBuilder addTagPredicateBuilder(@Nullable DbColumn[] theSourceJoinColumn) {
352                TagPredicateBuilder retVal = mySqlBuilderFactory.newTagPredicateBuilder(this);
353                addTable(retVal, theSourceJoinColumn);
354                return retVal;
355        }
356
357        /**
358         * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a TOKEN search parameter
359         */
360        public TokenPredicateBuilder addTokenPredicateBuilder(@Nullable DbColumn[] theSourceJoinColumn) {
361                TokenPredicateBuilder retVal = createTokenPredicateBuilder();
362                addTable(retVal, theSourceJoinColumn);
363                return retVal;
364        }
365
366        /**
367         * Create a predicate builder for selecting on a TOKEN search parameter
368         */
369        public TokenPredicateBuilder createTokenPredicateBuilder() {
370                return mySqlBuilderFactory.tokenIndexTable(this);
371        }
372
373        public void addCustomJoin(
374                        SelectQuery.JoinType theJoinType, DbTable theFromTable, DbTable theToTable, Condition theCondition) {
375                mySelect.addCustomJoin(theJoinType, theFromTable, theToTable, theCondition);
376        }
377
378        public ComboCondition createOnCondition(DbColumn[] theSourceColumn, DbColumn[] theTargetColumn) {
379                ComboCondition onCondition = ComboCondition.and();
380                for (int i = 0; i < theSourceColumn.length; i += 1) {
381                        onCondition.addCondition(BinaryCondition.equalTo(theSourceColumn[i], theTargetColumn[i]));
382                }
383                return onCondition;
384        }
385
386        /**
387         * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a <code>:missing</code> search parameter
388         */
389        public SearchParamPresentPredicateBuilder addSearchParamPresentPredicateBuilder(
390                        @Nullable DbColumn[] theSourceJoinColumn) {
391                SearchParamPresentPredicateBuilder retVal = mySqlBuilderFactory.searchParamPresentPredicateBuilder(this);
392                addTable(retVal, theSourceJoinColumn);
393                return retVal;
394        }
395
396        /**
397         * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a URI search parameter
398         */
399        public UriPredicateBuilder addUriPredicateBuilder(@Nullable DbColumn[] theSourceJoinColumn) {
400                UriPredicateBuilder retVal = createUriPredicateBuilder();
401                addTable(retVal, theSourceJoinColumn);
402                return retVal;
403        }
404
405        /**
406         * Create a predicate builder for selecting on a URI search parameter
407         */
408        public UriPredicateBuilder createUriPredicateBuilder() {
409                return mySqlBuilderFactory.uriIndexTable(this);
410        }
411
412        public SqlObjectFactory getSqlBuilderFactory() {
413                return mySqlBuilderFactory;
414        }
415
416        public ResourceIdPredicateBuilder newResourceIdBuilder() {
417                return mySqlBuilderFactory.resourceId(this);
418        }
419
420        /**
421         * Add and return a predicate builder (or a root query if no root query exists yet) for an arbitrary table
422         */
423        private void addTable(BaseJoiningPredicateBuilder thePredicateBuilder, @Nullable DbColumn[] theSourceJoinColumn) {
424                addTable(thePredicateBuilder, theSourceJoinColumn, SelectQuery.JoinType.INNER);
425        }
426
427        private void addTable(
428                        BaseJoiningPredicateBuilder thePredicateBuilder,
429                        @Nullable DbColumn[] theSourceJoinColumns,
430                        SelectQuery.JoinType theJoinType) {
431                if (theSourceJoinColumns != null) {
432                        DbTable fromTable = theSourceJoinColumns[0].getTable();
433                        DbTable toTable = thePredicateBuilder.getTable();
434                        DbColumn[] toColumn = toJoinColumns(thePredicateBuilder);
435                        addJoin(fromTable, toTable, theSourceJoinColumns, toColumn, theJoinType);
436                } else {
437                        if (myFirstPredicateBuilder == null) {
438
439                                BaseJoiningPredicateBuilder root;
440                                if (!myNeedResourceTableRoot) {
441                                        root = thePredicateBuilder;
442                                } else {
443                                        if (thePredicateBuilder instanceof ResourceTablePredicateBuilder) {
444                                                root = thePredicateBuilder;
445                                        } else {
446                                                root = mySqlBuilderFactory.resourceTable(this);
447                                        }
448                                }
449
450                                if (myCountQuery) {
451                                        mySelect.addCustomColumns(
452                                                        FunctionCall.count().setIsDistinct(true).addColumnParams(root.getResourceIdColumn()));
453                                } else {
454                                        if (mySelectPartitionId) {
455                                                mySelectedResourceIdColumn = root.getResourceIdColumn();
456                                                mySelectedPartitionIdColumn = root.getPartitionIdColumn();
457                                                mySelect.addColumns(mySelectedPartitionIdColumn, mySelectedResourceIdColumn);
458                                        } else {
459                                                mySelectedResourceIdColumn = root.getResourceIdColumn();
460                                                mySelect.addColumns(mySelectedResourceIdColumn);
461                                        }
462                                }
463                                mySelect.addFromTable(root.getTable());
464                                myFirstPredicateBuilder = root;
465
466                                if (!myNeedResourceTableRoot || (thePredicateBuilder instanceof ResourceTablePredicateBuilder)) {
467                                        return;
468                                }
469                        }
470
471                        DbTable fromTable = myFirstPredicateBuilder.getTable();
472                        DbTable toTable = thePredicateBuilder.getTable();
473                        DbColumn[] fromColumn = toJoinColumns(myFirstPredicateBuilder);
474                        DbColumn[] toColumn = toJoinColumns(thePredicateBuilder);
475                        addJoin(fromTable, toTable, fromColumn, toColumn, theJoinType);
476                }
477        }
478
479        @Nonnull
480        public DbColumn[] toJoinColumns(BaseJoiningPredicateBuilder theBuilder) {
481                DbColumn partitionIdColumn = theBuilder.getPartitionIdColumn();
482                DbColumn resourceIdColumn = theBuilder.getResourceIdColumn();
483                return toJoinColumns(partitionIdColumn, resourceIdColumn);
484        }
485
486        /**
487         * Remove or keep partition_id columns depending on settings.
488         */
489        @Nonnull
490        public DbColumn[] toJoinColumns(DbColumn partitionIdColumn, DbColumn resourceIdColumn) {
491                if (isIncludePartitionIdInJoins()) {
492                        return new DbColumn[] {partitionIdColumn, resourceIdColumn};
493                } else {
494                        return new DbColumn[] {resourceIdColumn};
495                }
496        }
497
498        public boolean isIncludePartitionIdInJoins() {
499                return mySelectPartitionId && myPartitionSettings.isPartitionIdsInPrimaryKeys();
500        }
501
502        public void addJoin(DbTable theFromTable, DbTable theToTable, DbColumn[] theFromColumn, DbColumn[] theToColumn) {
503                addJoin(theFromTable, theToTable, theFromColumn, theToColumn, SelectQuery.JoinType.INNER);
504        }
505
506        public void addJoin(
507                        DbTable theFromTable,
508                        DbTable theToTable,
509                        DbColumn[] theFromColumn,
510                        DbColumn[] theToColumn,
511                        SelectQuery.JoinType theJoinType) {
512                assert theFromColumn.length == theToColumn.length;
513                Join join = new DbJoin(mySpec, theFromTable, theToTable, theFromColumn, theToColumn);
514                mySelect.addJoins(theJoinType, join);
515        }
516
517        public boolean isSelectPartitionId() {
518                return mySelectPartitionId;
519        }
520
521        /**
522         * Generate and return the SQL generated by this builder
523         */
524        public GeneratedSql generate(@Nullable Integer theOffset, @Nullable Integer theMaxResultsToFetch) {
525
526                getOrCreateFirstPredicateBuilder();
527
528                mySelect.validate();
529                String sql = mySelect.toString();
530
531                List<Object> bindVariables = new ArrayList<>();
532                while (true) {
533
534                        int idx = sql.indexOf(myBindVariableSubstitutionBase);
535                        if (idx == -1) {
536                                break;
537                        }
538
539                        int endIdx = sql.indexOf("'", idx + myBindVariableSubstitutionBase.length());
540                        String substitutionIndexString = sql.substring(idx + myBindVariableSubstitutionBase.length(), endIdx);
541                        int substitutionIndex = Integer.parseInt(substitutionIndexString);
542                        bindVariables.add(myBindVariableValues.get(substitutionIndex));
543
544                        sql = sql.substring(0, idx - 1) + "?" + sql.substring(endIdx + 1);
545                }
546
547                Integer maxResultsToFetch = theMaxResultsToFetch;
548                Integer offset = theOffset;
549                if (offset != null && offset == 0) {
550                        offset = null;
551                }
552                if (maxResultsToFetch != null || offset != null) {
553
554                        maxResultsToFetch = defaultIfNull(maxResultsToFetch, 10000);
555                        String selectedResourceIdColumn = mySelectedResourceIdColumn.getColumnNameSQL();
556
557                        sql = applyLimitToSql(myDialect, offset, maxResultsToFetch, sql, selectedResourceIdColumn, bindVariables);
558                }
559
560                return new GeneratedSql(myMatchNothing, sql, bindVariables);
561        }
562
563        /**
564         * This method applies the theDialect limiter (select first NNN offset MMM etc etc..) to
565         * a SQL string. It enhances the built-in Hibernate dialect version with some additional
566         * enhancements.
567         */
568        public static String applyLimitToSql(
569                        Dialect theDialect,
570                        Integer theOffset,
571                        Integer theMaxResultsToFetch,
572                        String theInputSql,
573                        @Nullable String theSelectedColumnOrNull,
574                        List<Object> theBindVariables) {
575                AbstractLimitHandler limitHandler = (AbstractLimitHandler) theDialect.getLimitHandler();
576                Limit selection = new Limit();
577                selection.setFirstRow(theOffset);
578                selection.setMaxRows(theMaxResultsToFetch);
579                QueryOptions queryOptions = new QueryOptionsImpl();
580                theInputSql = limitHandler.processSql(theInputSql, selection, queryOptions);
581
582                int startOfQueryParameterIndex = 0;
583
584                boolean isSqlServer = (theDialect instanceof SQLServerDialect);
585                if (isSqlServer) {
586
587                        /*
588                         * SQL server requires an ORDER BY clause to be present in the SQL if there is
589                         * an OFFSET/FETCH FIRST clause, so if there isn't already an ORDER BY clause,
590                         * the theDialect will automatically add an order by with a pseudo-column name. This
591                         * happens in SQLServer2012LimitHandler.
592                         *
593                         * But, SQL Server also pukes if you include an ORDER BY on a column that you
594                         * aren't also SELECTing, if the select statement contains a UNION, INTERSECT or EXCEPT operator.
595                         * Who knows why SQL Server is so picky.. but anyhow, this causes an issue, so we manually replace
596                         * the pseudo-column with an actual selected column.
597                         */
598                        if (theInputSql.contains("order by @@version")) {
599                                if (theSelectedColumnOrNull != null) {
600                                        theInputSql = theInputSql.replace("order by @@version", "order by " + theSelectedColumnOrNull);
601                                } else {
602                                        // not certain if this case can happen, but ordering by the ordinal first column should always
603                                        // be syntactically valid and seems like a better option than ordering by a static value
604                                        // regardless
605                                        theInputSql = theInputSql.replace("order by @@version", "order by 1");
606                                }
607                        }
608
609                        // The SQLServerDialect has a bunch of one-off processing to deal with rules on when
610                        // a limit can be used, so we can't rely on the flags that the limithandler exposes since
611                        // the exact structure of the query depends on the parameters
612                        if (theInputSql.contains("top(?)")) {
613                                theBindVariables.add(0, theMaxResultsToFetch);
614                        }
615                        if (theInputSql.contains("offset 0 rows fetch first ? rows only")) {
616                                theBindVariables.add(theMaxResultsToFetch);
617                        }
618                        if (theInputSql.contains("offset ? rows fetch next ? rows only")) {
619                                theBindVariables.add(theOffset);
620                                theBindVariables.add(theMaxResultsToFetch);
621                        }
622                        if (theOffset != null && theInputSql.contains("rownumber_")) {
623                                theBindVariables.add(theOffset + 1);
624                                theBindVariables.add(theOffset + theMaxResultsToFetch + 1);
625                        }
626
627                } else if (limitHandler.supportsVariableLimit()) {
628
629                        boolean bindLimitParametersFirst = limitHandler.bindLimitParametersFirst();
630                        if (limitHandler.useMaxForLimit() && theOffset != null) {
631                                theMaxResultsToFetch = theMaxResultsToFetch + theOffset;
632                        }
633
634                        if (limitHandler.bindLimitParametersInReverseOrder()) {
635                                startOfQueryParameterIndex = bindCountParameter(
636                                                theBindVariables,
637                                                theMaxResultsToFetch,
638                                                limitHandler,
639                                                startOfQueryParameterIndex,
640                                                bindLimitParametersFirst);
641                                bindOffsetParameter(
642                                                theBindVariables,
643                                                theOffset,
644                                                limitHandler,
645                                                startOfQueryParameterIndex,
646                                                bindLimitParametersFirst);
647                        } else {
648                                startOfQueryParameterIndex = bindOffsetParameter(
649                                                theBindVariables,
650                                                theOffset,
651                                                limitHandler,
652                                                startOfQueryParameterIndex,
653                                                bindLimitParametersFirst);
654                                bindCountParameter(
655                                                theBindVariables,
656                                                theMaxResultsToFetch,
657                                                limitHandler,
658                                                startOfQueryParameterIndex,
659                                                bindLimitParametersFirst);
660                        }
661                }
662                return theInputSql;
663        }
664
665        private static int bindCountParameter(
666                        List<Object> bindVariables,
667                        Integer maxResultsToFetch,
668                        AbstractLimitHandler limitHandler,
669                        int startOfQueryParameterIndex,
670                        boolean bindLimitParametersFirst) {
671                if (limitHandler.supportsLimit()) {
672                        if (bindLimitParametersFirst) {
673                                bindVariables.add(startOfQueryParameterIndex++, maxResultsToFetch);
674                        } else {
675                                bindVariables.add(maxResultsToFetch);
676                        }
677                }
678                return startOfQueryParameterIndex;
679        }
680
681        public static int bindOffsetParameter(
682                        List<Object> theBindVariables,
683                        @Nullable Integer theOffset,
684                        AbstractLimitHandler theLimitHandler,
685                        int theStartOfQueryParameterIndex,
686                        boolean theBindLimitParametersFirst) {
687                if (theLimitHandler.supportsLimitOffset() && theOffset != null) {
688                        if (theBindLimitParametersFirst) {
689                                theBindVariables.add(theStartOfQueryParameterIndex++, theOffset);
690                        } else {
691                                theBindVariables.add(theOffset);
692                        }
693                }
694                return theStartOfQueryParameterIndex;
695        }
696
697        /**
698         * If at least one predicate builder already exists, return the last one added to the chain. If none has been selected, create a builder on HFJ_RESOURCE, add it and return it.
699         */
700        public BaseJoiningPredicateBuilder getOrCreateFirstPredicateBuilder() {
701                return getOrCreateFirstPredicateBuilder(true);
702        }
703
704        /**
705         * If at least one predicate builder already exists, return the last one added to the chain. If none has been selected, create a builder on HFJ_RESOURCE, add it and return it.
706         */
707        public BaseJoiningPredicateBuilder getOrCreateFirstPredicateBuilder(
708                        boolean theIncludeResourceTypeAndNonDeletedFlag) {
709                if (myFirstPredicateBuilder == null) {
710                        getOrCreateResourceTablePredicateBuilder(theIncludeResourceTypeAndNonDeletedFlag);
711                }
712                return myFirstPredicateBuilder;
713        }
714
715        public ResourceTablePredicateBuilder getOrCreateResourceTablePredicateBuilder() {
716                return getOrCreateResourceTablePredicateBuilder(true);
717        }
718
719        public ResourceTablePredicateBuilder getOrCreateResourceTablePredicateBuilder(
720                        boolean theIncludeResourceTypeAndNonDeletedFlag) {
721                if (myResourceTableRoot == null) {
722                        ResourceTablePredicateBuilder resourceTable = mySqlBuilderFactory.resourceTable(this);
723                        addTable(resourceTable, null);
724                        if (theIncludeResourceTypeAndNonDeletedFlag) {
725                                Condition typeAndDeletionPredicate = resourceTable.createResourceTypeAndNonDeletedPredicates();
726                                addPredicate(typeAndDeletionPredicate);
727                        }
728                        myResourceTableRoot = resourceTable;
729                }
730                return myResourceTableRoot;
731        }
732
733        /**
734         * The SQL Builder library has one annoying limitation, which is that it does not use/understand bind variables
735         * for its generated SQL. So we work around this by replacing our contents with a string in the SQL consisting
736         * of <code>[random UUID]-[value index]</code> and then
737         */
738        public String generatePlaceholder(Object theValue) {
739                String placeholder = myBindVariableSubstitutionBase + myBindVariableValues.size();
740                myBindVariableValues.add(theValue);
741                return placeholder;
742        }
743
744        public List<String> generatePlaceholders(Collection<?> theValues) {
745                return theValues.stream().map(this::generatePlaceholder).collect(Collectors.toList());
746        }
747
748        public int countBindVariables() {
749                return myBindVariableValues.size();
750        }
751
752        public void setMatchNothing() {
753                myMatchNothing = true;
754        }
755
756        public DbTable addTable(String theTableName) {
757                return mySchema.addTable(theTableName);
758        }
759
760        public PartitionSettings getPartitionSettings() {
761                return myPartitionSettings;
762        }
763
764        public RequestPartitionId getRequestPartitionId() {
765                return myRequestPartitionId;
766        }
767
768        public String getResourceType() {
769                return myResourceType;
770        }
771
772        public StorageSettings getStorageSettings() {
773                return myStorageSettings;
774        }
775
776        public void addPredicate(@Nonnull Condition theCondition) {
777                assert theCondition != null;
778                mySelect.addCondition(theCondition);
779                myHaveAtLeastOnePredicate = true;
780        }
781
782        public ComboCondition addPredicateLastUpdated(DateRangeParam theDateRange) {
783                ResourceTablePredicateBuilder resourceTableRoot = getOrCreateResourceTablePredicateBuilder(false);
784                return addPredicateLastUpdated(theDateRange, resourceTableRoot);
785        }
786
787        public ComboCondition addPredicateLastUpdated(
788                        DateRangeParam theDateRange, ResourceTablePredicateBuilder theResourceTablePredicateBuilder) {
789                List<Condition> conditions = new ArrayList<>(2);
790                BinaryCondition condition;
791
792                if (isNotEqualsComparator(theDateRange)) {
793                        condition = createConditionForValueWithComparator(
794                                        LESSTHAN,
795                                        theResourceTablePredicateBuilder.getLastUpdatedColumn(),
796                                        theDateRange.getLowerBoundAsInstant());
797                        conditions.add(condition);
798                        condition = createConditionForValueWithComparator(
799                                        GREATERTHAN,
800                                        theResourceTablePredicateBuilder.getLastUpdatedColumn(),
801                                        theDateRange.getUpperBoundAsInstant());
802                        conditions.add(condition);
803                        return ComboCondition.or(conditions.toArray(new Condition[0]));
804                }
805
806                if (theDateRange.getLowerBoundAsInstant() != null) {
807                        condition = createConditionForValueWithComparator(
808                                        GREATERTHAN_OR_EQUALS,
809                                        theResourceTablePredicateBuilder.getLastUpdatedColumn(),
810                                        theDateRange.getLowerBoundAsInstant());
811                        conditions.add(condition);
812                }
813
814                if (theDateRange.getUpperBoundAsInstant() != null) {
815                        condition = createConditionForValueWithComparator(
816                                        LESSTHAN_OR_EQUALS,
817                                        theResourceTablePredicateBuilder.getLastUpdatedColumn(),
818                                        theDateRange.getUpperBoundAsInstant());
819                        conditions.add(condition);
820                }
821
822                return ComboCondition.and(conditions.toArray(new Condition[0]));
823        }
824
825        private boolean isNotEqualsComparator(DateRangeParam theDateRange) {
826                if (theDateRange != null) {
827                        DateParam lb = theDateRange.getLowerBound();
828                        DateParam ub = theDateRange.getUpperBound();
829
830                        return lb != null
831                                        && ub != null
832                                        && lb.getPrefix().equals(NOT_EQUAL)
833                                        && ub.getPrefix().equals(NOT_EQUAL);
834                }
835                return false;
836        }
837
838        public void addResourceIdsPredicate(List<JpaPid> thePidList) {
839                List<Long> pidList = thePidList.stream().map(JpaPid::getId).collect(Collectors.toList());
840
841                DbColumn resourceIdColumn = getOrCreateFirstPredicateBuilder().getResourceIdColumn();
842                InCondition predicate = new InCondition(resourceIdColumn, generatePlaceholders(pidList));
843                addPredicate(predicate);
844        }
845
846        public void excludeResourceIdsPredicate(Set<JpaPid> theExistingPidSetToExclude) {
847
848                // Do  nothing if it's empty
849                if (theExistingPidSetToExclude == null || theExistingPidSetToExclude.isEmpty()) return;
850
851                List<Long> excludePids = JpaPid.toLongList(theExistingPidSetToExclude);
852
853                ourLog.trace("excludePids = {}", excludePids);
854
855                DbColumn resourceIdColumn = getOrCreateFirstPredicateBuilder().getResourceIdColumn();
856                InCondition predicate = new InCondition(resourceIdColumn, generatePlaceholders(excludePids));
857                predicate.setNegate(true);
858                addPredicate(predicate);
859        }
860
861        public BinaryCondition createConditionForValueWithComparator(
862                        ParamPrefixEnum theComparator, DbColumn theColumn, Object theValue) {
863                switch (theComparator) {
864                        case LESSTHAN:
865                                return BinaryCondition.lessThan(theColumn, generatePlaceholder(theValue));
866                        case LESSTHAN_OR_EQUALS:
867                                return BinaryCondition.lessThanOrEq(theColumn, generatePlaceholder(theValue));
868                        case GREATERTHAN:
869                                return BinaryCondition.greaterThan(theColumn, generatePlaceholder(theValue));
870                        case GREATERTHAN_OR_EQUALS:
871                                return BinaryCondition.greaterThanOrEq(theColumn, generatePlaceholder(theValue));
872                        case NOT_EQUAL:
873                                return BinaryCondition.notEqualTo(theColumn, generatePlaceholder(theValue));
874                        case EQUAL:
875                                // NB: fhir searches are always range searches;
876                                // which is why we do not use "EQUAL"
877                        case STARTS_AFTER:
878                        case APPROXIMATE:
879                        case ENDS_BEFORE:
880                        default:
881                                throw new IllegalArgumentException(Msg.code(1263));
882                }
883        }
884
885        public SearchQueryBuilder newChildSqlBuilder(boolean theSelectPartitionId) {
886                return new SearchQueryBuilder(
887                                myFhirContext,
888                                myStorageSettings,
889                                myPartitionSettings,
890                                myRequestPartitionId,
891                                myResourceType,
892                                mySqlBuilderFactory,
893                                myBindVariableSubstitutionBase,
894                                myDialect,
895                                false,
896                                myBindVariableValues,
897                                theSelectPartitionId);
898        }
899
900        public SelectQuery getSelect() {
901                return mySelect;
902        }
903
904        public boolean haveAtLeastOnePredicate() {
905                return myHaveAtLeastOnePredicate;
906        }
907
908        public void addSortCoordsNear(
909                        CoordsPredicateBuilder theCoordsBuilder,
910                        double theLatitudeValue,
911                        double theLongitudeValue,
912                        boolean theAscending) {
913                FunctionCall absLatitude = new FunctionCall("ABS");
914                String latitudePlaceholder = generatePlaceholder(theLatitudeValue);
915                ComboExpression absLatitudeMiddle = new ComboExpression(
916                                ComboExpression.Op.SUBTRACT, theCoordsBuilder.getColumnLatitude(), latitudePlaceholder);
917                absLatitude = absLatitude.addCustomParams(absLatitudeMiddle);
918
919                FunctionCall absLongitude = new FunctionCall("ABS");
920                String longitudePlaceholder = generatePlaceholder(theLongitudeValue);
921                ComboExpression absLongitudeMiddle = new ComboExpression(
922                                ComboExpression.Op.SUBTRACT, theCoordsBuilder.getColumnLongitude(), longitudePlaceholder);
923                absLongitude = absLongitude.addCustomParams(absLongitudeMiddle);
924
925                ComboExpression sum = new ComboExpression(ComboExpression.Op.ADD, absLatitude, absLongitude);
926                String ordering;
927                if (theAscending) {
928                        ordering = "";
929                } else {
930                        ordering = " DESC";
931                }
932
933                String columnName = "MHD" + (myNextNearnessColumnId++);
934                mySelect.addAliasedColumn(sum, columnName);
935                mySelect.addCustomOrderings(columnName + ordering);
936        }
937
938        public void addSortString(DbColumn theColumnValueNormalized, boolean theAscending) {
939                addSortString(theColumnValueNormalized, theAscending, false);
940        }
941
942        public void addSortString(DbColumn theColumnValueNormalized, boolean theAscending, boolean theUseAggregate) {
943                OrderObject.NullOrder nullOrder = OrderObject.NullOrder.LAST;
944                addSortString(theColumnValueNormalized, theAscending, nullOrder, theUseAggregate);
945        }
946
947        public void addSortNumeric(DbColumn theColumnValueNormalized, boolean theAscending) {
948                addSortNumeric(theColumnValueNormalized, theAscending, false);
949        }
950
951        public void addSortNumeric(DbColumn theColumnValueNormalized, boolean theAscending, boolean theUseAggregate) {
952                OrderObject.NullOrder nullOrder = OrderObject.NullOrder.LAST;
953                addSortNumeric(theColumnValueNormalized, theAscending, nullOrder, theUseAggregate);
954        }
955
956        public void addSortDate(DbColumn theColumnValueNormalized, boolean theAscending) {
957                addSortDate(theColumnValueNormalized, theAscending, false);
958        }
959
960        public void addSortDate(DbColumn theColumnValueNormalized, boolean theAscending, boolean theUseAggregate) {
961                OrderObject.NullOrder nullOrder = OrderObject.NullOrder.LAST;
962                addSortDate(theColumnValueNormalized, theAscending, nullOrder, theUseAggregate);
963        }
964
965        public void addSortString(
966                        DbColumn theTheColumnValueNormalized,
967                        boolean theTheAscending,
968                        OrderObject.NullOrder theNullOrder,
969                        boolean theUseAggregate) {
970                if ((dialectIsMySql || dialectIsMsSql)) {
971                        // MariaDB, MySQL and MSSQL do not support "NULLS FIRST" and "NULLS LAST" syntax.
972                        String direction = theTheAscending ? " ASC" : " DESC";
973                        String sortColumnName =
974                                        theTheColumnValueNormalized.getTable().getAlias() + "." + theTheColumnValueNormalized.getName();
975                        final StringBuilder sortColumnNameBuilder = new StringBuilder();
976                        // The following block has been commented out for performance.
977                        // Uncomment if NullOrder is needed for MariaDB, MySQL or MSSQL
978                        /*
979                        // Null values are always treated as less than non-null values.
980                        if ((theTheAscending && theNullOrder == OrderObject.NullOrder.LAST)
981                                || (!theTheAscending && theNullOrder == OrderObject.NullOrder.FIRST)) {
982                                // In this case, precede the "order by" column with a case statement that returns
983                                // 1 for null and 0 non-null so that nulls will be sorted as greater than non-nulls.
984                                sortColumnNameBuilder.append( "CASE WHEN " ).append( sortColumnName ).append( " IS NULL THEN 1 ELSE 0 END" ).append(direction).append(", ");
985                        }
986                        */
987                        sortColumnName = formatColumnNameForAggregate(theTheAscending, theUseAggregate, sortColumnName);
988                        sortColumnNameBuilder.append(sortColumnName).append(direction);
989                        mySelect.addCustomOrderings(sortColumnNameBuilder.toString());
990                } else {
991                        addSort(theTheColumnValueNormalized, theTheAscending, theNullOrder, theUseAggregate);
992                }
993        }
994
995        private static String formatColumnNameForAggregate(
996                        boolean theTheAscending, boolean theUseAggregate, String sortColumnName) {
997                if (theUseAggregate) {
998                        String aggregateFunction;
999                        if (theTheAscending) {
1000                                aggregateFunction = "MIN";
1001                        } else {
1002                                aggregateFunction = "MAX";
1003                        }
1004                        sortColumnName = aggregateFunction + "(" + sortColumnName + ")";
1005                }
1006                return sortColumnName;
1007        }
1008
1009        public void addSortNumeric(
1010                        DbColumn theTheColumnValueNormalized,
1011                        boolean theAscending,
1012                        OrderObject.NullOrder theNullOrder,
1013                        boolean theUseAggregate) {
1014                if ((dialectIsMySql || dialectIsMsSql)) {
1015                        // MariaDB, MySQL and MSSQL do not support "NULLS FIRST" and "NULLS LAST" syntax.
1016                        // Null values are always treated as less than non-null values.
1017                        // As such special handling is required here.
1018                        String direction;
1019                        String sortColumnName =
1020                                        theTheColumnValueNormalized.getTable().getAlias() + "." + theTheColumnValueNormalized.getName();
1021                        if ((theAscending && theNullOrder == OrderObject.NullOrder.LAST)
1022                                        || (!theAscending && theNullOrder == OrderObject.NullOrder.FIRST)) {
1023                                // Negating the numeric column value and reversing the sort order will ensure that the rows appear
1024                                // in the correct order with nulls appearing first or last as needed.
1025                                direction = theAscending ? " DESC" : " ASC";
1026                                sortColumnName = "-" + sortColumnName;
1027                        } else {
1028                                direction = theAscending ? " ASC" : " DESC";
1029                        }
1030                        sortColumnName = formatColumnNameForAggregate(theAscending, theUseAggregate, sortColumnName);
1031                        mySelect.addCustomOrderings(sortColumnName + direction);
1032                } else {
1033                        addSort(theTheColumnValueNormalized, theAscending, theNullOrder, theUseAggregate);
1034                }
1035        }
1036
1037        public void addSortDate(
1038                        DbColumn theTheColumnValueNormalized,
1039                        boolean theTheAscending,
1040                        OrderObject.NullOrder theNullOrder,
1041                        boolean theUseAggregate) {
1042                if ((dialectIsMySql || dialectIsMsSql)) {
1043                        // MariaDB, MySQL and MSSQL do not support "NULLS FIRST" and "NULLS LAST" syntax.
1044                        String direction = theTheAscending ? " ASC" : " DESC";
1045                        String sortColumnName =
1046                                        theTheColumnValueNormalized.getTable().getAlias() + "." + theTheColumnValueNormalized.getName();
1047                        final StringBuilder sortColumnNameBuilder = new StringBuilder();
1048                        // The following block has been commented out for performance.
1049                        // Uncomment if NullOrder is needed for MariaDB, MySQL or MSSQL
1050                        /*
1051                        // Null values are always treated as less than non-null values.
1052                        if ((theTheAscending && theNullOrder == OrderObject.NullOrder.LAST)
1053                                || (!theTheAscending && theNullOrder == OrderObject.NullOrder.FIRST)) {
1054                                // In this case, precede the "order by" column with a case statement that returns
1055                                // 1 for null and 0 non-null so that nulls will be sorted as greater than non-nulls.
1056                                sortColumnNameBuilder.append( "CASE WHEN " ).append( sortColumnName ).append( " IS NULL THEN 1 ELSE 0 END" ).append(direction).append(", ");
1057                        }
1058                        */
1059                        sortColumnName = formatColumnNameForAggregate(theTheAscending, theUseAggregate, sortColumnName);
1060                        sortColumnNameBuilder.append(sortColumnName).append(direction);
1061                        mySelect.addCustomOrderings(sortColumnNameBuilder.toString());
1062                } else {
1063                        addSort(theTheColumnValueNormalized, theTheAscending, theNullOrder, theUseAggregate);
1064                }
1065        }
1066
1067        private void addSort(
1068                        DbColumn theTheColumnValueNormalized,
1069                        boolean theTheAscending,
1070                        OrderObject.NullOrder theNullOrder,
1071                        boolean theUseAggregate) {
1072                OrderObject.Dir direction = theTheAscending ? OrderObject.Dir.ASCENDING : OrderObject.Dir.DESCENDING;
1073                Object columnToOrder = theTheColumnValueNormalized;
1074                if (theUseAggregate) {
1075                        if (theTheAscending) {
1076                                columnToOrder = FunctionCall.min().addColumnParams(theTheColumnValueNormalized);
1077                        } else {
1078                                columnToOrder = FunctionCall.max().addColumnParams(theTheColumnValueNormalized);
1079                        }
1080                }
1081                OrderObject orderObject = new OrderObject(direction, columnToOrder);
1082                orderObject.setNullOrder(theNullOrder);
1083                mySelect.addCustomOrderings(orderObject);
1084        }
1085
1086        /**
1087         * If set to true (default is false), force the generated SQL to start
1088         * with the {@link ca.uhn.fhir.jpa.model.entity.ResourceTable HFJ_RESOURCE}
1089         * table at the root of the query.
1090         * <p>
1091         * This seems to perform better if there are multiple joins on the
1092         * resource ID table.
1093         */
1094        public void setNeedResourceTableRoot(boolean theNeedResourceTableRoot) {
1095                myNeedResourceTableRoot = theNeedResourceTableRoot;
1096        }
1097}