Using Slick to Filter Data in a Select Statement: Advanced Techniques and Best Practices for Efficient Database Access

Using Slick to Filter Data in a Select Statement

In this article, we will explore how to use the Slick library to filter data in a select statement. We will cover the basics of Slick, its syntax, and some advanced techniques for filtering data.

Introduction to Slick

Slick is a popular Scala library used for SQL database access. It provides a simple way to interact with databases using a familiar object-oriented syntax. In this article, we will focus on the Postgres profile, which is one of the most widely used profiles in Slick.

Defining Tables and Joining Them

Before we can start filtering data, we need to define our tables and join them together. This is done using the Table trait provided by Slick.

case class Seller(id: Long)
case class Produce(name: String, sellerId: Long, importerId: Long, created: LocalDateTime)

class Sellers(tag: Tag) extends Table[Seller](tag, "Sellers") {
  def id = column[Long]("ID", O.PrimaryKey)
  def * = id &lt;&gt; (Seller.apply, Seller.unapply)
}

class Produces(tag: Tag) extends Table[Produce](tag, "Produce") {
  def name = column[String]("NAME", O.PrimaryKey)
  def sellerId = column[Long]("SellersID")
  def importerId = column[Long]("IMPORTERID")
  def created = column[LocalDateTime]("CREATED")
  def * = (name, sellerId, importerId, created) &lt;&gt; (Produce.tupled, Produce.unapply)
}

val sellers = TableQuery[Sellers]
val produces = TableQuery[Produces]

sellers.join(produces).on(_.id === _.sellerId)

In this example, we define two tables: Seller and Produce. We then use the TableQuery trait to get instances of these tables.

We join the two tables together using the join method. The on method is used to specify the condition for joining the two tables.

Filtering Data

Now that we have joined our tables, we can filter the data using the filter method.

.filter { case (s, p) => p.sellerId === 276L }

In this example, we use the filter method to only include rows where the sellerId column is equal to 276.

Grouping Data

We can also group our data using the groupBy method.

.groupBy { case (s, p) => s.id }

In this example, we use the groupBy method to group our data by the id column of the Seller table.

Mapping Data

Once we have grouped and filtered our data, we can map it to a new structure using the map method.

.map { case (sid, group) =>
  (
    sid,
    group
      .filter { case (s, p) => p.created > ldt2019 }
      .map { case (s, p) => p.importerId }
      .distinct.length,
    group
      .filter { case (s, p) => p.name === "Apple" && p.created > ldt2018 }
      .map { case (s, p) => p.importerId }
      .distinct.length
  )
}

In this example, we use the map method to create a new structure that includes two columns: one for the number of distinct importer IDs where the date is greater than ldt2019, and another for the number of distinct importer IDs where the name is “Apple” and the date is greater than ldt2018.

Putting it All Together

Here’s the complete example:

import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
import slick.jdbc.PostgresProfile.api._

case class Seller(id: Long)
case class Produce(name: String, sellerId: Long, importerId: Long, created: LocalDateTime)

class Sellers(tag: Tag) extends Table[Seller](tag, "Sellers") {
  def id = column[Long]("ID", O.PrimaryKey)
  def * = id &lt;&gt; (Seller.apply, Seller.unapply)
}

class Produces(tag: Tag) extends Table[Produce](tag, "Produce") {
  def name = column[String]("NAME", O.PrimaryKey)
  def sellerId = column[Long]("SellersID")
  def importerId = column[Long]("IMPORTERID")
  def created = column[LocalDateTime]("CREATED")
  def * = (name, sellerId, importerId, created) &lt;&gt; (Produce.tupled, Produce.unapply)
}

val sellers = TableQuery[Sellers]
val produces = TableQuery[Produces]

val dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
val ldt2019 = LocalDateTime.parse("2019-04-30 16:38:00", dtf)
val ldt2018 = LocalDateTime.parse("2018-01-30 16:38:00", dtf)

sellers.join(produces).on(_.id === _.sellerId)
  .filter { case (s, p) => p.sellerId === 276L }
  .groupBy { case (s, p) => s.id }
  .map { case (sid, group) =>
    (
      sid,
      group
        .filter { case (s, p) => p.created > ldt2019 }
        .map { case (s, p) => p.importerId }
        .distinct.length,
      group
        .filter { case (s, p) => p.name === "Apple" && p.created > ldt2018 }
        .map { case (s, p) => p.importerId }
        .distinct.length
    )
  }

libraryDependencies += "com.github.tminglei" %% "slick-pg" % "0.18.0"

This example demonstrates how to use Slick to filter data in a select statement.

Conclusion

In this article, we covered the basics of using Slick to filter data in a select statement. We discussed defining tables and joining them together, filtering data, grouping data, and mapping data to a new structure.

Last modified on 2025-05-07