How can I combine pagination and non-grouping of requests in Slick 3.0?

Question

To simplify, let's say that I have three tables:

val postTable = TableQuery[Posts] val postTagTable = TableQuery[PostTags] val tagTable = TableQuery[Tags] 

A single post can have multiple tags, and postTagTable just contains a relation.

Now I can request messages and tags as follows:

 val query = for { post <- postTable postTag <- postTagTable if post.id === postTag.postId tag <- tagTable if postTag.tagId === tag.id } yield (post, tag) val postTags = db.run(query.result).map { case result: Seq[(Post,Tag)] => result.groupBy(_._1).map { case (post, postTagSeq) => (post, postTagSeq.map(_._2)) } } 

Which would give me Future[Seq[(Post, Seq(Tag))]] .

So far so good.

But what if I want to add pagination for messages? Since one Post can have several Tags with the above request, I don’t know how many lines in take from the request to get, say, 10 Posts .

Does anyone know a good way to get the same result with a certain number of posts in a single query?

I'm not even sure how I would approach this in my native SQL without nested queries, so if anyone has a suggestion in this direction, I would also be happy to hear it.

Thanks!

EDIT

Just so you know what request I'm doing right now:

 val pageQuery = postTable drop(page * pageSize) take(pageSize) val query = for { pagePost <- pageQuery post <- postTable if pagePost.id === post.id postTag <- postTagTable if post.id === postTag.postId tag <- tagTable if postTag.tagId === tag.id } yield (post, tag) val postTags = db.run(query.result).map { case result: Seq[(Post,Tag)] => result.groupBy(_._1).map { case (post, postTagSeq) => (post, postTagSeq.map(_._2)) } } 

But this obviously leads to a subquery. And that I would like to avoid.

EDIT 2

Another solution with two queries:

 val pageQuery = postTable drop(page * pageSize) map(_.id) take(pageSize) db.run(pageQuery.result) flatMap { case ids: Seq[Int] => val query = for { post <- postTable if post.id inSetBind ids postTag <- postTagTable if post.id === postTag.postId tag <- tagTable if postTag.tagId === tag.id } yield (post, tag) val postTags = db.run(query.result).map { case result: Seq[(Post,Tag)] => result.groupBy(_._1).map { case (post, postTagSeq) => (post, postTagSeq.map(_._2)) } } } 

But this will require two trips to the database and use the in operator, so it is probably not as good as the connection request.

Any suggestions?

Answer 1

You can do the following:

  def findPagination(from: Int, to: Int): Future[Seq[(Post, Seq[Tag])]] = { val query:DBIO[Seq[(Album,Seq[Genre])]] = postRepository.findAll(from, to).flatMap{posts=> DBIO.sequence( posts.map{ post=> tagRepository.findByPostId(post.id).map(tags=>(post,tags)) } ) } db.run(query) } 

Inside PostRepository

 def findAll(from: Int, limit: Int): DBIO[Seq[Post]] = postTable.drop(from).take(limit).result 

Inside TagRepository

  def findByPostId(id: Int): DBIO[Seq[Tag]] = { val query = for { tag <- tagTable pstTag <- postTagTable if pstTag.postId === id && tag.id === pstTag.tagId } yield tag query.result } 

EDIT

I cannot do this without a subquery in a single query. Your current solution is the best. You can also optimize your query by removing the unnecessary "connection"

 val query = for { pagePost <- pageQuery postTag <- postTagTable if pagePost.id === postTag.postId tag <- tagTable if postTag.tagId === tag.id } yield (pagePost, tag) 

And you get something like the following SQL (Slick 3.0.1):

 SELECT x2.`postname`, x2.`id`, x3.`tagname`, x3.`id` FROM (SELECT x4.`postname` AS `postname`, x4.`id` AS `id` FROM `POST` x4 LIMIT 10, 1) x2, `POST_TAG` x5, `TAG` x3 WHERE (x2.`id` = x5.`postId`) AND (x5.`tagId` = x3.`id`) 

Perhaps in your case it is also more efficient to precompile this request http://slick.typesafe.com/doc/3.0.0/queries.html#compiled-queries

Answer 2

I have the same problem. May be more interesting:

  val query = for { ((p, pt), t) <- posts.filter({x => x.userid === userId}).sortBy({x=>x.createdate.desc}).take(count). joinLeft (postsTags).on((x, y)=>x.postid === y.postid). joinLeft (tags).on(_._2.map(_.tagid) === _.tagid) } yield (p.?, t.map(_.?)) //val query = posts filter({x => x.userid === userId}) sortBy({x=>x.createdate.desc}) take(count) try db.run(query result) catch{ case ex:Exception => { log error("ex:", ex) Future[Seq[(Option[PostsRow], Option[Option[TagsRow]])]] { Seq((None, None)) } } } 

then the result of this query:

 result onComplete { case Success(x) => { x groupBy { x => x._1 } map {x=>(x._1, x._2.map(_._2))} } case Failure(err) => { log error s"$err" } 

}

it returns Sequence as follows: Seq [(Post, Seq [Tag]), (Post, Seq [Tag]) ........]