
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 excludeResourceIdsPredicate(Set<JpaPid> theExistingPidSetToExclude) { 907 908 // Do nothing if it's empty 909 if (theExistingPidSetToExclude == null || theExistingPidSetToExclude.isEmpty()) return; 910 911 List<Long> excludePids = JpaPid.toLongList(theExistingPidSetToExclude); 912 913 ourLog.trace("excludePids = {}", excludePids); 914 915 DbColumn resourceIdColumn = getOrCreateFirstPredicateBuilder().getResourceIdColumn(); 916 InCondition predicate = new InCondition(resourceIdColumn, generatePlaceholders(excludePids)); 917 predicate.setNegate(true); 918 addPredicate(predicate); 919 } 920 921 public void excludeResourceTypesPredicate(Collection<String> theResourceTypes) { 922 DbColumn resourceTypeCol = getOrCreateResourceTablePredicateBuilder().getResourceTypeColumn(); 923 Condition c = new NotCondition(new InCondition(resourceTypeCol, generatePlaceholders(theResourceTypes))); 924 addPredicate(c); 925 } 926 927 public BinaryCondition createConditionForValueWithComparator( 928 ParamPrefixEnum theComparator, DbColumn theColumn, Object theValue) { 929 switch (theComparator) { 930 case LESSTHAN: 931 return BinaryCondition.lessThan(theColumn, generatePlaceholder(theValue)); 932 case LESSTHAN_OR_EQUALS: 933 return BinaryCondition.lessThanOrEq(theColumn, generatePlaceholder(theValue)); 934 case GREATERTHAN: 935 return BinaryCondition.greaterThan(theColumn, generatePlaceholder(theValue)); 936 case GREATERTHAN_OR_EQUALS: 937 return BinaryCondition.greaterThanOrEq(theColumn, generatePlaceholder(theValue)); 938 case NOT_EQUAL: 939 return BinaryCondition.notEqualTo(theColumn, generatePlaceholder(theValue)); 940 case EQUAL: 941 // NB: fhir searches are always range searches; 942 // which is why we do not use "EQUAL" 943 case STARTS_AFTER: 944 case APPROXIMATE: 945 case ENDS_BEFORE: 946 default: 947 throw new IllegalArgumentException(Msg.code(1263)); 948 } 949 } 950 951 public SearchQueryBuilder newChildSqlBuilder(boolean theSelectPartitionId) { 952 return new SearchQueryBuilder( 953 myFhirContext, 954 myStorageSettings, 955 myPartitionSettings, 956 myRequestPartitionId, 957 myResourceType, 958 mySqlBuilderFactory, 959 myBindVariableSubstitutionBase, 960 myDialect, 961 false, 962 myBindVariableValues, 963 theSelectPartitionId, 964 false); 965 } 966 967 public SelectQuery getSelect() { 968 return mySelect; 969 } 970 971 public boolean haveAtLeastOnePredicate() { 972 return myHaveAtLeastOnePredicate; 973 } 974 975 public void addSortCoordsNear( 976 CoordsPredicateBuilder theCoordsBuilder, 977 double theLatitudeValue, 978 double theLongitudeValue, 979 boolean theAscending) { 980 FunctionCall absLatitude = new FunctionCall("ABS"); 981 String latitudePlaceholder = generatePlaceholder(theLatitudeValue); 982 ComboExpression absLatitudeMiddle = new ComboExpression( 983 ComboExpression.Op.SUBTRACT, theCoordsBuilder.getColumnLatitude(), latitudePlaceholder); 984 absLatitude = absLatitude.addCustomParams(absLatitudeMiddle); 985 986 FunctionCall absLongitude = new FunctionCall("ABS"); 987 String longitudePlaceholder = generatePlaceholder(theLongitudeValue); 988 ComboExpression absLongitudeMiddle = new ComboExpression( 989 ComboExpression.Op.SUBTRACT, theCoordsBuilder.getColumnLongitude(), longitudePlaceholder); 990 absLongitude = absLongitude.addCustomParams(absLongitudeMiddle); 991 992 ComboExpression sum = new ComboExpression(ComboExpression.Op.ADD, absLatitude, absLongitude); 993 String ordering; 994 if (theAscending) { 995 ordering = ""; 996 } else { 997 ordering = " DESC"; 998 } 999 1000 String columnName = "MHD" + (myNextNearnessColumnId++); 1001 mySelect.addAliasedColumn(sum, columnName); 1002 mySelect.addCustomOrderings(columnName + ordering); 1003 } 1004 1005 public void addSortString(DbColumn theColumnValueNormalized, boolean theAscending) { 1006 addSortString(theColumnValueNormalized, theAscending, false); 1007 } 1008 1009 public void addSortString(DbColumn theColumnValueNormalized, boolean theAscending, boolean theUseAggregate) { 1010 OrderObject.NullOrder nullOrder = OrderObject.NullOrder.LAST; 1011 addSortString(theColumnValueNormalized, theAscending, nullOrder, theUseAggregate); 1012 } 1013 1014 public void addSortNumeric(DbColumn theColumnValueNormalized, boolean theAscending) { 1015 addSortNumeric(theColumnValueNormalized, theAscending, false); 1016 } 1017 1018 public void addSortNumeric(DbColumn theColumnValueNormalized, boolean theAscending, boolean theUseAggregate) { 1019 OrderObject.NullOrder nullOrder = OrderObject.NullOrder.LAST; 1020 addSortNumeric(theColumnValueNormalized, theAscending, nullOrder, theUseAggregate); 1021 } 1022 1023 public void addSortDate(DbColumn theColumnValueNormalized, boolean theAscending) { 1024 addSortDate(theColumnValueNormalized, theAscending, false); 1025 } 1026 1027 public void addSortDate(DbColumn theColumnValueNormalized, boolean theAscending, boolean theUseAggregate) { 1028 OrderObject.NullOrder nullOrder = OrderObject.NullOrder.LAST; 1029 addSortDate(theColumnValueNormalized, theAscending, nullOrder, theUseAggregate); 1030 } 1031 1032 public void addSortString( 1033 DbColumn theTheColumnValueNormalized, 1034 boolean theTheAscending, 1035 OrderObject.NullOrder theNullOrder, 1036 boolean theUseAggregate) { 1037 if ((dialectIsMySql || dialectIsMsSql)) { 1038 // MariaDB, MySQL and MSSQL do not support "NULLS FIRST" and "NULLS LAST" syntax. 1039 String direction = theTheAscending ? " ASC" : " DESC"; 1040 String sortColumnName = 1041 theTheColumnValueNormalized.getTable().getAlias() + "." + theTheColumnValueNormalized.getName(); 1042 final StringBuilder sortColumnNameBuilder = new StringBuilder(); 1043 // The following block has been commented out for performance. 1044 // Uncomment if NullOrder is needed for MariaDB, MySQL or MSSQL 1045 /* 1046 // Null values are always treated as less than non-null values. 1047 if ((theTheAscending && theNullOrder == OrderObject.NullOrder.LAST) 1048 || (!theTheAscending && theNullOrder == OrderObject.NullOrder.FIRST)) { 1049 // In this case, precede the "order by" column with a case statement that returns 1050 // 1 for null and 0 non-null so that nulls will be sorted as greater than non-nulls. 1051 sortColumnNameBuilder.append( "CASE WHEN " ).append( sortColumnName ).append( " IS NULL THEN 1 ELSE 0 END" ).append(direction).append(", "); 1052 } 1053 */ 1054 sortColumnName = formatColumnNameForAggregate(theTheAscending, theUseAggregate, sortColumnName); 1055 sortColumnNameBuilder.append(sortColumnName).append(direction); 1056 mySelect.addCustomOrderings(sortColumnNameBuilder.toString()); 1057 } else { 1058 addSort(theTheColumnValueNormalized, theTheAscending, theNullOrder, theUseAggregate); 1059 } 1060 } 1061 1062 private static String formatColumnNameForAggregate( 1063 boolean theTheAscending, boolean theUseAggregate, String sortColumnName) { 1064 if (theUseAggregate) { 1065 String aggregateFunction; 1066 if (theTheAscending) { 1067 aggregateFunction = "MIN"; 1068 } else { 1069 aggregateFunction = "MAX"; 1070 } 1071 sortColumnName = aggregateFunction + "(" + sortColumnName + ")"; 1072 } 1073 return sortColumnName; 1074 } 1075 1076 public void addSortNumeric( 1077 DbColumn theTheColumnValueNormalized, 1078 boolean theAscending, 1079 OrderObject.NullOrder theNullOrder, 1080 boolean theUseAggregate) { 1081 if ((dialectIsMySql || dialectIsMsSql)) { 1082 // MariaDB, MySQL and MSSQL do not support "NULLS FIRST" and "NULLS LAST" syntax. 1083 // Null values are always treated as less than non-null values. 1084 // As such special handling is required here. 1085 String direction; 1086 String sortColumnName = 1087 theTheColumnValueNormalized.getTable().getAlias() + "." + theTheColumnValueNormalized.getName(); 1088 if ((theAscending && theNullOrder == OrderObject.NullOrder.LAST) 1089 || (!theAscending && theNullOrder == OrderObject.NullOrder.FIRST)) { 1090 // Negating the numeric column value and reversing the sort order will ensure that the rows appear 1091 // in the correct order with nulls appearing first or last as needed. 1092 direction = theAscending ? " DESC" : " ASC"; 1093 sortColumnName = "-" + sortColumnName; 1094 } else { 1095 direction = theAscending ? " ASC" : " DESC"; 1096 } 1097 sortColumnName = formatColumnNameForAggregate(theAscending, theUseAggregate, sortColumnName); 1098 mySelect.addCustomOrderings(sortColumnName + direction); 1099 } else { 1100 addSort(theTheColumnValueNormalized, theAscending, theNullOrder, theUseAggregate); 1101 } 1102 } 1103 1104 public void addSortDate( 1105 DbColumn theTheColumnValueNormalized, 1106 boolean theTheAscending, 1107 OrderObject.NullOrder theNullOrder, 1108 boolean theUseAggregate) { 1109 if ((dialectIsMySql || dialectIsMsSql)) { 1110 // MariaDB, MySQL and MSSQL do not support "NULLS FIRST" and "NULLS LAST" syntax. 1111 String direction = theTheAscending ? " ASC" : " DESC"; 1112 String sortColumnName = 1113 theTheColumnValueNormalized.getTable().getAlias() + "." + theTheColumnValueNormalized.getName(); 1114 final StringBuilder sortColumnNameBuilder = new StringBuilder(); 1115 // The following block has been commented out for performance. 1116 // Uncomment if NullOrder is needed for MariaDB, MySQL or MSSQL 1117 /* 1118 // Null values are always treated as less than non-null values. 1119 if ((theTheAscending && theNullOrder == OrderObject.NullOrder.LAST) 1120 || (!theTheAscending && theNullOrder == OrderObject.NullOrder.FIRST)) { 1121 // In this case, precede the "order by" column with a case statement that returns 1122 // 1 for null and 0 non-null so that nulls will be sorted as greater than non-nulls. 1123 sortColumnNameBuilder.append( "CASE WHEN " ).append( sortColumnName ).append( " IS NULL THEN 1 ELSE 0 END" ).append(direction).append(", "); 1124 } 1125 */ 1126 sortColumnName = formatColumnNameForAggregate(theTheAscending, theUseAggregate, sortColumnName); 1127 sortColumnNameBuilder.append(sortColumnName).append(direction); 1128 mySelect.addCustomOrderings(sortColumnNameBuilder.toString()); 1129 } else { 1130 addSort(theTheColumnValueNormalized, theTheAscending, theNullOrder, theUseAggregate); 1131 } 1132 } 1133 1134 private void addSort( 1135 DbColumn theTheColumnValueNormalized, 1136 boolean theTheAscending, 1137 OrderObject.NullOrder theNullOrder, 1138 boolean theUseAggregate) { 1139 OrderObject.Dir direction = theTheAscending ? OrderObject.Dir.ASCENDING : OrderObject.Dir.DESCENDING; 1140 Object columnToOrder = theTheColumnValueNormalized; 1141 if (theUseAggregate) { 1142 if (theTheAscending) { 1143 columnToOrder = FunctionCall.min().addColumnParams(theTheColumnValueNormalized); 1144 } else { 1145 columnToOrder = FunctionCall.max().addColumnParams(theTheColumnValueNormalized); 1146 } 1147 } 1148 OrderObject orderObject = new OrderObject(direction, columnToOrder); 1149 orderObject.setNullOrder(theNullOrder); 1150 mySelect.addCustomOrderings(orderObject); 1151 } 1152 1153 /** 1154 * If set to true (default is false), force the generated SQL to start 1155 * with the {@link ca.uhn.fhir.jpa.model.entity.ResourceTable HFJ_RESOURCE} 1156 * table at the root of the query. 1157 * <p> 1158 * This seems to perform better if there are multiple joins on the 1159 * resource ID table. 1160 */ 1161 public void setNeedResourceTableRoot(boolean theNeedResourceTableRoot) { 1162 myNeedResourceTableRoot = theNeedResourceTableRoot; 1163 } 1164}