6

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
Improve this question
2
  • 1
    I'm not entirely sure what you expect as a result. I guess I have to ask: how would you do it in plain SQL? Commented Dec 21, 2015 at 6:59
  • @PatrykĆwiek updated with example expected sql Commented Dec 21, 2015 at 12:48

4 Answers 4

Reset to default
7 
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

yes, I can add clauses to a query, the goal was to take various queries which have been created and bring them together to generate sql like in the example.
nice, thanks - that is exactly what I was looking for
the reason I cannot pass around a Query is because the order in which the QuerySegments are generated is non-deterministic but the order in which I build up the query for actual execution is very important. Using the technique you outlined above, I can take a set of generated segments and assemble them in the correct order when ready for execution.
0

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.

Improve this answer

Comments

-1

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
Improve this answer

2 Comments

this won't work in my case because the incoming query are a non-deterministic arbitrary set.
we need a more generic solution.
-2

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.