slick/slick

Scala Language Integrated Connection Kit

Rohan Sircar

@rohan-sircar

assuming you know how to get it?

jeejeeone

@jeejeeone

Ok thanks. Well no idea but I can work with this.

Rohan Sircar

@rohan-sircar

val profile: JdbcProfile = slick.jdbc.PostgresProfile

every database backend has it's own version of profile

jeejeeone

@jeejeeone

Alright cool easy fix, it was available already

Thanks! Though I have to say this was pretty frustrating :D Maybe I miss something in the docs

Antoine Doeraene

@sherpal

is it possible to make recursive queries (in postgresql) with slick?

Raymond Te Hok

@rtehok

hello, from this old SO post does anyone has this kind of error with slick 3.3.3 ?

slick.util.AsyncExecutor$$anon$1$$anon$2@3b043815[Running, pool size = 10, active threads = 10, queued tasks = 1000, completed tasks = 355]

2 replies

Andrzej Sołtysik

@asoltysik

Hi, I have a value class like this: 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 to
tableA.id.asInstanceOf[Rep[String]] === tableB.id - works but dangerous
Making tableB's id into value class too - it's not possible in this case

Rohan Sircar

@rohan-sircar

@asoltysik maybe with slick-refined you could do this?

1 reply

Rohan Sircar

@rohan-sircar

does Rep have a map method? then you could tableA.id.map(_.value) ?

Evgeny Shlykov

@eshlykov

Hi! 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.

Paweł Kaczor

@pawelkaczor

Hey, I just implemented generic, reusable and customizable raw sql query result to case class mapper of type slick.jdbc.GetResult[YourClass].
You obtain the mapper by calling #getResult(YourClass.curried, YourClass.tupled)
Anyone interested? Or perhaps I've just rediscovered the wheel?

2 replies

mjabari

@mjabari

@pawelkaczor it'd be in interesting. Please put the project link

Craig Tataryn

@ctataryn

Hello, I was wondering if anyone has experience in being able to persist emojis to MySql from Slick? I created an SO question for this, figured I'd check here as well in case anyone who's online might be able to answer.

Andy Czerwonka

@andyczerwonka

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?

Andy Czerwonka

@andyczerwonka

ah, this article seems to cover the case https://softwaremill.com/comparing-scala-relational-database-access-libraries/

Anirudh Vyas

@AnirudhVyas

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)
`

any suggestions would be good

Anirudh Vyas

@AnirudhVyas

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

Rohan Sircar

@rohan-sircar

for the time being maybe you could just use plain sql query?

Anirudh Vyas

@AnirudhVyas

can i combine sql with already existing slick stuff - some old code where I need to add this

Rohan Sircar

@rohan-sircar

I've never used it myself, only read about it. But if the interpolators return DBIO then you could compose it with other DBIOs in transactions

If it doesn't return DBIO, then probably not

Antoine Doeraene

@sherpal

Could you post the definition of some_great_table? Because what you do should work

Anirudh Vyas

@AnirudhVyas

  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")
    }

i think the problem is that you cannot filter on Rep[Boolean] type which is isEnabled

Anirudh Vyas

@AnirudhVyas

this is how that subquery is used:

 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)

ignore details of these tables for clarity - part of the thing i dislike about this is joinLefts etc. but those need to exist ...

Antoine Doeraene

@sherpal

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.

Anirudh Vyas

@AnirudhVyas

let me give it a try, thank you!

Anirudh Vyas

@AnirudhVyas

thanks @sherpal that definitely works

Antoine Doeraene

@sherpal

Cool, glad I could help. In the first map you could also add a third element with the if the other way around, and sum that column instead in the group by. It "might" improve performance...

Anirudh Vyas

@AnirudhVyas

hmm I'll give that a try thanks!

Min Joo Kang

@mandoolala

hello, I found that when upserting the column entries of BigDecimal, somehow the number is automatically rounded up.. how can I keep the original value with the original decimal places?

Antoine Doeraene

@sherpal

@mandoolala Are you sure the problem is with Slick? Could it be in the database itself?

Min Joo Kang

@mandoolala

yeah just realised it is the database itself! thanks!!

Ghost

@ghost~5cc594f1d73408ce4fbee018

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;

Aim is to get the earliest unsent entries grouped by resource_uuid

Antoine Doeraene

@sherpal

when you do your for comprehension like that, you are making a cross join. What you want is an inner join, so instead of

event <- eventsTable.filter(_.id === idResult)

you should do

event <- eventsTable
if event.id === idResult.id

Ghost

@ghost~5cc594f1d73408ce4fbee018

that actually produced the same SQL

Rohan Sircar

@rohan-sircar

slick generated query looks ok to me, can't be sure due to all the aliases but the format is similar. Are you getting different results from the two of them?

Rohan Sircar

@rohan-sircar

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

Antoine Doeraene

@sherpal

ah yes indeed it's the same, but as Rohan says it's the same. If think if you want the inner join syntax, you have to do
eventsTable.join([your sub query]).on(_.id === _)

Rohan Sircar

@rohan-sircar

Actually these examples are equivalent and neither is a cartesian product. A cartesian product is returned when you join two tables without specifying a join condition, such as in
select *
from t1,t2

hmm

Ghost

@ghost~5cc594f1d73408ce4fbee018

I'll need the inner join as the table is quite large sub-select times out

Rohan Sircar

@rohan-sircar

the point is, that is inner join

ok, I think I read quite a while back that bugs in the db server might cause a cross join so explicit inner join is a best practice

so you can do
eventsTable.join([your sub query]).on(_.id === _)
as Antoine suggested

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 (?,?,?)

Where communities thrive

People

Repo info

Activity