Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
    nafg
    @nafg
    Can you show the code and the error
    Raymond Te Hok
    @rtehok
    hello, I have an issue and asked here, maybe can someone in this forum help me ? maybe I am doing something wrong (probably), or maybe I am misunderstanding the way I am supposed to use slick ?
    jeejeeone
    @jeejeeone
    @nafg There is no error, the problem is that .transactionally method is not available
    Rohan Sircar
    @rohan-sircar
    you need to import profile api
    import profile.api._
    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:
    1. tableA.id.asColumnOf[String] === tableB.id - the resulting sql query doesn't use an index when it's supposed to
    2. tableA.id.asInstanceOf[Rep[String]] === tableB.id - works but dangerous
    3. 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
    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!!
    Ali Ustek
    @austek

    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
    Ali Ustek
    @austek
    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