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