(select * from table_name where name = 'some-name')
. The table contains 2 rows only. When I ran the query only one time, it rough took 50 ms to return the output. When I ran the same query for 5000 times using a loop, it took around 3000 ms to return for each query. Although preparing the query took only ~1 ms and executing the query took 30 ms roughly(which is same as when I ran the query only one time). So I want to understand from where the extra time in 3000ms is coming? I am using connection pool and running this query on local postgres instance
db.run()
method took 5000ms to complete. The preparation time and execution time is very less. Any idea here why it is taking 5000ms
I'm switching the test database from h2 to an embedded postgres. It is harder than I thought as it is uncovering some (circular?) table dependency issues:
When I run
db.run((Tables.Address.schema ++ Tables.Profile.schema).createIfNotExists).futureValue
I get
[info] The future returned an exception of type: org.postgresql.util.PSQLException, with message: ERROR: relation "profile" does not exist. (PersistenceTest.scala:56)
and when I switch the order
db.run((Tables.Profile.schema ++ Tables.Address.schema).createIfNotExists).futureValue
I get
[info] The future returned an exception of type: org.postgresql.util.PSQLException, with message: ERROR: relation "address" does not exist. (PersistenceTest.scala:56)
Any advice?
(I'm running 3.4.0-M1 since I ran into similar issues with dropIfExists which were solved by upgrading from 3.3.3 to 3.4.0)
Hello,
I have trouble using slick with distinct and sort by a distance (postgis in postgresSQL)
to run this query :
It generate a SELECT DISTINCT ON expressions must match initial ORDER BY expressions
but I can not see how to create a subquery for this
```
val queryCenterLimit = (for {
(((((((center, _), _), _), _), _), _), _) <- joinQueries
} yield (center.id, center.location))
.distinctOn(_._1)
.sortBy(r => location.bind.distance(r._2))
.drop(pagination.after.map(_.toInt).getOrElse(0))
.take(pagination.limit)
```
Do you see how I can solve this ?
Thanks for your help
What is the idiomatic way to handle failures on insert where the new record fails a constraint? E.g. duplicate name on a unique constraint, check constraint, etc?
Hopping on this question as well, is there an example of error handling with slick? I want to be able to distinguish general db errors with more specific errors (e.g. failed constraints)
query.map { case (a, b) => (a, b, None) }
None
is not a Rep
of course, so I can't do this
joinLeft
. Note that the None
is not a simple type, it is an Option[EntireTableClass]
, rather than int
or something
Rep.None
does not seem to work, because it is a table rather than a TypedType
@ArnaudT I have same question in postgresql;
tables.articleMaterials
.join(tables.articles).on(_.materialId === _.materialId)
.filter(_._1.ownerDomain === cmd.domainId)
.filterIf(cmd.beginTime > 0)(_._1.createTime > cmd.beginTime)
.filterIf(cmd.endTime < Timestamps.MaxValue)(_._1.createTime <= cmd.endTime)
.filterIf(cmd.ownerIds.nonEmpty)(_._1.ownerId.inSet(cmd.ownerIds))
.filterOpt(cmd.kw.filter(Strings.nonEmpty).map(it => s"%$it%"))((it, q) => it._1.remarks.like(q) || it._2.title.like(q))
.map(it => it._1)
.distinctOn(_.materialId)
.sortBy(_.modifyTime.desc)
.drop(cmd.skip)
.take(cmd.limit)
.result
this query works fine in mysql; in postgresql will cause a exception[SELECT DISTINCT ON expressions must match initial ORDER BY expressions] @nafg
I have a data model that consists of 2 entities, User
s and Event
s, and a mapping between them, EventInvitation
s. Here's a simplified look:
case class User(id: String, ...)
case class Event(id: String, ...)
case class EventInvitation(userId: String, eventId: String, ...)
I have created slick table definitions for the 3 entities as you might expect, with foreign keys from the invitation fields to the user and event ids in those respective tables. I would like to create a function that takes a user id, and returns all of the events to which that user is invited to, as well as all of the invitations for those events, so a signature similar to the following:
def getEventsForUser(userId: String): Seq[(Event, Seq[EventInvitation])]
I have come up with the following to create the DBIOAction
that seems to work from my limited testing so far:
type PopulatedEvent = (Event, Seq[EventInvitation])
def getEventsByUserAction(userId: String): DBIOAction[Seq[PopulatedEvent], NoStream, Effect.Read] =
val eventInvitationsQuery: Query[schema.EventInvitations, EventInvitation, Seq] = eventInvitationDao.getEventInvitationsByUserQuery(userId) // queries event invitations by user id
val eventsQuery = tableQuery filter { _.id in eventInvitationsQuery.map { _.eventId } } // here, `tableQuery` is the table query for the Events table, so this gets all events for that a user is invited to
for {
events <- eventsQuery.result
allInvites <- eventInvitationsQuery.result map { _.map { invite => (invite.eventId, invite) }} map { MultiDict.from[EventId, EventInvitation](_) } // using MultiDict from scala-collections-contrib (https://www.javadoc.io/doc/org.scala-lang.modules/scala-collection-contrib_2.13/latest/scala/collection/MultiDict.html), this essentially groups invitations by event id
} yield events map { event => (event, allInvites.get(event.id).toSeq) }
This seems to work from the limited testing I've done, but for some reason, feels a bit hacky to me. I would have to think this is a somewhat common use case (aggregating some entity and a collection of related entities together), so my guess is that someone has probably come up with a more efficient way to accomplish this than I just have. Does anyone here have ideas or examples on a better way to do this? I have left out the table definitions for brevity and because they are fairly standard as I can tell, but I can show those (or any other missing code) as well. Happy to clarify anything. Thanks!
i'm struggling with RejectedExecutionException
Caused by: java.util.concurrent.RejectedExecutionException: Task slick.basic.BasicBackend$DatabaseDef$$anon$3@369ff44b rejected from slick.util.AsyncExecutor$$anon$1$$anon$2@1db1c8bd[Terminated, pool size = 0, active threads = 0, queued tasks = 0, completed tasks = 13]
at java.base/java.util.concurrent.ThreadPoolExecutor$AbortPolicy.rejectedExecution(ThreadPoolExecutor.java:2055)
at java.base/java.util.concurrent.ThreadPoolExecutor.reject(ThreadPoolExecutor.java:825)
at java.base/java.util.concurrent.ThreadPoolExecutor.execute(ThreadPoolExecutor.java:1355)
at slick.util.AsyncExecutor$$anon$1$$anon$4.execute(AsyncExecutor.scala:161)
at slick.basic.BasicBackend$DatabaseDef.runSynchronousDatabaseAction(BasicBackend.scala:264)
at slick.basic.BasicBackend$DatabaseDef.runSynchronousDatabaseAction$(BasicBackend.scala:262)
at slick.jdbc.JdbcBackend$DatabaseDef.runSynchronousDatabaseAction(JdbcBackend.scala:37)
at slick.basic.BasicBackend$DatabaseDef.slick$basic$BasicBackend$DatabaseDef$$runInContextInline(BasicBackend.scala:241)
at slick.basic.BasicBackend$DatabaseDef.runInContextSafe(BasicBackend.scala:147)
at slick.basic.BasicBackend$DatabaseDef.runInContext(BasicBackend.scala:141)
at slick.basic.BasicBackend$DatabaseDef.runInContext$(BasicBackend.scala:140)
at slick.jdbc.JdbcBackend$DatabaseDef.runInContext(JdbcBackend.scala:37)
at slick.basic.BasicBackend$DatabaseDef.runInternal(BasicBackend.scala:76)
at slick.basic.BasicBackend$DatabaseDef.runInternal$(BasicBackend.scala:75)
at slick.jdbc.JdbcBackend$DatabaseDef.runInternal(JdbcBackend.scala:37)
at slick.basic.BasicBackend$DatabaseDef.run(BasicBackend.scala:73)
at slick.basic.BasicBackend$DatabaseDef.run$(BasicBackend.scala:73)
at slick.jdbc.JdbcBackend$DatabaseDef.run(JdbcBackend.scala:37)
I've tried increasing the queue size and the number of threads but it doesn't seem to help.
Hi All,
I'm trying to trying to translate this query to Scala Slick but I'm having a hard time.
select x2."retrieval_reference_no",
x2."transaction_timestamp",
x3."account_id",
x3."entry_code",
x3."amount",
x3."current",
x3."hold",
x3."pending",
x3."account_type",
(select coalesce(x4."description", x3."entry_code")
from "transaction_description" x4
where x4."entry_code" = x3."entry_code"),
x2."response_code",
x2."response_detail",
x2."transaction_id",
x2."original_transaction_id",
x2."transaction_type"
from "transaction" x2,
"transaction_history" x3
where ((x2."transaction_id" = x3."transaction_id") and (x2."tenant_id" = x3."tenant_id"))
and (((x3."tenant_id" = 'SampleTenantId') and (x3."account_id" = 'SrcAcctId')))
order by x2."transaction_timestamp" desc
Here is my attempt to translate this to Slick Query.
def getEntryDescription(
entryCode: Rep[String]
): Query[Rep[String], String, Seq] = {
transactionDescriptionQuery
.filter(_.entryCode === entryCode)
.map(row => row.description.ifNull(entryCode))
}
val whereCondition = for {
(txn, history) <- joinHistoryTables
if history.tenantId === params.tenantId && history.accountId === params.accountId
description <- getEntryDescription(history.entryCode)
} yield {
(txn, history, description)
}
whereCondition
.sortBy { case (txn, _) => txn.transactionTimestamp.desc }
.map { case (txn, history, description) =>
(
txn.retrievalReferenceNo,
txn.transactionTimestamp,
history.accountId,
history.entryCode,
history.amount,
history.current,
history.hold,
history.pending,
history.accountType,
description,
txn.responseCode,
txn.responseDetail,
txn.transactionId,
txn.originalTransactionId,
txn.transactionType
) <> (TransactionHistoryDetails.tupled, TransactionHistoryDetails.unapply)
}
.drop(params.offset)
.take(params.limit)
But this give me:
select x2."retrieval_reference_no",
x2."transaction_timestamp",
x3."account_id",
x3."entry_code",
x3."amount",
x3."current",
x3."hold",
x3."pending",
x3."account_type",
coalesce(x4."description", x3."entry_code")),
x2."response_code",
x2."response_detail",
x2."transaction_id",
x2."original_transaction_id",
x2."transaction_type"
from "transaction" x2,
"transaction_history" x3
where ((x2."transaction_id" = x3."transaction_id") and (x2."tenant_id" = x3."tenant_id"))
and (((x3."tenant_id" = 'SampleTenantId') and (x3."account_id" = 'SrcAcctId'))) and x4."entry_code" = x3."entry_code"
order by x2."transaction_timestamp" desc
Table.filter(_.id === parameterId)
. I am just trying to confirm if I am correct or not.
Hi,
I'm developing a scala web app using Slick.
However, I face a problem that I can't resolve.
I have no idea what the cause is.
16:21:11 % sbt clean run
[info] welcome to sbt 1.6.2 (Eclipse Adoptium Java 17.0.4)
[info] loading settings for project openreports_scala-build from plugins.sbt ...
[info] loading project definition from /Users/ijufumi/workspaces/ijufumi/production/openreports_scala/project
[info] loading settings for project root from build.sbt ...
[info] set current project to Open Report API (in build file:/Users/ijufumi/workspaces/ijufumi/production/openreports_scala/)
[success] Total time: 0 s, completed Aug 14, 2022, 4:30:47 PM
[info] compiling 34 Scala sources to /Users/ijufumi/workspaces/ijufumi/production/openreports_scala/target/scala-2.13/classes ...
[error] /Users/ijufumi/workspaces/ijufumi/production/openreports_scala/src/main/scala/jp/ijufumi/openreports/entities/Member.scala:47:7: value <> is not a member of (slick.lifted.Rep[Option[Int]], slick.jdbc.PostgresProfile.api.Rep[String], slick.jdbc.PostgresProfile.api.Rep[String], slick.jdbc.PostgresProfile.api.Rep[String], slick.jdbc.PostgresProfile.api.Rep[String], slick.jdbc.PostgresProfile.api.Rep[String], slick.jdbc.PostgresProfile.api.Rep[java.sql.Timestamp], slick.jdbc.PostgresProfile.api.Rep[java.sql.Timestamp], slick.jdbc.PostgresProfile.api.Rep[Long])
[error] possible cause: maybe a semicolon is missing before `value <>`?
[error] ) <> (Member.tupled, Member.unapply)
[error] ^
[error] /Users/ijufumi/workspaces/ijufumi/production/openreports_scala/src/main/scala/jp/ijufumi/openreports/entities/Member.scala:47:33: missing argument list for method unapply in object Member
[error] Unapplied methods are only converted to functions when a function type is expected.
[error] You can make this conversion explicit by writing `unapply _` or `unapply(_)` instead of `unapply`.
[error] ) <> (Member.tupled, Member.unapply)
[error] ^
[error] two errors found
[error] (Compile / compileIncremental) Compilation failed
[error] Total time: 3 s, completed Aug 14, 2022, 4:30:49 PM
My code is here.
Can someone please help me?
Thanks
import slick.jdbc.PostgresProfile.api.{timestampColumnType => _, _}
timestampColumnType
with your implementation