CODEWITHSUNDEEP
scalaslick-3.0
IUnknown
IUnknown

Reputation: 2636

combining slick queries into single query

Using Slick 3.1, how do I combine multiple Queries into a single query for the same type? This is not a join or a union, but combining query "segments" to create a single query request. These "segments" can be any individually valid query.

val query = TableQuery[SomeThingValid]

// build up pieces of the query in various parts of the application logic
val q1 = query.filter(_.value > 10)
val q2 = query.filter(_.value < 40)
val q3 = query.sortBy(_.date.desc)
val q4 = query.take(5)

// how to combine these into a single query ?
val finalQ = ??? q1 q2 q3 q4 ???

// in order to run in a single request
val result = DB.connection.run(finalQ.result)

EDIT: the expected sql should be something like:

SELECT * FROM "SomeThingValid" WHERE "SomeThingValid"."value" > 10 AND "SomeThingValid"."valid" < 40 ORDER BY "MemberFeedItem"."date" DESC LIMIT 5

Upvotes: 6

Views: 3042

Answers (4)

Ahmed Gamal
Ahmed Gamal

Reputation: 1706

It's Just a worth remark to mention here from the slick essential book, it seems that you might need to avoid combining multiple queries in one single query.

Combining actions to sequence queries is a powerful feature of Slick. However, you may be able to reduce multiple queries into a single database query. If you can do that, you’re probably better off doing it.

Upvotes: 0

danielnixon
danielnixon

Reputation: 4268

val q1 = query.filter(_.value > 10)
val q2 = q1.filter(_.value < 40)
val q3 = q2.sortBy(_.date.desc)
val q4 = q3.take(5)

I think you should do something like the above (and pass around Querys) but if you insist on passing around query "segments", something like this could work:

type QuerySegment = Query[SomeThingValid, SomeThingValid, Seq] => Query[SomeThingValid, SomeThingValid, Seq]

val q1: QuerySegment = _.filter(_.value > 10)
val q2: QuerySegment = _.filter(_.value < 40)
val q3: QuerySegment = _.sortBy(_.date.desc)
val q4: QuerySegment = _.take(5)

val finalQ = Function.chain(Seq(q1, q2, q3, q4))(query)

Upvotes: 7

Lukasz Zuchowski
Lukasz Zuchowski

Reputation: 258

I think, it should work. But I didn't test it yet.

val users = TableQuery[Users]
val filter1: Query[Users, User, Seq] = users.filter(condition1)
val filter2: Query[Users, User, Seq] = users.filter(condition2)
(filter1 ++ filter2).result.headOption

Upvotes: -2

nikiforo
nikiforo

Reputation: 424

I've used this "pattern" with slick2.0

val query = TableQuery[SomeThingValid]

val flag1, flag3 = false
val flag2, flag4 = true

val bottomFilteredQuery = if(flag1) query.filter(_.value > 10) else query
val topFilteredQuery = if(flag2) bottomFilteredQuery.filter(_.value < 40) else bottomFilteredQuery
val sortedQuery = if(flag3) topFilteredQuery.soryBy(_.date.desc) else topFilteredQuery
val finalQ = if(flag4) sortedQuery.take(5) else sortedQuery

Upvotes: -1

Related Questions