Tried passing multiple values to a query param to filter a column values that are in the list of values in query param.
In Query Filter tried the "in" operator. Its not working. Below is sample query param with multiple filters to filter by that I am trying.
?queryParam=value1,value2
Please advise how to filter based on multiple values.
Discussion posts and replies are publicly visible
We need more context here. Is this about one of these cases:
- a queryFilter for queryRecordType
- about a third party API that you want to pass values to
- or about an API that you create and you want to get values passed?
And try to explain this like to a three year old.
we have 2 Appian application and get value though web api call. One application require, all records from other application by passing multiple Ids as query parameter.
passing multiple value is not working, for single ids working fine.
I feel like a 120 years old caveman !?!?!?
I try to translate this into something usable.
- I want to create a GET API in Appian that accepts multiple comma-separated IDs in a single query parameter
- I need to query a list of records for which the primary key matches the passed IDs
- I then want to return the fetched record data in JSON format
Is that correct? If no, please elaborate.
And I need to see your implementation!
yes, that's correct
?deliveryId=%7B3%2C2%7D
Do you think that I can help you without seeng what that query expression is doing !?!?
So you pass a string like "{1,2,3}" into a queryFilter using the "in" operator. What data type is that rule input?
I suggest to parse the string into a list of integers before passing it into the query.
a!localVariables( local!input: "{1,2,3}", tointeger(split(extract(local!input, "{", "}"), ",")) )
Thanks a Lot its working
#documentIdsfilelist package com.surescripts.formularydownloadapi.documentIdsfilelist import com.fasterxml.jackson.annotation.JsonProperty.Access import com.fasterxml.jackson.annotation.{JsonCreator, JsonProperty} import io.circe.derivation.{deriveDecoder, deriveEncoder} import io.circe.{Decoder, Encoder} @JsonCreator case class DocumentIdsFileList( @JsonProperty(value = "name", access = Access.READ_WRITE) name: String, @JsonProperty(value = "pbm", access = Access.READ_WRITE) pbm: String, @JsonProperty(value = "pbmtype", access = Access.READ_WRITE) pbmtype: String, @JsonProperty(value = "subtype", access = Access.READ_WRITE) subtype: Option[String], @JsonProperty(value = "size", access = Access.READ_WRITE) size: Int, @JsonProperty(value = "created", access = Access.READ_WRITE) created: String, @JsonProperty(value = "lastmodified", access = Access.READ_WRITE) lastModified: String, @JsonProperty(value = "effective_date", access = Access.READ_WRITE) effectiveDate: String ) object DocumentIdsFileList { implicit val decoder: Decoder[DocumentIdsFileList] = deriveDecoder[DocumentIdsFileList] implicit val encoder: Encoder[DocumentIdsFileList] = deriveEncoder[DocumentIdsFileList] } #documentIdsfilelistRepo package com.surescripts.formularydownloadapi.documentIdsfilelist import com.surescripts.formularydownloadapi.Env import com.surescripts.scala.cats.data.DataRepository import doobie.implicits.toSqlInterpolator import io.github.resilience4j.bulkhead.annotation.Bulkhead import io.github.resilience4j.bulkhead.annotation.Bulkhead.Type import io.github.resilience4j.circuitbreaker.annotation.CircuitBreaker import io.github.resilience4j.ratelimiter.annotation.RateLimiter import io.github.resilience4j.retry.annotation.Retry import io.github.resilience4j.timelimiter.annotation.TimeLimiter import org.springframework.stereotype.Repository import java.time.LocalDate import java.time.format.DateTimeFormatter import java.util.concurrent.CompletableFuture import scala.util.Try @Repository class DocumentIdsFileListRepository extends DataRepository { @Bulkhead(name = "default-bulkhead-db", `type` = Type.THREADPOOL) @TimeLimiter(name = "default-time-limiter-db") @CircuitBreaker(name = "default-circuit-breaker-db") @RateLimiter(name = "default-rate-limiter-db") @Retry(name = "default-retry-db") private val today: String = DateTimeFormatter.BASIC_ISO_DATE.format(LocalDate.now) def findDocumentIdsFileList(pbm: String, pbmtype: String, subtype: String, createdAfter: String, lastModifiedAfter: String, drugDb: String, version: String)(implicit env: Env): CompletableFuture[Try[List[DocumentIdsFileList]]] = { val baseQuery = sql""" select fw.document_name as name, pmm.PART_NM as pbm, fl.type as pbmtype, fl.sub_type, fw.document_size as size1, fw.creation_date as created, fw.version, CASE WHEN fw.last_modified_date < fw.creation_date THEN fw.creation_date ELSE fw.last_modified_date END as lastmodified, fl.effective_date as effectiveDate from formulary_webdav fw inner join formulary_load fl on fw.formulary_load_id = fl.formulary_load_id and fl.management_status = 'A' inner join pmm_part pmm on fl.publisher = pmm.part_uid where exists(select * from pmm_service_contract sc inner join tp_service_direction sd on sc.service_id = sd.service_id and sd.direction = 'R' inner join tp_trx_in_service tis on sd.service_direction_id = tis.service_direction_id and tis.trx_type = 'FRMDST' where (pmm.pid = sc.pid1 or pmm.pid = sc.pid2) and sc.status = 'A') and fw.version = 60 """ val pbmFilter = if (pbm != null) sql""" and pmm.part_nm = $pbm""" else fr"" val pbmtypeFilter = if (pbmtype != null) sql""" and fl.type = $pbmtype""" else fr"" val subtypeFilter = if (subtype != null) sql""" and fl.sub_type = $subtype""" else fr"" val createdAfterFilter = if (createdAfter != null) sql""" and fw.creation_date >= $createdAfter""" else fr"" val lastModifiedAfterFilter = if (lastModifiedAfter != null) sql""" and fw.last_modified_date >= $lastModifiedAfter""" else fr"" val dateFilter = fr" and fl.effective_date <= $today" val fullQuery = baseQuery ++ pbmFilter ++ pbmtypeFilter ++ subtypeFilter ++ createdAfterFilter ++ lastModifiedAfterFilter ++ dateFilter ++ fr" order by fl.effective_date desc" executeQueryFuture[DocumentIdsFileList](fullQuery) } } #documentIdsfilelistService package com.surescripts.formularydownloadapi.documentIdsfilelist import cats.effect.IO import com.surescripts.formularydownloadapi.Env import com.surescripts.scala.cats.data.DataService import scala.util.Try case class DocumentIdsFileListService() extends DataService { def findDocumentIdsFileList(pbm: String, pbmtype: String, subtype: String, createdAfter: String, lastModifiedAfter: String, drugDb: String, version: String)(implicit env: Env): IO[Try[Option[DocumentIdsFileList]]] = singleQuery[DocumentIdsFileList](env.documentIdsFileListRepository.findDocumentIdsFileList(pbm, pbmtype, subtype, createdAfter, lastModifiedAfter, drugDb, version)) } #Controller package com.surescripts.formularydownloadapi import cats.effect.IO import cats.effect.unsafe.implicits.global import com.surescripts.formularydownloadapi.documentIdsfilelist.{DocumentIdsFileList, DocumentIdsFileListService} import io.circe.Json import io.circe.syntax.EncoderOps import io.swagger.v3.oas.annotations.media.{ArraySchema, Content, Schema} import io.swagger.v3.oas.annotations.responses.{ApiResponse, ApiResponses} import org.springframework.http.ResponseEntity import org.springframework.web.bind.annotation.{GetMapping, RequestMapping, RequestParam, RestController} import org.typelevel.log4cats.slf4j.Slf4jLogger import scala.util.{Failure, Success} import scala.concurrent.ExecutionContext @RestController @RequestMapping(Array("/formulary")) class Controller(env: Env)(implicit ec: ExecutionContext) { implicit val logger = Slf4jLogger.getLogger[IO] @ApiResponses(value = Array( new ApiResponse(responseCode = "200", description = "Found the lists", content = Array(new Content(mediaType = "application/json", schema = new Schema(implementation = classOf[DocumentIdsFileList])))), new ApiResponse(responseCode = "404", description = "Lists not found", content = Array(new Content())))) @GetMapping(value = Array("/lists")) def getDocumentIdsFileList(@RequestParam(required = false) pbm: String, @RequestParam(required = false) pbmtype: String, @RequestParam(required = false) subtype: String, @RequestParam(required = false) createdafter: String, @RequestParam(required = false) lastmodifiedafter: String): ResponseEntity[Json] = env.documentIdsFileListService.findDocumentIdsFileList(pbm, pbmtype, subtype, createdafter, lastmodifiedafter, "NONE", "60")(env).unsafeRunSync() match { case Success(lists) if lists.nonEmpty => ResponseEntity.ok().body(Map("lists" -> lists).asJson) case Success(_) => ResponseEntity.status(404).body("No lists found.".asJson) case Failure(e) => logger.error(e)("Error calling GET /lists") ResponseEntity.internalServerError().body("Internal server error".asJson) } @GetMapping(value = Array("/shutdown")) def shutdown(): IO[Unit] = IO { logger.info("Shutting down service.") System.exit(0) } } #Env package com.surescripts.formularydownloadapi import cats.effect.unsafe.implicits.global import com.surescripts.formularydownloadapi.documentIdsfilelist._ import com.surescripts.scala.cats.data.DataEnv import org.springframework.stereotype.Component /** * Application-specific Environment * <p>Note: The component cannot be named "env" because it collides with an existing Spring bean. * @param applicationContext Spring application context */ @Component(value = "applicationEnv") class Env extends DataEnv { lazy val documentIdsFileListRepository: DocumentIdsFileListRepository = getBean[DocumentIdsFileListRepository] lazy val documentIdsFileListService: DocumentIdsFileListService = getBean[DocumentIdsFileListService] } #Main package com.surescripts.formularydownloadapi import cats.effect._ import cats.effect.unsafe.implicits.global import com.surescripts.scala.cats.common.SpringConfiguration import io.micrometer.influx.InfluxMeterRegistry import org.springframework.boot.SpringApplication import org.springframework.boot.autoconfigure.SpringBootApplication import org.springframework.context.annotation.Import import org.springframework.scheduling.annotation.EnableScheduling import scala.concurrent.duration.DurationInt @EnableScheduling @SpringBootApplication @Import(Array(classOf[Env], classOf[SpringConfiguration], classOf[Config])) // Import Config class here case class Main() /** * Application entry point. */ object Main extends IOApp { def run(args: List[String]): IO[ExitCode] = { val applicationContext = SpringApplication.run(classOf[Main]) while (!ShutdownMonitor.shouldShutDown()) IO.sleep(1.second).unsafeRunSync() applicationContext.getBean(classOf[InfluxMeterRegistry]).close() applicationContext.close() global.shutdown() System.exit(ExitCode.Success.code) IO(ExitCode.Success) } } #Config package com.surescripts.formularydownloadapi import com.surescripts.formularydownloadapi.documentIdsfilelist.{DocumentIdsFileListRepository, DocumentIdsFileListService} import org.springframework.beans.factory.annotation.Value import org.springframework.context.annotation.{Bean, Configuration, PropertySource} import org.springframework.web.servlet.config.annotation.EnableWebMvc import scala.concurrent.ExecutionContext /*** * Spring Boot Configuration */ @EnableWebMvc @PropertySource(Array("classpath:surescriptsApplicationInfo.properties")) @Configuration class Config { @Value("${spring.application.name}") private val applicationName: String = null @Value("${spring.application.product}") private val applicationProduct: String = null @Value("${spring.datasource.url}") private val dataSourceUrl: String = null @Value("${spring.datasource.username}") private val dataSourceUsername: String = null @Value("${spring.datasource.password}") private val dataSourcePassword: String = null @Value("${spring.cloud.vault.uri}") private val vaultUri: String = null @Bean def documentIdsFileListRepository: DocumentIdsFileListRepository = new DocumentIdsFileListRepository @Bean def documentIdsFileListService: DocumentIdsFileListService = new DocumentIdsFileListService // Define the ExecutionContext bean @Bean def executionContext: ExecutionContext = ExecutionContext.global }
package com.surescripts.formularydownloadapi import com.surescripts.formularydownloadapi.documentIdsfilelist.DocumentIdsFileList import org.junit.jupiter.api.Assertions._ import org.junit.jupiter.api.Test import org.junit.jupiter.api.extension.ExtendWith import org.springframework.beans.factory.annotation.Autowired import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc import org.springframework.boot.test.context.SpringBootTest import org.springframework.http.MediaType import org.springframework.test.context.junit.jupiter.SpringExtension import org.springframework.test.web.servlet.MockMvc import org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get import org.springframework.test.web.servlet.result.MockMvcResultHandlers.print import org.springframework.test.web.servlet.result.MockMvcResultMatchers.{jsonPath, status} @SpringBootTest @AutoConfigureMockMvc @ExtendWith(Array(classOf[SpringExtension])) class ControllerTest { @Autowired private var mockMvc: MockMvc = _ @Test def testGetDocumentIdsFileList(): Unit = { this.mockMvc.perform(get("/formulary/lists") .param("pbm", "ROLLUP_PBM_30") .param("pbmtype", "AL") .contentType(MediaType.APPLICATION_JSON)) .andDo(print()) .andExpect(status().isOk()) .andExpect(jsonPath("$.lists").isArray) .andExpect(jsonPath("$.lists[0].pbm").value("ROLLUP_PBM_30")) .andExpect(jsonPath("$.lists[0].pbmtype").value("AL")) } @Test def testGetDocumentIdsFileListNotFound(): Unit = { this.mockMvc.perform(get("/formulary/lists") .param("pbm", "NON_EXISTENT_PBM") .param("pbmtype", "NON_EXISTENT_TYPE") .contentType(MediaType.APPLICATION_JSON)) .andDo(print()) .andExpect(status().isNotFound()) } }
Now im getting the below error with the test cases- testGetDocumentIdsFileList() java.lang.AssertionError: Expected an array at JSON path "$.lists" but found: {name=ALId0, pbm=ROLLUP_PBM_30, pbmtype=AL, subtype=null, size=180, created=2024-07-08 04:16:12.282, lastModified=60, effectiveDate=2024-07-08 04:16:12.282} Expected: an instance of java.util.List but: <{name=ALId0, pbm=ROLLUP_PBM_30, pbmtype=AL, subtype=null, size=180, created=2024-07-08 04:16:12.282, lastModified=60, effectiveDate=2024-07-08 04:16:12.282}> is a java.util.LinkedHashMap at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20) at org.springframework.test.util.JsonPathExpectationsHelper.assertValueIsArray(JsonPathExpectationsHelper.java:166) at org.springframework.test.web.servlet.result.JsonPathResultMatchers.lambda$isArray$12(JsonPathResultMatchers.java:226) at org.springframework.test.web.servlet.MockMvc$1.andExpect(MockMvc.java:214) at com.surescripts.formularydownloadapi.ControllerTest.testGetDocumentIdsFileList(ControllerTest.scala:32) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) Can we take the example of below test class, @Test def testGetPharmacy(): Unit = { val responseJson: String = Pharmacy("0716780", "CVS PHARMACY", 118, Instant.parse("2000-01-01T06:00:00.00Z"), Some(3), Some(338508)).asJson.noSpaces this.mockMvc.perform(get("/pharmacy-cache-updater/api/v1/pharmacy") .header("Authorization", "bearer " ++ authTokenHandler.createToken(new JwtClaims())) .queryParam("ncpdpid", "0716780") .contentType(MediaType.APPLICATION_JSON)) .andDo(print()) .andExpect(status().isOk()) .andExpect(content().string(responseJson))
package com.surescripts.formularydownloadapi import com.surescripts.formularydownloadapi.documentIdsfilelist.DocumentIdsFileList import io.circe.syntax._ import org.junit.jupiter.api.Assertions._ import org.junit.jupiter.api.Test import org.junit.jupiter.api.extension.ExtendWith import org.springframework.beans.factory.annotation.Autowired import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc import org.springframework.boot.test.context.SpringBootTest import org.springframework.http.MediaType import org.springframework.test.context.junit.jupiter.SpringExtension import org.springframework.test.web.servlet.MockMvc import org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get import org.springframework.test.web.servlet.result.MockMvcResultHandlers.print import org.springframework.test.web.servlet.result.MockMvcResultMatchers.{content, status} @SpringBootTest @AutoConfigureMockMvc @ExtendWith(Array(classOf[SpringExtension])) class ControllerTest { @Autowired private var mockMvc: MockMvc = _ @Test def testGetDocumentIdsFileList(): Unit = { val expectedResponse = Map( "lists" -> List( DocumentIdsFileList( name = "ALId0", pbm = "ROLLUP_PBM_30", pbmtype = "AL", subtype = None, size = 180, created = "2024-07-08 04:16:12.282", lastModified = "60", effectiveDate = "2024-07-08 04:16:12.282" ) ) ).asJson.noSpaces this.mockMvc.perform(get("/formulary/lists") .param("pbm", "ROLLUP_PBM_30") .param("pbmtype", "AL") .contentType(MediaType.APPLICATION_JSON)) .andDo(print()) .andExpect(status().isOk()) .andExpect(content().string(expectedResponse)) } @Test def testGetDocumentIdsFileListNotFound(): Unit = { this.mockMvc.perform(get("/formulary/lists") .param("pbm", "NON_EXISTENT_PBM") .param("pbmtype", "NON_EXISTENT_TYPE") .contentType(MediaType.APPLICATION_JSON)) .andDo(print()) .andExpect(status().isNotFound()) } }