slick.util.AsyncExecutor$$anon$1$$anon$2@3b043815[Running, pool size = 10, active threads = 10, queued tasks = 1000, completed tasks = 355]
case class MyId(value: String) extends AnyVal
. Then I have two tables - one has id: Rep[MyId]
, the second one has id: Rep[String]
. Is there a way to compare these two like this tableA.id === tableB.id
, apart from these methods:tableA.id.asColumnOf[String] === tableB.id
- the resulting sql query doesn't use an index when it's supposed totableA.id.asInstanceOf[Rep[String]] === tableB.id
- works but dangerousHi! I have a database which uses PostgresProfile
. It has a TIMESTAMP
column. And I have to insert some values using sqlu
-syntax. What is a correct way to transform java.util.Instant
to String
? I've discrovered two ways: (1) PostgresProfile.columnTypes.instantType.valueToSQLLiteral
, and (2) PostgresProfile.columnTypes.instantType.serializeFiniteTime
. The first expression is java.sql.Timestamp#toSttring
, whereas the second is java.util.Instant#toString
, and they differ in timezones. Which one should I use to make my insertion consistent with other insertions via +=
, insertOrUpdate
, etc?
Some links:
Highlight: I've tried the first and the second way, and it seems to me that the answer is two: PostgresProfile.columnTypes.instantType.serializeFiniteTime
. But I find it a bit strange, because the first one looks more suitable according to naming and its point of declaration.
wondering if there is an example of mapping value types, e.g.
case class SomeTypedId(val underlying: UUID) extends AnyVal
I want to map the UUID to a column, e.g. something like
def id: Rep[UUID] = column[UUID]("id", O.AutoInc, O.PrimaryKey)
Is there an established pattern for this?
hi all,
val subQuery = some_great_table
.groupBy(_.queryId)
.map {
case (queryId, group) =>
(queryId, group.filter(_.isEnabled).length, group.filter(!_.isEnabled).length)
}
When i attempt to use this to join with other data, it fails with following exception:
slick.SlickTreeException: Cannot convert node to SQL Comprehension
| Path s5._2 : Vector[t2<{s3: String', s4: Boolean'}>]
at slick.compiler.MergeToComprehensions.$anonfun$convert$60(MergeToComprehensions.scala:179)
at scala.Option.getOrElse(Option.scala:189)
at slick.compiler.MergeToComprehensions.convertBase$1(MergeToComprehensions.scala:179)
at slick.compiler.MergeToComprehensions.$anonfun$convert$59(MergeToComprehensions.scala:173)
at slick.compiler.MergeToComprehensions.$anonfun$convert$59$adapted(MergeToComprehensions.scala:173)
at slick.compiler.MergeToComprehensions.mergeCommon(MergeToComprehensions.scala:356)
at slick.compiler.MergeToComprehensions.mergeFilterWhere$1(MergeToComprehensions.scala:173)
at slick.compiler.MergeToComprehensions.mergeGroupBy$1(MergeToComprehensions.scala:168)
at slick.compiler.MergeToComprehensions.$anonfun$convert$36(MergeToComprehensions.scala:103)
`
One workaround i was thinking was:
val subQuery = savedQueryCronTabTable
.groupBy(e => (e.queryId, e.isEnabled))
.map {
case ((queryId, isEnabled), group) => (queryId, isEnabled, group.length)
}
but this produces 2 rows, i wish to be able to have isEnabled and disabled counts in 1 row
class SavedQueryCronDefinitionTable(tag: Tag) extends Table[SavedQueryCronDefinition](tag, "saved_query_cron_tab") {
def * =
(
queryId,
cronExpression,
tabIdentifier,
creation,
startDate,
endDate,
niceLevel,
executionJSON,
isEnabled
) <> (SavedQueryCronDefinition.tupled, SavedQueryCronDefinition.unapply)
def tabIdentifier: Rep[UUID] = column[UUID]("tab_identifier")
def creation: Rep[LocalDateTime] = column[LocalDateTime]("creation")
def startDate: Rep[Option[LocalDateTime]] = column[Option[LocalDateTime]]("start_date")
def endDate: Rep[Option[LocalDateTime]] = column[Option[LocalDateTime]]("end_date")
def niceLevel: Rep[Int] = column[Int]("niceness")
def executionJSON: Rep[ExecuteSavedQueryRequest] = column[ExecuteSavedQueryRequest]("execution_json")
def isEnabled: Rep[Boolean] = column[Boolean]("is_enabled")
def pk: PrimaryKey = primaryKey("saved_query_current_schedule_pkey", (queryId, cronExpression))
def queryId: Rep[String] = column[String]("query_id")
def cronExpression: Rep[String] = column[String]("cron_expression")
}
Rep[Boolean]
type which is isEnabled
val joinedResult: ResultType = for {
(((savedQuery, resultInfo), uddsInfo), cronTabInfo) <- joinQuery
.joinLeft(queryResultMetadataTable)
.on(_.queryId === _.queryId)
.joinLeft(userDefinedDataSourceTable)
.on(_._1.queryId === _.savedQueryId)
.joinLeft(subQuery)
.on(_._1._1.queryId === _._1)
} yield (savedQuery, resultInfo, uddsInfo, cronTabInfo)
I haven't tested to actually run the query, but doing
val my_great_table = TableQuery[SavedQueryCronDefinitionTable]
my_great_table
.map(elem => (elem.queryId, Case If elem.isEnabled Then 1 Else 0))
.groupBy(_._1)
.map {
case (id, elems) => (id, elems.map(_._2).sum, elems.length - elems.map(_._2).sum)
}
.result
.statements
.foreach(println)
gives the statement
select "query_id", sum((case when "is_enabled" then 1 else 0 end)), count(1) - sum((case when "is_enabled" then 1 else 0 end)) from "saved_query_cron_tab" group by "query_id"
which, I believe, is what you want.
Hi all,
I have following SQL Query
SELECT o.* FROM resource_changes o INNER JOIN (
select min(id) as id from resource_changes i where sent = FALSE AND updated_at > '2020-04-18' group by resource_uuid
) t ON o.id = t.id;
I'd like to reproduce in Slick, I've tried
val q = for {
idResult <- eventsTable
.sortBy(_.id.asc)
.sortBy(_.updatedAt.asc)
.filter(_.sent === sent)
.filter(_.updatedAt > Instant.now().minus(30, ChronoUnit.DAYS))
.groupBy(_.resourceUuid)
.map(_._2.map(_.id).min)
event <- eventsTable.filter(_.id === idResult)
} yield event
q.result.statements.foreach(println)
q.result
but that produces
SELECT *
FROM
(SELECT
MIN(x3.x4) AS x5
FROM
(SELECT
`id` AS x4,
`updated_at` AS x6,
`sent` AS x7,
`resource_uuid` AS x8
FROM
`resource_changes`
ORDER BY `updated_at` , `id`) x3
WHERE
(x3.x7 = FALSE)
AND (x3.x6 > { ts '2021-04-19 12:43:53.17752' })
GROUP BY x3.x8) x9,
`resource_changes` x2
WHERE
x2.`id` = x9.x5;
https://stackoverflow.com/questions/394853/sql-inner-join-syntax
I think
WHERE
x2.`id` = x9.x5;
is inner join
edit: ok, I looked around a bit more and I'm not so sure anymore
eventsTable.join([your sub query]).on(_.id === _)
you could also turn on these loggers
<logger name="slick.jdbc.JdbcBackend.statement" level="DEBUG" />
<logger name="slick.jdbc.JdbcBackend.parameter" level="DEBUG" />
<logger name="slick.jdbc.JdbcBackend.statementAndParameter" level="DEBUG" />
to get cleaner sql
root 2021-05-19T20:40:37.148 [http4s-demo.database-5] DEBUG s.j.J.statement - Preparing insert statement (returning: book_id,book_title,book_isbn,author_id,created_at): insert into "books" ("book_title","book_isbn","author_id") values (?,?,?)