private def processSearchParams()

in newswires/app/db/FingerpostWireEntry.scala [172:300]
107 lines of code
19 McCabe index (conditional complexity)

  private def processSearchParams(
      search: SearchParams
  ): List[SQLSyntax] = {
    val sourceFeedsQuery = search.suppliersIncl match {
      case Nil => None
      case sourceFeeds =>
        Some(
          sqls.in(
            sqls"upper(${syn.supplier})",
            sourceFeeds.map(feed => sqls"upper($feed)")
          )
        )
    }

    val sourceFeedsExclQuery = search.suppliersExcl match {
      case Nil => None
      case sourceFeedsExcl =>
        val se = this.syntax("sourceFeedsExcl")
        val doesContainFeeds = sqls.in(
          sqls"upper(${se.supplier})",
          sourceFeedsExcl.map(feed => sqls"upper($feed)")
        )
        // unpleasant, but the sort of trick you need to pull
        // because "NOT IN (...)" doesn't hit an index.
        // https://stackoverflow.com/a/19364694
        Some(
          sqls"""|NOT EXISTS (
                 |  SELECT FROM ${FingerpostWireEntry as se}
                 |  WHERE ${syn.id} = ${se.id}
                 |    AND $doesContainFeeds
                 |)""".stripMargin
        )
    }

    val keywordsQuery = search.keywordIncl match {
      case Nil      => None
      case keywords =>
        // "??|" is actually the "?|" operator - doubled to prevent the
        // SQL driver from treating it as a placeholder for a parameter
        // https://jdbc.postgresql.org/documentation/query/#using-the-statement-or-preparedstatement-interface
        Some(
          sqls"""(${syn.content} -> 'keywords') ??| ${textArray(keywords)}"""
        )
    }

    val keywordsExclQuery = search.keywordExcl match {
      case Nil => None
      case keywords =>
        val ke = this.syntax("keywordsExcl")
        // "??|" is actually the "?|" operator - doubled to prevent the
        // SQL driver from treating it as a placeholder for a parameter
        // https://jdbc.postgresql.org/documentation/query/#using-the-statement-or-preparedstatement-interface
        val doesContainKeywords =
          sqls"(${ke.content}->'keywords') ??| ${textArray(keywords)}"
        // unpleasant, but the kind of trick you need to pull because
        // NOT [row] ?| [list] won't use the index.
        // https://stackoverflow.com/a/19364694
        Some(
          sqls"""|NOT EXISTS (
                 |  SELECT FROM ${FingerpostWireEntry as ke}
                 |  WHERE ${syn.id} = ${ke.id}
                 |    AND $doesContainKeywords
                 |)""".stripMargin
        )
    }

    val categoryCodesInclQuery = search.categoryCodesIncl match {
      case Nil => None
      case categoryCodes =>
        Some(
          sqls"${syn.categoryCodes} && ${textArray(categoryCodes)}"
        )
    }

    val categoryCodesExclQuery = search.categoryCodesExcl match {
      case Nil => None
      case categoryCodesExcl =>
        val cce = this.syntax("categoryCodesExcl")
        val doesContainCategoryCodes =
          sqls"${cce.categoryCodes} && ${textArray(categoryCodesExcl)}"

        Some(
          sqls"""|NOT EXISTS (
                 |  SELECT FROM ${FingerpostWireEntry as cce}
                 |  WHERE ${syn.id} = ${cce.id}
                 |    AND $doesContainCategoryCodes
                 |)""".stripMargin
        )
    }

    val dateRangeQuery = (search.start, search.end) match {
      case (Some(startDate), Some(endDate)) =>
        Some(
          sqls"${FingerpostWireEntry.syn.ingestedAt} BETWEEN CAST($startDate AS timestamptz) AND CAST($endDate AS timestamptz)"
        )
      case (Some(startDate), None) =>
        Some(
          sqls"${FingerpostWireEntry.syn.ingestedAt} >= CAST($startDate AS timestamptz)"
        )
      case (None, Some(endDate)) =>
        Some(
          sqls"${FingerpostWireEntry.syn.ingestedAt} <= CAST($endDate AS timestamptz)"
        )
      case _ => None
    }

    // grr annoying but broadly I think subjects(/categoryCodes) and keywords are the same "axis" to search on
    val clausesJoinedWithOr =
      List(
        keywordsQuery,
        categoryCodesInclQuery
      ).flatten match {
        case Nil => None
        case clauses =>
          Some(sqls.joinWithOr(clauses: _*))
      }

    List(
      clausesJoinedWithOr,
      keywordsExclQuery,
      search.text.map(query =>
        sqls"websearch_to_tsquery('english', $query) @@ ${FingerpostWireEntry.syn.column("combined_textsearch")}"
      ),
      sourceFeedsQuery,
      sourceFeedsExclQuery,
      dateRangeQuery,
      categoryCodesExclQuery
    ).flatten
  }