
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}